How to Copy SQL Server Database

Developers and DBAs often encounter a situation when they need to copy a database from one SQL sever to another for the purposes of development, testing, or diagnostics. With dbForge Studio for SQL Server, you can duplicate an SQL server database 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 than the one 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.
  4. NOTE: To perform step 3, you need to have administrator privileges granted.
  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 above mentioned procedure with help of the SQL Server Copy Database Wizard .

The Detach and Attach Method pros and cons

  • 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 than on the source;
    • can't be used on Windows Azure.

Backup and Restore Method

NOTE: to use this method, SQL Server version on the target must be the same or higher than the one 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.
    Restore Wizard - Source and Target Tab

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

    Restore Wizard - Options

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

    Restore Wizard - Operation Complete

Backup and Restore Method pros and cons

  • 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 than on the source;
    • shared folder is required;
    • can't be used on Windows Azure.

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.

Schema and Data Compare&Sync Method pros and cons

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

Summary

With dbForge Studio for SQL Server, you can copy or clone a SQL server 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.