i want to make an auto restore script for the transaction log only
i have found the script to restore log in directory which have named with dbname_timestamp.trn
the problem is this script will restore all the log files in there directory no matter which log files have restored before or not. and it will get failed in the output screen in sql server management studio.
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 = N'''
+ @backupPath + @backupFile + ''' WITH FILE = 1, STANDBY= ''' + @standbydir + ''''
PRINT (@cmd)
FETCH NEXT FROM backupFiles INTO @backupFile
END
the question is, what script to make the restore is only restore the log file only the log where not restored yet before.
for example:
in directory, there are 4 log files, log_1 , log_2, log_3, and log_4. i have restored log_1 and log_2, what script to make restore only log_3 and log_4, because my script will restore the all of log files in there directory.
btw, This is my script (modified from Greg's Scripts)
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @standbydir 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 = 'yogalogmanual'
SET @backupPath = 'K:\shared240\logmanual\'
SET @standbydir = 'K:\shared240\logmanual\yogarollback_01.bak'
-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.bak'
AND backupFile LIKE @dbName + '%'
--SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = N'''
-- + @backupPath + @lastFullBackup + ''' WITH FILE = 1, STANDBY = N''' + @standbydir + ''''
--EXEC (@cmd)
-- to check backup log which not restored yet
-- 5 - check for log backups
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 = N'''
+ @backupPath + @backupFile + ''' WITH FILE = 1, STANDBY= ''' + @standbydir + ''''
PRINT (@cmd)
FETCH NEXT FROM backupFiles INTO @backupFile
END
CLOSE backupFiles
DEALLOCATE backupFiles
Thank You,
Yoga
Best Answer
This script will get you the last log that was restored to the database. You could use it to amend your script to pickup a the first file with a date > the backup_start_date.
Alternatively, have you considered configuring Log Shipping?