CREATE PROCEDURE RebuildDevDatabase AS
 
-- the original database (use 'SET @DB = NULL' to disable backup)
DECLARE @DB VARCHAR(200)
SET @DB = 'LiveDatabase'
 
-- the backup filename
DECLARE @BackupFile VARCHAR(2000)
SET @BackupFile = 'C:\Backups\SQL\RebuildDevDatabase\db_backup.dat'
 
-- the new database name
DECLARE @TestDB VARCHAR(200)
SET @TestDB = 'DevDatabase'
 
-- the new database files without .mdf/.ldf
DECLARE @RestoreFile VARCHAR(2000)
SET @RestoreFile = 'C:\Backups\SQL\RebuildDevDatabase\db_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, '''') + ' WITH INIT, SKIP'
SELECT @query
    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 = 'ALTER DATABASE ' + @TestDB + '  SET SINGLE_USER with ROLLBACK IMMEDIATE'
    EXEC (@query)
    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