Previous Next

Best Way to Copy an SQL Server Database

Overview

Duplicating an SQL Server database from one sever to another is a common development technique, used to add a database to a new SQL server, during the database refactoring, etc. dbForge Studio for SQL Server enables to preform the database replication in several ways:

  • detaching and attaching database;
  • comparing and synchronizing schemas and data of source and target databases;
  • restoring a backed up source database to a target database.

In this article, we will take a closer look at each method and determine differences between them.

Detach and Attach Method

NOTE: to use this method, SQL Server version on the target must be the same or higher that that on the source.

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

Detach Databatase Command

2. The Detach Database dialog box opens with the name of the database in it. Click OK.

Detach Database Dialog Box

3. Duplicate the detached database from the source server to the target one:

  • in desktop mode, press the Windows key and R at the same time, to show the Run command line;
  • the Run dialog box opens. Type "\\dbmssql" and click OK;
    Run Command
  • copy the detached database file (*.mdf) and the log file (*.ldf), and paste them to the target location, or to the folder, shared between two servers.

NOTE: To perform step 3, you should have administrator privileges.

5. Right-click the target connection, point to Tasks and select "Attach Database..." from the shortcut menu.

Attach Database Command

6. The Browse Files and Attach Database dialog boxes open. In the Browse Files dialog box, select the required database and click OK.

Attach Database Dialog Box

7. The database file and log file will appear in the Attach Database dialog box. Click OK.

Attach Data Dialog Box

8. Reattach the database to the source connection.

dbForge Studio for SQL Server enables to automate the aforesaid procedure with help of the Transfer Database wizard. For more infomation, see Copying SQL Server Database with the Copy Database Wizard.

Backup and Restore Method

NOTE: to use this method, SQL Server version on the target must be the same or higher that that on the source.

1. Backup the source database. See the detailed step-by-step guidance on how to backup an SQL Server database.

NOTE: you should backup the source database to the folder, shared between the servers. Otherwise, you should move the backup file to the target server - see step 3 for the Detach and Attach method.

2. Create a new target database: in Database Explorer, right-click the target connection and select "New Database".

Create New Database Command

3. Right-click the new database and select "Restore". The Restore wizard opens.

4. On the Source and Target tab of the wizard:

  • in Source for Restore, specify the path to the backup file;
  • in Destination to Restore, select the target database;
  • Click Next.

6. (Optional) On the Options tab of the wizard, set the required options for the backup procedure. Click "Restore".

7. Wait till the restore process is completed and click "Finish".

Data and Schema Comparison and Synchronization Method

1. Create a new target database: in Database Explorer, right-click the target connection and select New Database.

Create New Database Command

2. Compare and synchronize schemas of the source database with the target database. See the detailed step-by-step guidance on how to compare and synchronize SQL schemas.

3. Compare and synchronize data of the source database with the target database. See the detailed step-by-step guidance on how to compare and synchronize SQL data.

Summary

With dbForge Studio for SQL Server, you can copy a database in three different ways. Each of them has its strong and weak points, however, sometimes only one method can can be used for a certain task, e.g., if SQL Server versions of target and source do not coincide, or one of them is less, than SQL Server 2005, only the Data and Schema Comparison and Synchronization method can be used.

To sum up, we would like to list pros and cons of each method.

The Detach and Attach Method

Pros:

  • the fastest way to copy a database.

Cons:

  • source database should be offline;
  • all connections will be lost;
  • shared folder is required;
  • SQL Server version on the target must be the same or higher that that on the source;
  • can't be used on Windows Azure.

Backup and Restore Method

Pros:

  • source database may remain online;
  • connections will be preserved.

Cons:

  • a bit slower, then the previous method;
  • SQL Server version on the target must be the same or higher that that on the source;
  • shared folder is required;
  • can't be used on Windows Azure.

Schema and Data Compare&Sync Method

Pros:

  • the source database may remain online;
  • connections will be preserved;
  • does not require a shared folder;
  • method does not depend on the SQL Server version;
  • can be used on Windows Azure.

Cons:

  • time consuming method.