SQL SERVER – Copy Database from Instance to Another Instance – Copy Paste in SQL Server

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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

50 thoughts on “SQL SERVER – Copy Database from Instance to Another Instance – Copy Paste in SQL Server

    • 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.

  1. 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: http://sqlconcept.com/2011/04/21/the-concept-of-one-way-time-travel/

    Feodor

  2. 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?

  3. Hi Dave,

    I tried copy database but logical file names of destination database remains same as source database.

  4. 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

  5. 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

  6. 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 SAFE\ELMASRYDBA
    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 AUTHORITY\NETWORK 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.

  7. 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.

  8. 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.

    • 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.

  9. 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…..

  10. Pingback: SQLAuthority News – An Year Worth Remembering and Looking Forward to Better Next Year « SQL Server Journey with SQLAuthority

  11. Pingback: SQL SERVER – What is Piecemeal Restore – Quiz – Puzzle – 22 of 31 « SQL Server Journey with SQL Authority

  12. 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?

  13. 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!

  14. 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

  15. Pingback: SQL SERVER – Generate Script for Schema and Data – SQL in Sixty Seconds #021 – Video « SQL Server Journey with SQL Authority

  16. Hi,
    Could anybody please suggest me a free tool which can copy primary keys and all constraints when copying remote database?

  17. I am geeting an error While trying to find a folder on SQL an OLE DB error was encountered with error code ….(Login failed for user ‘user’). I am passing correct credential for source and destination server ( they are different )
    Any help?

  18. Pingback: SQL SERVER – Take Database Backup using SSMS – SQL in Sixty Seconds #037 – Video « SQL Server Journey with SQL Authority

  19. Pingback: SQL SERVER – Cycle Clipboard Ring in SSMS – SQL in Sixty Seconds #045 – Video | SQL Server Journey with SQL Authority

  20. How can I transfer records(only selected columns) from table A in SQL 2005 to SQL 2008 located on same server? I tried folowing but it doesn’t recognize source tables

  21. How can I transfer records(only selected columns) from table A in SQL 2005 to SQL 2008 located on same server select/insert ? I tried following but it doesn’t recognize source table (error: Invalid object name SourceTable ):

    Use [DestinationTable]

    Insert into [DestinationTable].dbo.[table1]
    (id, itemName,ItemCode)

    Select id, itemName,ItemCode
    from [SourceTable].dbo.[table1]

    Is my SQL script incorrect?

  22. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

  23. I am new to sql server. I have to copy one database to another machine which runs sql server 2008R2> I have created agent job but its fails. source machine is in Domain but destination machine is not in domain. it is in network but in workgroup

  24. i want to daily update my sql-server daily which is hosted from the local server how can i do this ?
    For eg :- 1 Table having total 10 records next days their are total 15 records so 5 new records have been added, so i want to update this daily.

  25. I have actually tried it, it ran successfully, but then i am not able to see copied DBs on destination server? Any thing i am missing?

  26. I have SQL Server 2012 and I want to create similar DB in SQL 2008 instance, can i do that or there will be any problem with Schema due to newly introduced Data types

  27. I am experiencing the same error at the Execute SQL Server Agent Job step as many of the others here.

    3 scenarios:
    1. works – source and destination db are on the same SQL server instance
    2. SQL Server Agent Job (error) – source and destination db are on the same server, but different SQL server instance (same version of SQL 11.0/2012)
    3. SQL Server Agent Job (error) – source and destination db are on different servers (same version of SQL 11.0/2012)

    Admin permissions doesn’t appear to be the issue.

    Hopefully, someone will be able to come up with a resolution for this error.

  28. Pingback: SQL SERVER – Copy Database – SQL in Sixty Seconds #067 | Journey to SQL Authority with Pinal Dave

  29. Pingback: SQL SERVER – Attach or Detach Database – SQL in Sixty Seconds #068 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s