SQL Server auto restore only log where not restored before 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 &gt; @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 &gt; @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
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 &lt; MAX(redo_start_lsn) can be safely skipped.