Generate Restore Scripts
Applicability:
Applicability:
SQL Server 2000: N/A
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Tested
SQL Server 2014: Tested
SQL Server 2016: Tested
SQL Server 2017: Not Tested
Credits:
Credits:
Author: ChillyDBA
Date: 6 Jun 2020
Description
Description
Generates TSQL Scripts to restore all database backups from to the destination server. The recovery model of the source database is taken into account, and a parameter must be supplied to set the destination compatibility level
GENERATE script on: SOURCE SERVER
EXECUTE script on: DESTINATION SERVER
Code
Code
--*******************************************--**** RUN THIS ON THE SOURCE SERVER ***--*******************************************
-- use this script to generate backup scripts for every non-system DB on a server.-- save each DB script to a separate file using the file name at the top of the section
SET NOCOUNT ON
--*******************************************--**** Define and set parameter values ***--*******************************************
DECLARE @DatabaseName SYSNAME ,@DatabaseID INT ,@FilePath SYSNAME ,@RecoveryModel SYSNAME ,@CompatabilityLevel SYSNAME
DECLARE @RestoreDatabaseList TABLE( DatabaseID INT ,DatabaseName SYSNAME ,RecoveryModel SYSNAME)
DECLARE @RestoreDatabaseResults TABLE( ResultID INT IDENTITY (1,1) ,ResultText SYSNAME)
DECLARE @DataFileList TABLE( FileID INT IDENTITY (1,1) ,DBFileType SYSNAME ,DBFileName SYSNAME)
SELECT @FilePath = 'D:\DBA\Temp\' --supply a path name to use for the backup command ,@DatabaseName = NULL --optionally, supply a DB name for generaton of command for a single database ,@CompatabilityLevel = '130' -- for SQL Server 2016
INSERT @RestoreDatabaseList (DatabaseID, DatabaseName, RecoveryModel)SELECT database_id, name, Recovery_Model_DescFROM master.sys.databasesWHERE name NOT IN ('master','model','tempdb','msdb','reportserver','reportservertempdb', 'distribution')AND name = ISNULL(@DatabaseName, name)AND state_desc IN ('ONLINE')
SELECT @DatabaseID = MIN (DatabaseID)FROM @RestoreDatabaseList
WHILE @DatabaseID IS NOT NULLBEGIN SELECT @DatabaseName = DatabaseName ,@RecoveryModel = RecoveryModel FROM @RestoreDatabaseList WHERE DatabaseID = @DatabaseID
DELETE FROM @DataFileList
INSERT @DataFileList (DBFileType, DBFileName) SELECT type_desc ,name FROM master.sys.master_files WHERE DB_NAME(database_id) = @DatabaseName
INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @RestoreDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ START SCRIPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @RestoreDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT '-- Save as file name: 050. Restore_database_' + @DatabaseName + '_migrated_from_' + @@SERVERNAME + '.sql' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT '--**********************************************************************************' INSERT @RestoreDatabaseResults (ResultText) SELECT '--***** Purpose: Restore Migrated Database [' + @DatabaseName + ']' INSERT @RestoreDatabaseResults (ResultText) SELECT '--***** Generated on: ' + CONVERT(VARCHAR(12), GETDATE(), 112) INSERT @RestoreDatabaseResults (ResultText) SELECT '--***** Generated by: ' + SUSER_SNAME() INSERT @RestoreDatabaseResults (ResultText) SELECT '--**********************************************************************************' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT 'USE master' INSERT @RestoreDatabaseResults (ResultText) SELECT 'GO' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT 'ALTER DATABASE [' + @DatabaseName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE' INSERT @RestoreDatabaseResults (ResultText) SELECT 'GO' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT 'RESTORE DATABASE [' + @DatabaseName + ']' INSERT @RestoreDatabaseResults (ResultText) SELECT 'FROM DISK = N''' + @FilePath + @DatabaseName + '_For_Migration.bak''' INSERT @RestoreDatabaseResults (ResultText) SELECT 'WITH' INSERT @RestoreDatabaseResults (ResultText) SELECT ' FILE = 1,' INSERT @RestoreDatabaseResults (ResultText) SELECT ' MOVE ''' + DBFileName + ''' TO N''D:\SQLServer\MSSQL\Data\' + DBFileName + '.mdf'',' FROM @DataFileList WHERE DBFileType = 'rows'
INSERT @RestoreDatabaseResults (ResultText) SELECT ' MOVE ''' + DBFileName + ''' TO N''E:\SQLServer\MSSQL\Data\' + DBFileName + '.ldf'',' FROM @DataFileList WHERE DBFileType = 'log'
INSERT @RestoreDatabaseResults (ResultText) SELECT ' NOUNLOAD,' INSERT @RestoreDatabaseResults (ResultText) SELECT ' REPLACE,' INSERT @RestoreDatabaseResults (ResultText) SELECT ' STATS = 5'
INSERT @RestoreDatabaseResults (ResultText) SELECT ' GO' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT 'ALTER DATABASE [' + @DatabaseName + '] SET MULTI_USER' INSERT @RestoreDatabaseResults (ResultText) SELECT 'GO' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT 'ALTER DATABASE [' + @DatabaseName + '] SET COMPATIBILITY_LEVEL = ' + @CompatabilityLevel INSERT @RestoreDatabaseResults (ResultText) SELECT 'GO' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT 'ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY ' + @RecoveryModel + ' WITH NO_WAIT' INSERT @RestoreDatabaseResults (ResultText) SELECT 'GO' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT 'USE [' + @DatabaseName + ']' INSERT @RestoreDatabaseResults (ResultText) SELECT 'GO' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT 'EXEC sp_changedbowner ''sa''' INSERT @RestoreDatabaseResults (ResultText) SELECT 'GO' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @RestoreDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ END SCRIPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @RestoreDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT ''
SELECT @DatabaseID = MIN (DatabaseID) FROM @RestoreDatabaseList WHERE DatabaseID > @DatabaseID
ENDSELECT ResultTextFROM @RestoreDatabaseResultsORDER BY ResultID
SET NOCOUNT OFF
-- use this script to generate backup scripts for every non-system DB on a server.-- save each DB script to a separate file using the file name at the top of the section
SET NOCOUNT ON
--*******************************************--**** Define and set parameter values ***--*******************************************
DECLARE @DatabaseName SYSNAME ,@DatabaseID INT ,@FilePath SYSNAME ,@RecoveryModel SYSNAME ,@CompatabilityLevel SYSNAME
DECLARE @RestoreDatabaseList TABLE( DatabaseID INT ,DatabaseName SYSNAME ,RecoveryModel SYSNAME)
DECLARE @RestoreDatabaseResults TABLE( ResultID INT IDENTITY (1,1) ,ResultText SYSNAME)
DECLARE @DataFileList TABLE( FileID INT IDENTITY (1,1) ,DBFileType SYSNAME ,DBFileName SYSNAME)
SELECT @FilePath = 'D:\DBA\Temp\' --supply a path name to use for the backup command ,@DatabaseName = NULL --optionally, supply a DB name for generaton of command for a single database ,@CompatabilityLevel = '130' -- for SQL Server 2016
INSERT @RestoreDatabaseList (DatabaseID, DatabaseName, RecoveryModel)SELECT database_id, name, Recovery_Model_DescFROM master.sys.databasesWHERE name NOT IN ('master','model','tempdb','msdb','reportserver','reportservertempdb', 'distribution')AND name = ISNULL(@DatabaseName, name)AND state_desc IN ('ONLINE')
SELECT @DatabaseID = MIN (DatabaseID)FROM @RestoreDatabaseList
WHILE @DatabaseID IS NOT NULLBEGIN SELECT @DatabaseName = DatabaseName ,@RecoveryModel = RecoveryModel FROM @RestoreDatabaseList WHERE DatabaseID = @DatabaseID
DELETE FROM @DataFileList
INSERT @DataFileList (DBFileType, DBFileName) SELECT type_desc ,name FROM master.sys.master_files WHERE DB_NAME(database_id) = @DatabaseName
INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @RestoreDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ START SCRIPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @RestoreDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT '-- Save as file name: 050. Restore_database_' + @DatabaseName + '_migrated_from_' + @@SERVERNAME + '.sql' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT '--**********************************************************************************' INSERT @RestoreDatabaseResults (ResultText) SELECT '--***** Purpose: Restore Migrated Database [' + @DatabaseName + ']' INSERT @RestoreDatabaseResults (ResultText) SELECT '--***** Generated on: ' + CONVERT(VARCHAR(12), GETDATE(), 112) INSERT @RestoreDatabaseResults (ResultText) SELECT '--***** Generated by: ' + SUSER_SNAME() INSERT @RestoreDatabaseResults (ResultText) SELECT '--**********************************************************************************' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT 'USE master' INSERT @RestoreDatabaseResults (ResultText) SELECT 'GO' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT 'ALTER DATABASE [' + @DatabaseName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE' INSERT @RestoreDatabaseResults (ResultText) SELECT 'GO' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT 'RESTORE DATABASE [' + @DatabaseName + ']' INSERT @RestoreDatabaseResults (ResultText) SELECT 'FROM DISK = N''' + @FilePath + @DatabaseName + '_For_Migration.bak''' INSERT @RestoreDatabaseResults (ResultText) SELECT 'WITH' INSERT @RestoreDatabaseResults (ResultText) SELECT ' FILE = 1,' INSERT @RestoreDatabaseResults (ResultText) SELECT ' MOVE ''' + DBFileName + ''' TO N''D:\SQLServer\MSSQL\Data\' + DBFileName + '.mdf'',' FROM @DataFileList WHERE DBFileType = 'rows'
INSERT @RestoreDatabaseResults (ResultText) SELECT ' MOVE ''' + DBFileName + ''' TO N''E:\SQLServer\MSSQL\Data\' + DBFileName + '.ldf'',' FROM @DataFileList WHERE DBFileType = 'log'
INSERT @RestoreDatabaseResults (ResultText) SELECT ' NOUNLOAD,' INSERT @RestoreDatabaseResults (ResultText) SELECT ' REPLACE,' INSERT @RestoreDatabaseResults (ResultText) SELECT ' STATS = 5'
INSERT @RestoreDatabaseResults (ResultText) SELECT ' GO' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT 'ALTER DATABASE [' + @DatabaseName + '] SET MULTI_USER' INSERT @RestoreDatabaseResults (ResultText) SELECT 'GO' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT 'ALTER DATABASE [' + @DatabaseName + '] SET COMPATIBILITY_LEVEL = ' + @CompatabilityLevel INSERT @RestoreDatabaseResults (ResultText) SELECT 'GO' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT 'ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY ' + @RecoveryModel + ' WITH NO_WAIT' INSERT @RestoreDatabaseResults (ResultText) SELECT 'GO' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT 'USE [' + @DatabaseName + ']' INSERT @RestoreDatabaseResults (ResultText) SELECT 'GO' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT 'EXEC sp_changedbowner ''sa''' INSERT @RestoreDatabaseResults (ResultText) SELECT 'GO' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @RestoreDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ END SCRIPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @RestoreDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT '' INSERT @RestoreDatabaseResults (ResultText) SELECT ''
SELECT @DatabaseID = MIN (DatabaseID) FROM @RestoreDatabaseList WHERE DatabaseID > @DatabaseID
ENDSELECT ResultTextFROM @RestoreDatabaseResultsORDER BY ResultID
SET NOCOUNT OFF