Script to check seq # on the transaction log backups

[kodaksmile]

Does anyone by chance have a script that will run through and check the sequence numbers on the transaction logs in the log shipping directory? We are required to delay our log shipping apply by 24 hours due to limitations on legacy system recovery, and I don't want to find out we are 24 hours behind because we are missing a log file.

 

[mkal]

If when you set up log shipping and you confiured a Monitory you should be informed if you are missing a log file on the secondary server if it doesn't get there during the allotted time.

If you don't have the monitor set up you could use the xp_cmdshell dir to insert the directories files into a table that can be queried...something like this should work.

create table #tmpDir (ret nvarchar(1000))
insert #tmpDir
exec master.dbo.xp_cmdshell 'Dir C:\<your path/folder name>

--remove unwanted directory content
delete #tmpDir where not ret like '%.<some extension> %' or ret is null

By default xp_cmdshell is enabled on 2000 but you would have to enable it on 2005/2008. It's not recommended you enable xp_cmdshell but I suppose you could enable it, run your script, then disable it.