Blank Output when I run the restore script

Question

Post reply

Blank Output when I run the restore script

ijafri

SSC Enthusiast

Points: 110
More actions
November 3, 2016 at 2:50 pm

#331712

Hi,

I tried to run the restore script posted at http://qa.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.

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

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

mahesh.shinde SSC Enthusiast Points: 148 More actions · Answer 1

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 · Answer 2

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