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.

SQL SERVER - Copy Database from Instance to Another Instance - Copy Paste in SQL Server copywizard1

SQL SERVER - Copy Database from Instance to Another Instance - Copy Paste in SQL Server copywizard2
Specify Source Server
SQL SERVER - Copy Database from Instance to Another Instance - Copy Paste in SQL Server copywizard3
Specify Destination Server
SQL SERVER - Copy Database from Instance to Another Instance - Copy Paste in SQL Server copywizard4
Here you can select option if you want to keep the database ONLINE when it is being copied.
SQL SERVER - Copy Database from Instance to Another Instance - Copy Paste in SQL Server copywizard5
You can also select option of MOVE or COPY database as well.

SQL SERVER - Copy Database from Instance to Another Instance - Copy Paste in SQL Server copywizard6
Give appropriate database name.
SQL SERVER - Copy Database from Instance to Another Instance - Copy Paste in SQL Server copywizard7
On this screen you can select additional options to copy as well.
SQL SERVER - Copy Database from Instance to Another Instance - Copy Paste in SQL Server copywizard8
You create the package over here.
SQL SERVER - Copy Database from Instance to Another Instance - Copy Paste in SQL Server copywizard9
You can schedule the package using SQL Server Agent.
SQL SERVER - Copy Database from Instance to Another Instance - Copy Paste in SQL Server copywizard10

SQL SERVER - Copy Database from Instance to Another Instance - Copy Paste in SQL Server copywizard11

SQL SERVER - Copy Database from Instance to Another Instance - Copy Paste in SQL Server copywizard12
When this process is over it will show the success message and database will be copied to another server.
SQL SERVER - Copy Database from Instance to Another Instance - Copy Paste in SQL Server copywizard13

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)

SQL Azure, SQL Backup and Restore, SQL Server
Previous Post
SQL SERVER – Getting Columns Headers without Result Data – SET FMTONLY ON
Next Post
SQL SERVER – A Quick Notes on SQL Azure

Related Posts

59 Comments. Leave new

  • ivan mohapatra
    May 27, 2011 10:14 am

    thanks dave

    Reply
  • Pinal,

    Is it possible to copy database from 2005 to 2008 R2 server?

    Thanks
    Prasad

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

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

    Reply
  • ivan mohapatra
    May 27, 2011 11:47 am

    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?

    Reply
  • Varinder Sandhu
    May 27, 2011 12:09 pm

    This is really nice article.

    I want to Highlight one point …

    Copy Database Wizard should have access to source database files.

    Reply
  • Yuvraj Gautam
    May 27, 2011 5:09 pm

    Hi Dave,

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

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

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

    Reply
    • Hello Milan,
      Use the attach and detach method it is good to upgrade databases or moving very large databases.

      Reply
  • Thanks it helped me lot. was trying copy database from one instance to another.

    Reply
  • This was really good. Thank you..! Mr. Dave.

    Reply
  • Mostafa Elmasry
    August 27, 2011 6:17 pm

    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.

    Reply
  • Dave,

    Do you recommend this option for copying a database (from one server to another) that is approximately 1TB in size?

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

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

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

      Reply
  • I also get the same error message

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

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

    Reply
  • 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?

    Reply
  • 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!

    Reply
  • Ananda Sudarshan
    April 9, 2012 12:32 pm

    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

    Reply
  • How can we do this by Query..

    Reply

Leave a Reply