Build restore scripts dynamically

• Brian R.A. Smith

  Mr or Mrs. 500

  Points: 507

  More actions

  November 2, 2016 at 12:09 pm

  #318612

  Comments posted to this topic are about the item Build restore scripts dynamically

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  November 14, 2016 at 9:03 am

  #1912289

  Nice script, thanks.

• mahesh.shinde

  SSC Enthusiast

  Points: 148

  More actions

  April 15, 2017 at 5:24 am

  #1938250

  This script would require small modifications. I have modified the script so tht it would display restore script properly in output window. Also we would need to remove query option --> Include column headers in result set --> Under Results Text in SSMS.

  
/**************************************************************************************************/
/***        Build Restore Scripts Dynamically             ***/
/***                               ***/
/*** Save this script on each server to be restored, and open in Management Studio    ***/
/*** Set the @BackupPath variable to the parent folder where the server's backup files exist ***/
/*** Run the script, and copy the output results to a new query window        ***/
/*** Run entire script at once, or restore each database individually         ***/
/*** This script works fine only with native sql backups created using Maintenance Plan   ***/
/*** Script created by Brian Smith - April 2, 2015               ***/
/*** Script Modified by Mahesh Shinde - April 15, 2017                                        ***/
/***                               ***/
/**************************************************************************************************/

  SET NOCOUNT ON

  DECLARE @BackupPath varchar(500); SET @BackupPath = 'C:\MSSQL\Backups\' -- e.g. 'C:\MSSQL\Backups\'
DECLARE @DB varchar(500)
DECLARE @AllFilesTable TABLE (
    subdirectory varchar(1500),
    depth int,
    isfile bit
    )
DECLARE @BackupsTable TABLE (
    BackupFile varchar(1500)
    )
DECLARE @BakFile varchar(1500)
DECLARE @TrnFile varchar(1500)
DECLARE @RestoreScript varchar(max)

  -- Start building the restore script
SET @RestoreScript = 'USE [master];
'

  -- Capture the first database
SELECT @DB = min(name) FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') AND state_desc = 'ONLINE'

  --select min(name) from sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') AND state_desc = 'ONLINE'

  --set @DB = 'DBTeam'
-- Begin the loop
WHILE @DB IS NOT NULL
BEGIN

  -- Capture the list of backup files
--select @DB
INSERT INTO @AllFilesTable (subdirectory, depth, isfile)
EXEC ('xp_dirtree ''' + @BackupPath + '' + @DB + ''', 1, 1')

  --select * from @AllFilesTable

  INSERT INTO @BackupsTable (BackupFile)
SELECT subdirectory FROM @AllFilesTable WHERE RIGHT(subdirectory,29) >= RIGHT((SELECT max(subdirectory) FROM @AllFilesTable WHERE RIGHT(subdirectory,4) = '.bak'),29)

  --select * from @BackupsTable
-- Continue building the script lines

  SET @RestoreScript = @RestoreScript + 'ALTER DATABASE [' + @DB + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' + char(13)
-- Add the full backup restore script line
SELECT @BakFile = BackupFile FROM @BackupsTable WHERE RIGHT(BackupFile,4) = '.bak'
SET @RestoreScript = @RestoreScript + 'RESTORE DATABASE [' + @DB + '] FROM DISK = N''' + @BackupPath + @DB + '\' + @BakFile + ''' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5;'

  select @RestoreScript
DELETE @BackupsTable WHERE BackupFile = @BakFile
            
            -- Add the transaction log backup restore script lines (one for each .trn file)
            SELECT @TrnFile = min(BackupFile) FROM @BackupsTable WHERE RIGHT(BackupFile,4) = '.trn'
            WHILE @TrnFile IS NOT NULL
            BEGIN

                      select replace(replace( 'RESTORE LOG [' + @DB + '] FROM DISK = N''' + @BackupPath + @DB + '\' + @TrnFile + ''' WITH NORECOVERY;' , char(13) , ''),char(10),'')
                    DELETE @BackupsTable WHERE BackupFile = @TrnFile
                    SELECT @TrnFile = min(BackupFile) FROM @BackupsTable WHERE RIGHT(BackupFile,4) = '.trn'

              END

  --select @RestoreScript
-- Add the script line to bring the database back online

  select 'RESTORE DATABASE [' + @DB + '] WITH RECOVERY;
GO'
SET @RestoreScript = ''

  -- Clear the temp tables for the next capture
DELETE @AllFilesTable
-- Cycle through the databases one at a time
SELECT @DB = min(name) FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') AND state_desc = 'ONLINE' AND name > @DB
--select @RestoreScript
END
SET NOCOUNT OFF


• Brian R.A. Smith

  Mr or Mrs. 500

  Points: 507

  More actions

  April 15, 2017 at 3:44 pm

  #1938258

  The script was written and intended to use with the out-of-the-box defaults of SSMS, including the 'results to grid' query option. Yes, if you have changed your option to 'results to text' you would have to un-check 'include column headers' for it to look right. I suspect the other display discrepancies also relate to that setting, as they turn out fine for me in a 'results to grid' run.

  Also, when modifying someone else's script, please explain in the script--with each line, preferably--what was modified. This makes it much easier to determine what is relevant to any given environment.