Creating copy of live MS SQL Database in the same serve

There are occasions where we (developers) need to copy databases in the same server. Consider a situation where you want to get a copy of production database for testing purpose and you can’t move it into a new server (because of resource/budget restrictions).

Unfortunately MS SQL server doesn’t provide direct method to copy databases in same server (you can copy database between servers using Copy Database Wizard).

There fore we need to depend on alternative methods.

Following 3 steps show how you can achieve this with T-SQL statements.

1. Backup original database to disk

BACKUP DATABASE TestDB TO DISK = 'D:\TestDB\Test.dat'

• “TestDB” is the name of the original database name
• TO DISK indicate we are storing the backup in backup device (in this case it is hard disk)Backup device will be created in given file ('D:\TestDB\Test.dat')

2. Drop the destination database if it exists

IF EXISTS(SELECT * FROM sysdatabases WHERE name = 'TestDBCopy')

BEGIN

SET @query = 'DROP DATABASE TestDBCopy'

EXEC (@query)

END

• Destination database is the copy that we are going to create (TestDBCopy)

3. Restore with a new database

RESTORE DATABASE TestDBCopy FROM DISK = 'D:\TestDB\Test.dat'

WITH

MOVE 'TestDB' TO 'D:\TestDB\TestDBCopy.mdf',

MOVE 'TestDB_Log' TO 'D:\TestDB\TestDBCopy.ldf',

FILE = 5

• “TestDBCopy” is the name of the new database
• It will be restored from full backup set we created in the step 1 (Test.dat)
• Since it is in same server and files are already exists we need to move (rename log file and datafiles)

E.g. Move ‘TestDB’ TO 'D:\TestDB\TestDBCopy.mdf'

In here ‘TestDB’ is the logical name of the data file and 'D:\TestDB\TestDBCopy.mdf' section indicate full name of the new data file in OS.

• FILE = 5 indicate DBMS to pickup 5^th file set from the backup device.

That’s it, now you can go back to SQL Server Management Studio and see how your old copy and new copy will work.