Build restore scripts dynamically

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

  • Nice script, thanks.

  • 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                                        ***/
    /***                               ***/


    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

    -- 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

                        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'


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

    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

  • 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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply