How to Copy a MS SQL 2005 Database


Problem: You want to make a copy of a Database to a Database on the same server. Useful if you want to run two sites that use the same database schema.

Solution: (Cleaned up version of http://skainsez.blogspot.com/2008/03/how-to-copy-sqlserver-2005-database.html)

Using Microsoft SQL Server Management Studio Express:

The first step I did was to create a new database on the server which would be the duplicate db.

For the purposes of this article let’s assume that my original db is called ‘Prod’ and my new db is called ‘Dev’.

So I right clicked on the ‘Databases’ folder in SSMSE (Sql Server Management Studio Express), chose ‘New Database . . .’ and created a new db called ‘Dev’.

Next up I right-clicked on the ‘Prod’ db (the one I want to duplicate) and chose ‘Tasks -> BackUp . . .’ I created a full backup of the db just to make sure I had the latest data. Once the backup had completed I then right-clicked on the ‘Dev’ database and chose ‘Tasks -> Restore -> Database . . .’

On the ‘General’ page I specified ‘Prod’ as my ‘From database’ and ‘Dev’ as my ‘To database’. Note that when you select the ‘From database’ the ‘To database’ value gets changed to that value. You need to make sure that the ‘To database’ value is actually the name of the duplicate database.

Next I clicked over to the Options page in the Restore dialog. Here I had to change the ‘Restore As’ names of the files in the file listing. By default they are the same as the ‘Original File Name’ values. But that would cause the backup to overwrite the original database.

What we want is to change the ‘Restore As’ names to be the names of the duplicate database’s files.

In this example I changed these two entries:

C:\Program Files\Microsoft SQL Server\MSSQL\data\Prod.mdf
C:\Program Files\Microsoft SQL Server\MSSQL\data\Prod_log.ldf

To:

C:\Program Files\Microsoft SQL Server\MSSQL\data\Dev.mdf
C:\Program Files\Microsoft SQL Server\MSSQL\data\Dev_log.ldf

Then, still on the Options page of the ‘Restore Database’ dialog, I checked the ‘Overwrite the existing database’ checkbox. Next just hit the ‘OK’ button and let it do its thing. The end result should be a duplicate of your original database.

  1. No comments yet.
(will not be published)
  1. No trackbacks yet.