SQLTeam.com | Weblogs | Forums

Restore multiple transaction log backups in standby mode from disk

Hi, We are receiving transaction log backups from a vendor for a off-site database. We have already restored the full backup in Standby mode. we will be receiving multiple transaction log backups everyday. I need a script to restore the transaction log backups to standby mode. The script I am trying to use pulls the files into the filelist, but the script is not doing anything and I can't figure out why.

Can someone help me figure this out?

USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)

-- 2 - Initialize variables
SET @dbName = 'Telcor'
SET @backupPath = 'D:\TelcorLogDump'

-- 3 - get list of files
SET @cmd = 'DIR /b "' + @backupPath + '"'

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE '%.TRN'
AND backupFile LIKE @dbName + '%'
-- AND backupFile > @lastFullBackup

OPEN backupFiles

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile

WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''
+ @backupPath + @backupFile + ''' WITH STANDBY = N''D:\TelcorLogDump\ROLLBACK_UNDO_Telcor.BAK'
PRINT @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END

CLOSE backupFiles
DEALLOCATE backupFiles

Instead of

PRINT @cmd

Replace with:

EXEC(@cmd)

Note that your cursor is not using an ORDER BY to ensure the files are restored in the proper sequence. Assuming the backup date and time are part of the backup file name, you'll want to add ORDER BY backupFile to your cursor declaration. If log files are restored out of sequence you'll get an error.

I made the suggested changes. When I executed the code, i got the message: Commands Completed successfully instantly (nothing tried to restore). Something else is not right with this. I made sure the files are being pulled in correctly. Not sure what else to do to troubleshoot this.

You are only printing @cmd, not executing it.

I changed to exec @cmd. See below. Still I get no restores occuring only a commands completed successfully message.

USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)

-- 2 - Initialize variables
SET @dbName = 'Telcor'
SET @backupPath = 'D:\TelcorLogDump'

-- 3 - get list of files
SET @cmd = 'DIR /b "' + @backupPath + '"'

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE '%.TRN'
AND backupFile LIKE @dbName + '%'
ORDER BY backupFile

OPEN backupFiles

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile

WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''

  • @backupPath + @backupFile + ''' WITH STANDBY = N''D:\TelcorLogDump\ROLLBACK_UNDO_Telcor.BAK'
    EXEC (@cmd)
    FETCH NEXT FROM backupFiles INTO @backupFile
    END

CLOSE backupFiles
DEALLOCATE backupFiles

I commented out the exec @cmd and changed to a select @cmd, but I get no results.

I figured out why the exec @cmd wasn't working. I was filtering out the files before it got to the cursor. Now I have one last problem. I am getting a message that says Unclosed quotation mark after the character string 'D:\TelcorLogDump\ROLLBACK_UNDO_Telcor.BAK' See below code. Can someone help me figure out where I'm going wrong with these quote marks?USE Master;
GO
SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''

  • @backupPath + @backupFile + ''' WITH STANDBY = N''D:\TelcorLogDump\ROLLBACK_UNDO_Telcor.BAK'

I had to add two extra ticks at the end:
ROM DISK = '''

  • @backupPath + @backupFile + ''' WITH STANDBY = N''D:\TelcorLogDump\ROLLBACK_UNDO_Telcor.BAK'''