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
