SQL Server auto restore only log where not restored before

Question

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

Answer 1

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?

select top 1
 bs.database_name,
 backup_type = case bs.type
 when 'D' then 'FULL'
 when 'L' then 'LOG'
 when 'I' then 'DIFF'
 else 'OTHER'
 end,
 compressed_backup_size/1000000 as 'compressed_backup_size(MB)',
 physical_device_name, 
 bs.backup_start_date, 
 bs.backup_finish_date,
 bs.backup_finish_date - bs.backup_start_date as Duration
from msdb.dbo.backupset as bs
inner join msdb.dbo.backupmediafamily as bmf on bs.media_set_id = bmf.media_set_id
where bs.type = 'L'
and database_name = '<name of your database>'
Order by backup_start_date desc

• Yoga: i have try to using the logshipping tools, but i just want to make it from script, because from logshipping cannot backup and restore log with compress. your script just show me the latest log, whats script to make my restore script just restore the log file after <log result from your script> ?
• Sam Partridge: How do you populate @fileList? What version and edition of SQL Server are you using? Log shipping does support Compression. msdn.microsoft.com/en-GB/library/ms188168.aspx
• spaghettidba: If you're looking for a script to perform something similar to what log shipping does, here is mine: spaghettidba.com/2013/02/08/manual-log-shipping-with-powershell
• Yoga: i have put my script that i use. check it out. which part that i had to modified to makes my plan works?

Answer 2

I assume that your database is in the restoring state and you alredy have restored a full backupset WITH NORECOVERY.

In this case you can inspect the contents of the log backups using RESTORE HEADERONLY: this will return the column FirstLSN, that you can use to compare with MAX(redo_start_lsn) from sys.master_files. All log backups with FisrtLSN < MAX(redo_start_lsn) can be safely skipped.

• Yoga: i have restored with STANDBY mode, because i want to read access only on my secondary database. if i use NORECOVERY, i can't see anything in my secondary database.