Duplicating a MS SQL database

Duplication of a database with Microsoft SQL Server Management Studio, for example for a test environment.

Microsoft SQL Server Management Studio is part of an SQL server installation. To copy an entire database, follow these steps:

1. Open Microsoft SQL Server Management Studio.

2. Right click on the database to be copied and select Tasks > Backup....

3. In the Backup database dialog that follows, use the Add button to select a directory and target file where you want to save the backup copy under Target (below). For example: c:\temp\esdata.bak. Activate the Backup copy option.

    

   Make sure that only one target file is in the list.

   There may already be some entries in the Target area. You should delete these entries and replace them with your own (new) entry with the backup.

4. Now run the backup.

5. Right click on the Databases entry in the server tree structure and select Restore database... in the context menu (right click).

6. In the dialog that follows select the previously created backup file under Source for restore by selecting From medium.

7. Don't forget to set the Restore option in the list of backup sets (below).

8. Enter the new database name in the In database field under Target for restore, for example: esdata_test.

9. Switch to the Options page. The file names in the Restore database files as list must be matched. To do this, select a new target directory for the databases for the Restore as column using the appropriate ... button; for example: c:\MyDatabases\esdata_test.mdf (database file) and c:\MyDatabases\esdata_test.ldf (transaction log file).

10. The new database is created when you click OK to confirm.