Create multiple new databases from the same backup file



--Step 1 find Logic names
RESTORE FILELISTONLY
   FROM DISK = 'C:\bk\TEST.BAK' 
  --Find out the LogicalName of data file and log file
  --data file: TEST
  --log file LogicalName: TEST_log
  --Both names will be used in the MOVE command


  --Step 2 restore to create multiple copies of this database
-- Restore the file to a new db name test1
RESTORE DATABASE test1
   FROM DISK = 'C:\bk\TEST.BAK' 
   WITH RECOVERY,
   MOVE 'TEST' TO 'C:\bk\test1_Data.mdf', 
   MOVE 'TEST_log' TO 'C:\bk\test1_Log.ldf'
GO

-- Restore the file to a new db name test2
RESTORE DATABASE test2
   FROM DISK = 'C:\bk\TEST.BAK' 
   WITH RECOVERY,
   MOVE 'TEST' TO 'C:\bk\test2_Data.mdf', 
   MOVE 'TEST_log' TO 'C:\bk\test2_Log.ldf'
GO

-- Restore the file to a new db name test3
RESTORE DATABASE test3
   FROM DISK = 'C:\bk\TEST.BAK' 
   WITH RECOVERY,
   MOVE 'TEST' TO 'C:\bk\test3_Data.mdf', 
   MOVE 'TEST_log' TO 'C:\bk\test3_Log.ldf'
GO
 


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s