How to copy a database to another SQL Server database engine

This topic describes how to copy a database from one SQL Server database engine to another by using dbForge Studio for SQL Server. The Copy Database wizard allows you move, copy or override databases and their objects easily.

Limitations and Restrictions:

  1. You can not copy or move the following databases:
    • System databases
    • Databases marked for replication.
    • Databases marked Inaccessible, Loading, Offline, Recovering, Suspect.
  2. If you select the Move option, the wizard deletes the source database automatically after moving the database. The Copy Database wizard does not delete a source database if you select the Copy option.

  3. You cannot copy a database from the source SQL Server instance to the target, if there is exactly the same database hosted on the target SQL Server instance. In this case you must select the Override option. If the option is selected, the target database will be overridden.

  4. You must be a member of the sysadmin server role on both the source and target servers.
  5. SQL Agent should be run on SQL Server. For more information about SQL Agent, refer to the MDSN entry.

To Copy a Database:

1. In Database Explorer, right-click the database to be scripted, point to Tasks and then click Copy Database.

How to copy a SQL Server database

2. The Copy Database wizard opens.

How to copy a SQL Server database

3. Optionally, you can click the Open button, to load a previously saved project file.

4. In the Source section, select the source SQL Server instance.

5. In the Target section, select the target SQL Server instance.

Note

SQL Server version on target must be the same or higher than that on the source.

6. On the Select databases page, select check boxes next to a database name you want to copy, move or overwrite. Optionally you can use the Filter tool for quick search.

How to move a SQL Server database

7. Optionally, on the Target databases page, you can change the name of the target database, as well as the directory for the Data and Log files. By default, the Data and the Log files are saved to a folder of the target server. If you decide to change the directory for the files, make sure this folder is shared between the source and the target servers.

Copy a SQL Server database

8. On the Transfer settings page, set the proxy settings for the transfer of selected database(s).

Copy SQL database wizard

9. On the Copy logins page, select logins to copy.

10. On the Errors Handling page, specify errors processing behavior and logging options.

Errors Handling page of Copy database wizard

11. Click Execute.

Download dbForge Studio for SQL Server and try it absolutely free for 30 days!