Create a copy of an existing SQL Server database

I was searching for an easy way to create a copy of an existing live database and put it on the same SQL Server in a test database. Because I didn't find a good stored procedure I build my own SQL. To use it I put the code to the master database as a stored procedure, now I can run one short command to get a copy!

USE master GO

• - the original database (use 'SET @DB = NULL' to disable backup)

SET @DB = 'PcTopp'

• - the backup filename

SET @BackupFile = 'c:\pctopp\sqlserver\backup.dat'

• - the new database name

SET @TestDB = 'TestDB'

• - the new database files without .mdf/.ldf

SET @RestoreFile = 'c:\pctopp\sqlserver\backup'

• -
• -                    no change below this line
• -

DECLARE @query varchar(2000)

DECLARE @DataFile varchar(2000) SET @DataFile = @RestoreFile + '.mdf'

DECLARE @LogFile varchar(2000) SET @LogFile = @RestoreFile + '.ldf'

IF @DB IS NOT NULL BEGIN SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''') EXEC (@query) END

• - RESTORE FILELISTONLY FROM DISK = 'C:\temp\backup.dat'
• - RESTORE HEADERONLY FROM DISK = 'C:\temp\backup.dat'
• - RESTORE LABELONLY FROM DISK = 'C:\temp\backup.dat'
• - RESTORE VERIFYONLY FROM DISK = 'C:\temp\backup.dat'

IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB) BEGIN SET @query = 'DROP DATABASE ' + @TestDB EXEC (@query) END

RESTORE HEADERONLY FROM DISK = @BackupFile DECLARE @File int SET @File = @@ROWCOUNT

DECLARE @Data varchar(500) DECLARE @Log varchar(500)

SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')

CREATE TABLE #restoretemp ( LogicalName varchar(500), PhysicalName varchar(500), type varchar(10), FilegroupName varchar(200), size int, maxsize bigint ) INSERT #restoretemp EXEC (@query)

SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D' SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'

PRINT @Data PRINT @Log

TRUNCATE TABLE #restoretemp DROP TABLE #restoretemp

IF @File > 0 BEGIN SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') +  ' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' + QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File) EXEC (@query) END GO

If someone has a better solution it would be nice to get a comment on this!