June 2, 2022 at 8:27 pm
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. When I execute the script, I just get a message that Commands completed successfully, but nothing tried to restore.
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 + '%'
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
June 3, 2022 at 8:35 am
Using "with standby" you force the engine to make your database available in a read-only state.
If you know you'll have to restore all log backup files, use NoRecovery in stead of standby except for the last one.
DECLARE @cmd NVARCHAR(500) may be a bit small.
If you want to figure out what's "wrong" , instead of "EXEC (@cmd)" perform a "Select @cmd as CMD". That may give you a good idea.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 3, 2022 at 1:35 pm
I should still be able to restore each log in standby mode. I changed the exec @cmd to a select @cmd and I got no results. Something is going wrong and I can't figure out where. I did a select on the filelist and it is picking up the log file names.
June 3, 2022 at 1:53 pm
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'
June 3, 2022 at 1:54 pm
Does your service account have access to 'D:\TelcorLogDump\' ?
crossed with your last reply 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 3, 2022 at 1:56 pm
I had to add two extra ticks at the end:
ROM DISK = '''
* @backupPath + @backupFile + ''' WITH STANDBY = N''D:\TelcorLogDump\ROLLBACK_UNDO_Telcor.BAK'''
June 3, 2022 at 1:58 pm
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'
Indeed, ad '' ad the end
SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''
+ @backupPath + @backupFile + ''' WITH STANDBY = N''D:\TelcorLogDump\ROLLBACK_UNDO_Telcor.BAK'''
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply