/ Published in: SQL
replace paths and database names as needed
MY_DB_HERE
PATH_ON_SERVER
NEW_DB_NAME
MY_DB_HERE
PATH_ON_SERVER
NEW_DB_NAME
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
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