0

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

0

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
  • 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> ? – Yoga May 27 '16 at 8:06
  • 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 – Sam Partridge May 27 '16 at 8:33
  • 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 – spaghettidba May 27 '16 at 8:37
  • i have put my script that i use. check it out. which part that i had to modified to makes my plan works? – Yoga May 27 '16 at 8:43
1

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.

  • 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. – Yoga May 27 '16 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.