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.

Reference: Pinal Dave (https://blog.sqlauthority.com)

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

  • Hello, Dave.

    When I run Copy Database Wizard I receive an error on ‘Execute SQL Server Agent job’. Destination server Windows logs shows this error “Message: ERROR : errorCode=-1073548784 description=Executing the query “sys.sp_addrolemember @rolename = N’ifm_usr’, @mem…” failed with the following error: “The role ‘ifm_usr’ does not exist in the current database.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.” CDW doesn’t create ‘ifm_usr’ Database role automatically

    SQL Agent job is running under domain account, whom I provided sysadmin rights both on source and destination servers. This account was also added into server admins group (I was desperate :) ).

    Can you please provide any guidance.

    Reply
  • Hi..is there a limit of size in using copy db wizard?

    Reply
  • peter southgate
    October 4, 2016 10:01 pm

    This example uses the Detach and Attach method as with every other example that I have seen on the web. The issue with this method is that it requires the database to be taken off-line. The alternative method i.e. SQL Management Object method is never demonstrated in any tutorial I have found and I have never managed to get it to work – it always fails. I wonder if there is a known bug in this method and that is why no one ever demonstrates it. Has anyone else tried the alternative method with any success?

    Reply
  • Hello – I need some guidance regarding SQL Express and reusing an instance name. I am trying to install a new version of SQL 2014 Express on a desktop that already has SQL 2008 Express installed. I need to be able to reuse the same database and instance name with SQL 2014 Express. I don’t want to upgrade SQL 2008 Express to SQL 2014 Express because our application may not be the only one using SQL 2008 Express.
    I am getting errors when trying to install SQL 2014 Express…
    ////////////////
    Exception type: Microsoft.SqlServer.Configuration.SetupExtension.InstanceNameIsUsedByAnotherInstanceException
    Message:
    Instance name ‘XXXXXXXX’ is already in use. To continue, specify a unique instance name.
    HResult : 0x858c0014
    FacilityCode : 1420 (58c)
    ErrorCode : 20 (0014)
    ////////////////
    Is there a way I can reuse the same instance name and database?
    Thanks in advance…

    Reply
  • I scheduled the job to run instead of running immediately.
    It failed with
    ” Login failed for user ‘DOMAIN\DOM-USER$’ ”

    Yet, I cannot add that user to the source server. (Even checked off ‘Service Accounts’ when searching for the user.)

    Reply

Leave a Reply

Menu