USE master
GO
 
-- the original database (use 'SET @DB = NULL' to disable backup)
DECLARE @DB VARCHAR(200)
SET @DB = 'MY_DB_NAME'
 
-- the backup filename
DECLARE @BackupFile VARCHAR(2000)
SET @BackupFile = 'PATH_ON_SERVER'
 
-- the new database name
DECLARE @TestDB VARCHAR(200)
SET @TestDB = 'NEW_DB_NAME'
 
-- the new database files without .mdf/.ldf
DECLARE @RestoreFile VARCHAR(2000)
SET @RestoreFile = 'PATH_ON_SERVER'
 
-- ****************************************************************
-- 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