Using SQL Server Copy Database Wizard we can copy database from one server to another server.
Here i will show step by step using screen shots.
Click Alternatively you can initiate from maintenance plan node
Click on Copy Database. It will open the wizard.
Click on next.Specify Source server.Use authentication for connecting the server instance either windows or SQL Server
Click on next.select destination server
Click on next. you may get error like this
we have to start SQL server agent services in both source server and destination server.
Go to SQL server Agent --> Right Click --> Start
Once you have started the agent service.Click yes button as shown in the above error message picture.
you can use two methods for copying data. I have select first option in this demo.
use the detach and attach method. while perform the task source database will not be available to users.
It is the best option for copying the medium to large databases. use SQL Management Object if you want you database online.(you can use backup database and restore the database method. it is best option compared to this method). but it will take even days.I have tested with the second method with 1 GB database in my local machine it took almost 10 hour for copying to secondary sever. My sever RAM is 2 GB and i have installed TFS,visual studio 2008 in my system that time.
For copying the database it will use the integration service task Transfer SQL Server objects tasks.Click on Next
You can select copy or move option. here i have selected copy . then click on next. Provide the database name or it will use the source server database name.
You can select the two options as shown in the above picture. You can drop and recreate database or you can stop the execution if database with same name already present in destination server. I have selected the first option.Click on next
Select the Objects you want to transfer. These objects will not be part of your database backup. you may get orphaned user when your restore the database backup. I will show next demo on backup and restore.This is the advantage with copy database over database backup.
Click on next . If database copying will fail we can log the error in the text file or windows event log.
Click Next. You can schedule the Package or Run immediately. We can schedule job for daily,weekly or only once. This task will SQL Server Agent service Account.
Click Next.We can able to see the settings given through wizard.
Click finish to create and execute SQL Server transfer database objects task.
Job failed . We have created the job but job execution failed. we can see the error on Click on the error. You will get error based logging options.
This error will show if we logging option as text file.
If we logging option as windows event log the error will shows like this. The job failed Check the log on the destination server for details.
You have to add the SQL Server agent Login to the Source instance.
verify the event log in destination server.
It is showing that the SQL server Agent account "NETWORK SERVICE" Should have Sysadmin permissions in order execute the job.
Using Once we have add the sysadmin to the SQL server agent account we can able exeute the copy database task.
Verify that database copied into the destination server.
We have successfully copied the database from one server to together server.
Using Import and Export wizard also we can copy entire database from one server to another server. This will also use the same job for copying database.
Here i will show step by step using screen shots.
Click Alternatively you can initiate from maintenance plan node
Click on Copy Database. It will open the wizard.
Click on next.Specify Source server.Use authentication for connecting the server instance either windows or SQL Server
Click on next. you may get error like this
we have to start SQL server agent services in both source server and destination server.
Go to SQL server Agent --> Right Click --> Start
Once you have started the agent service.Click yes button as shown in the above error message picture.
you can use two methods for copying data. I have select first option in this demo.
use the detach and attach method. while perform the task source database will not be available to users.
It is the best option for copying the medium to large databases. use SQL Management Object if you want you database online.(you can use backup database and restore the database method. it is best option compared to this method). but it will take even days.I have tested with the second method with 1 GB database in my local machine it took almost 10 hour for copying to secondary sever. My sever RAM is 2 GB and i have installed TFS,visual studio 2008 in my system that time.
You can select copy or move option. here i have selected copy . then click on next. Provide the database name or it will use the source server database name.
You can select the two options as shown in the above picture. You can drop and recreate database or you can stop the execution if database with same name already present in destination server. I have selected the first option.Click on next
Select the Objects you want to transfer. These objects will not be part of your database backup. you may get orphaned user when your restore the database backup. I will show next demo on backup and restore.This is the advantage with copy database over database backup.
Click on next . If database copying will fail we can log the error in the text file or windows event log.
Click Next. You can schedule the Package or Run immediately. We can schedule job for daily,weekly or only once. This task will SQL Server Agent service Account.
Click Next.We can able to see the settings given through wizard.
Click finish to create and execute SQL Server transfer database objects task.
Job failed . We have created the job but job execution failed. we can see the error on Click on the error. You will get error based logging options.
This error will show if we logging option as text file.
If we logging option as windows event log the error will shows like this. The job failed Check the log on the destination server for details.
SQL Server Agent Should have read and write permission on the source and destination folder. First you have to find out under which account SQL Server agent is running
You can Check folder Permissions for the Network service as shown below. Service should have read and write permissions to that folder.
after providing permissions to that folder.you have to check Database issues.
You can get error details using system procedure xp_readerrorlog
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: ]
Error: 18456, Severity: 14, State: 38.
You have to add the SQL Server agent Login to the Source instance.
verify the event log in destination server.
It is showing that the SQL server Agent account "NETWORK SERVICE" Should have Sysadmin permissions in order execute the job.
Using Once we have add the sysadmin to the SQL server agent account we can able exeute the copy database task.
Verify that database copied into the destination server.
We have successfully copied the database from one server to together server.
Using Import and Export wizard also we can copy entire database from one server to another server. This will also use the same job for copying database.
No comments:
Post a Comment