Blank Output when I run the restore script

• ijafri

  SSC Enthusiast

  Points: 120

  More actions

  November 3, 2016 at 2:50 pm

  #331712

  Hi,

  I tried to run the restore script posted at http://www.sqlservercentral.com/scripts/148412/

  however, when I run it, is shows a blank output. I tried running it on several sql servers but it shows a blank output.

  Can you please review the code and repost it.

  thank you.

• mahesh.shinde

  SSC Enthusiast

  Points: 148

  More actions

  April 15, 2017 at 5:29 am

  #1938251

  Try using below script. Also you would need to change query option --> remove include column header in resultset from query output.
  
/**************************************************************************************************/
/***        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


• Sue_H

  SSC Guru

  Points: 90860

  More actions

  April 18, 2017 at 8:03 am

  #1938524

  That's an old post but the error is likely not related to needed to change the script. It's only designed for Maintenance Plan backups meaning the naming and directory structure and names needed are in place for the script to work. But if the user leaves off the backslash after the @BackupPath variable, the all files table won't be populated as the directories will not exist and there will be no output. Or the user isn't using maintenance plans. Checking for the backslash at the end of the path might be a better way to address the issue.

  Sue