SQL Server has feature which copy database from one database to another database and it can be automated as well using SSIS.
Make sure you have SQL Server Agent Turned on as this feature will create a job. The same job will execute the task. Make sure that SSIS is properly configured as well with necessary security permissions. You can automate this process as well control error logging.
Following are the steps to copy database from one instance to another instance.
Specify Source Server
Specify Destination Server
Here you can select option if you want to keep the database ONLINE when it is being copied.
You can also select option of MOVE or COPY database as well.
Give appropriate database name.
On this screen you can select additional options to copy as well.
You create the package over here.
You can schedule the package using SQL Server Agent.
When this process is over it will show the success message and database will be copied to another server.
You can see how easy is the process to copy the database to another server.
Watch SQL in Sixty Seconds Episode on same subject.
[youtube=http://www.youtube.com/watch?v=lSh3fq-MikE]Reference: Pinal Dave (https://blog.sqlauthority.com)
59 Comments. Leave new
thanks dave
Pinal,
Is it possible to copy database from 2005 to 2008 R2 server?
Thanks
Prasad
Hello Prasad,
U can copy database from 2005 to 2008r2 becuase this wizard is available from 2005 and later versions.But it is not possible in reverse order.
This is a basic UI in SSMS for the extended features for transferring objects which can be found in SSIS.
I think that SSIS is much more flexible, since it provides conditional flow features and variables as well as endless possibilities of container properties changes.
What I am trying to say is, that SSIS should be used in more complicated scenarios, since it is more flexible and more advanced.
@Prasad: yes, you can copy a database from 2005 to R2, if you have specify the proper server names and you have the proper logins. I recommend you use the SSIS, though.
And here is a way to transfer the database back from R2 to 2005:
Feodor
but dave this is not working in my database it is throughing error.
it is showing error when it is executing sql server agent.
plz suggest why it is?
This is really nice article.
I want to Highlight one point …
Copy Database Wizard should have access to source database files.
Hi Dave,
I tried copy database but logical file names of destination database remains same as source database.
The only issue that i have with this is that it will copy the SQL Logins from one server to another and maintain the permissions that were granted on the orginal server but it won’t copy the password(as far as i can tell). So i have to reset the password in the DB and application of hunt down the password and set it to the correct value on the new server.
Thanks,
Peter
Hi Dave,
I am using this copy database method to copy database from one server to another server. while going through the wizard, there are 2 option to copy 1. detach database 2. copy while being online. Now, I would like to know which is a good option to follow? Since my database is around 20GB big. I want the highperfance task and error free transfer.
Regards,
Milan
Malaysia
Hello Milan,
Use the attach and detach method it is good to upgrade databases or moving very large databases.
Thanks it helped me lot. was trying copy database from one instance to another.
This was really good. Thank you..! Mr. Dave.
when i try the copy database show to me this error
Date 27/08/2011 02:24:27 PM
Log Job History (CDW_SAFE_ELMASRYDBA_SAFE_ELMASRYDBA_1)
Step ID 1
Server SAFEELMASRYDBA
Job Name CDW_SAFE_ELMASRYDBA_SAFE_ELMASRYDBA_1
Step Name CDW_SAFE_ELMASRYDBA_SAFE_ELMASRYDBA_1_Step
Duration 00:01:33
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: NT AUTHORITYNETWORK SERVICE. Microsoft (R) SQL Server Execute Package Utility
Version 10.0.1600.22 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 2:24:27 PM Error: 2011-08-27 14:24:28.32 Code: 0xC001404B
Source: CDW_SAFE_ELMASRYDBA_SAFE_ELMASRYDBA_1 Log provider
“{F5F9E7C5-589A-4470-90F9-8FA070A782F3}” Description: The SSIS logging provider has failed to open the log.
Error code: 0x80070005. Access is denied. End Error Error: 2011-08-27 14:24:28.34
Code: 0xC001404B Source: CDW_SAFE_ELMASRYDBA_SAFE_ELMASRYDBA_1 Log provider ”
{F5F9E7C5-589A-4470-90F9-8FA070A782F3}” Description: The SSIS logging provider has failed to open the log.
Error code: 0x80070005. Access is denied. End Error Progress: 2011-08-27 14:24:28.37
Source: SAFE_ELMASRYDBA_SAFE_ELMASRYDBA_Transfer Objects Task
Task just started the execution.: 0% complete End Progress Error: 2011-08-27 14:25:58.42
Code: 0x00000000 Source: SAFE_ELMASRYDBA_SAFE_ELMASRYDBA_Transfer Objects Task
Description: ERROR : errorCode=0 description=Invalid column name ‘eturnSalesID’.
helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C} StackTrace:
at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData() at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.
TransferObjectsTask.TransferDatabasesUsingSMOTransfer() End Error Progress: 2011-08-27 14:25:58.50
Source: SAFE_ELMASRYDBA_SAFE_ELMASRYDBA_Transfer Objects Task Database transfer failed for 1 database(s).:
0% complete End Progress Progress: 2011-08-27 14:25:58.50
Source: SAFE_ELMASRYDBA_SAFE_ELMASRYDBA_Transfer Objects Task
Transfer objects finished execution.: 100% complete End Progress Warning: 2011-08-27 14:25:58.51
Code: 0x80019002 Source: CDW_SAFE_ELMASRYDBA_SAFE_ELMASRYDBA_1
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number
of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number
specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution
returned DTSER_FAILURE (1). Started: 2:24:27 PM Finished: 2:25:58 PM Elapsed: 91.063 seconds. The package execution failed.
The step failed.
Dave,
Do you recommend this option for copying a database (from one server to another) that is approximately 1TB in size?
i am using sql server 2005 enterprise edition. when i am trying copy database wizard, execute sql server agent job operation show error message: the job failed. check the log file for details.Please help me.
same error for me.. any resolution??
Performing operation
– Add log for package (Success)
– Add task for transferring database objects (Success)
– Create package (Success)
– Start SQL Server Agent Job (Success)
– Execute SQL Server Agent Job (Error)
Messages
* The job failed. Check the event log on the destination server for details. (Copy Database Wizard)
when i create copy of database from one server to other server give above error.
Did you finally got an answer to the error message you were getting? The Execute SQL Server Agent Job (Error).
I need the solution too, right now! Thanks.
I also get the same error message
Did you finally got an answer to the error message you were getting? The Execute SQL Server Agent Job (Error).
I need the solution too, right now! Thanks.
If you have a similar error, On destination server, you need to create a new credential, asociated with a login in the destination server that has sysadmin server role, later, when you have the crendential, you need to create, on the destination server, a new proxy, it has to asociate with your new credential, later run again Copy database wizard on the source server, and in “Schedule the package” page, change — integration Services Account with your new Proxy.
:D
i’ve one database(1) and same database(2) in somewhere. i want to update database(1) to database(2) daily. we can update two method.
Replication
sql server agent
but how.
answer me pls. its urgent
Thank u…..
Dave,
I really enjoy your articles. They are often very insightful. Can you direct me to finding out how the copy database works? Is it create scripts that migrate the data? Are they copying the underlying datafile?
Hello Dave,
Thank you for your carticles. I always find them useful. I have a question regarding copy database. Could you please help me?
I was trying to save a copy of a database on our production Server before updating that database. I used the Copy Database wizard with Detach and Attach method. Everything went well except it fails on the step “Execute SQL Server Agent Job”.
Now I found the copy [databaseName] is added to the SQL Server Agent Jobs as one of the job which executed on the day which the copy database failed.
Could you please let me know what could be the cause of this failure, and why the SQL Server Agent Job was created? Thank you!
Hi Dave,
This blogpost just brightened my day as I wanted exactly this, but then the option to copy the DB isnt appearing in my context in tasks… SQL Server agent is ruuning though. I am using SQL Server 2008 R2… Appreciate your insight
Thanks,
Anand
How can we do this by Query..
Backup and restore