The Easiest way to create a local Copy of your SQL Database with a different name

Every so often I come across the task to create a local copy of a database for testing purposes or to host for an external consultant or freelancer.

Every time I seem to forget how to do this the easiest possible way, so this time im blogging it both to share, and to save it for next time. If you know how to do even easier og better, please let me know, and i will update the script:

USE master 
GO 

-- Create a database backup of the database
-- that you want a copy of, and insert the
-- path as the BackupFileName below
DECLARE @BackupFileName NVARCHAR(2000) 
SET @BackupFileName = 'D:\Backup\backups\AminoUser\newbackup.bak' 

-- Find the logical name for both the Data and Log file
-- (right click database, properties, Files)
-- And enter names in the below
DECLARE @DataFileName NVARCHAR(200) 
SET @DataFileName = 'AminoUser'
DECLARE @LogFileName NVARCHAR(200) 
SET @LogFileName = 'AminoUser_log' 

-- What do you want the new database to be called?
DECLARE @NewDatabaseName NVARCHAR(200) 
SET @NewDataBaseName = 'AminoUser_Test2' 

-- Where to put the new files? Set the directory ending with \
DECLARE @NewFilePath NVARCHAR(2000)
SET @NewFilePath = 'D:\SQLdata\' 

-- And now execute.
DECLARE @Query VARCHAR(2000)
SET @Query = 'RESTORE FILELISTONLY FROM DISK = ' 
   +  QUOTENAME(@BackupFileName, '''') + 
' RESTORE DATABASE ' + @NewDatabaseName + 
' FROM DISK = ' + QUOTENAME(@BackupFileName, '''') +
   ' WITH RECOVERY, 
   MOVE ' + QUOTENAME(@DataFileName, '''') + ' TO ' 
              + QUOTENAME(@NewFilePath+ @NewDataBaseName+ '.mdf', '''') + ', 
   MOVE ' + QUOTENAME(@LogFileName, '''') + ' TO ' 
              + QUOTENAME(@NewFilePath+ @NewDataBaseName+ '.ldf', '''') 
EXEC (@Query)
GO
Posted August 8, 2007 by Joachim Lykke Andersen
In

Comments [0]   
All comments require the approval of the site owner before being displayed.
OpenID
Please login with either your OpenID above, or your details below.
Name
E-mail
(will show your gravatar icon)
Home page

Comment (HTML not allowed)  

Enter the code shown (prevents robots):
Live Comment Preview