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