Copying SQL Server Database with the Copy Database Wizard


The article is dedicated to the Copy Database wizard of dbForge Studio for SQL Server. The wizard is specially designed to simplify and optimize such routine and common procedure as the database duplication.

Thus, in this article, we will go into detail of working with the Copy Database wizard and describe the nuances involved with the database duplication procedure.

Limitations and Restrictions

Before you start working with the Copy Database wizard, make sure to comply with the following limitations and restrictions.

  1. SQL Server version on target must be the same or higher than that on the source.
  2. You can not copy or move the following databases:
    • System databases.
    • Databases marked for replication.
    • Databases marked Inaccessible, Loading, Offline, Recovering, Suspect.
  3. You must be a member of the sysadmin server role on both the source and target servers.
  4. If target and source servers are located on the same computer, SQL Agent should be run on target server. Otherwise, SQL Agent should be run on both the source and target servers.

Working With the Copy Database Wizard

1. In Database Explorer, right-click the source database, point to Tasks and select "Copy Database..." from the shortcut menu.

Copy Database Command

2. The Source and target page of the Copy Database wizard opens. Optionally, you can click the Open button, to load a previously saved project file. In the Source section, select the source SQL Server instance. In the Target section, select the target SQL Server instance. Click Next.

Copy Database - Source and target

3. On the Select databases page, select check boxes next to a database name you want to copy, move or overwrite. Please, note:

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

Optionally, you can use the Filter tool for quick search. Click Next.

Copy Database - Select databases

4. 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 default file storage directory of the target server. The Status field shows whether a current database name is valid for creating a database copy. Click Next.

Copy Database - Target databases

5. On the Transfer settings page, select an existing Operating System Proxy Account or enter credentials for Windows Account Authentication. Click Next.

Operating System Proxy Account is created for SQL Agent to ensure the access privileges to the file system of computer, on which SQL Server is installed. Operating System Proxy Account is linked with Windows Account. SQL Agent will use Operating System Proxy Account during the execution of file operations.

The wizard creates Operating System Proxy Account when credentials for Windows Account Authentication are entered, and deltetes it when the database duplication is over. For more information, refer to the MDSN entry.

Note, that a job of the "Operating System" type runs under the SQL Server Agent service account by default. Because of the product requirements, the account must belong to the sysadmin server role. Non-sysadmins need a proxy account to execute the "Operating System" job steps. Therefore, to run a job step of the "Operating System" type with the non-sysadmin owner, you need to set up a proxy.

Copy Database - Transfer settings

6. On the Copy logins page, select logins to copy. To ensure the database operability, the logins should be available on the target server. If the target server contains the reqiured logins, select Do not copy logins. Click Next.

Copy Database - Copy logins

7. On the Errors Handling page, specify errors processing behavior and logging options. Click Execute to start copying database(s).

Copy Database - Errors handling

8. Optionally, on the Progress page you can interrupt database copying by clicking the Cancel button.

Copy Database - Progress

9. The Finish page displays whether database copying has been finished successfully. Optionally, you can:

  • Click Show log file button to view logging information.
  • Select the Save a copy project check box to save the completed database copying as a project.
  • Click the Copy more button to preform another database duplication.

Click Finish to close the Copy Database wizard and complete database duplication.

Copy Database - Finish


Though dbForge Studio for SQL Server provides several ways to copy SQL Server databases, the Copy Databse wizard is the fastest and the easiest way to copy an SQL Server database from one server to another. This essential tool enables to perform such delicate and sometimes toilsome procedure as database duplication in several simple steps. During the work with the wizard, you can:

  • Specify source and target servers.
  • Select an action for a database: move, copy or override.
  • Change database name on the target server and the file location for Data and Log files.
  • Transfer logins to the target server.
  • Save the database project and use it afterwards.