Is it possible to force SQL Log Shipping to run from a given .trn file onward?

I have two databases, where one ships to another this was working fine. However I had to restore the primary database and in the interim another .trn file was created. (This would be rubbish given the DB was restored and so couldn't be applied anywhere.) For the next transaction I manually backed up the whole database, and could manually restore it to the logship destination. (13.trn below)

The next transaction file that was created could also be manually restored. However the jobs are stuck on the transaction before the full backup, (12.trn below) and of course the job is failing as it is behind the destination db.

If I can set it to try 15.trn, it should (probably) work! I've tried changing msdb..log_shipping_secondary_databases to no avail.

So, is it possible to set which file sqllogship.exe uses next?

Basic example below!

<DB Was Restored>
13.trn (contains full backup)
  • Have you considered completely re-initializing log shipping? You'd already be done in less time than it took to write the question. Nov 7, 2013 at 17:05
  • Indeed, but in a prod environment where that wouldn't be possible so I'd like to know how to get around the problem. I mean there must be a way to make it move to a given trn file.
    – BJury
    Nov 7, 2013 at 17:29

2 Answers 2


I have manually reset the log shipping by updating the last_restored_file column in the log_shipping_monitor_secondary table on the standby server to the last transaction log that was successfully restored.

Something like this:

UPDATE lsms 
SET lsms.last_restored_file = '{transaction log file}'
FROM dbo.log_shipping_monitor_secondary lsms
WHERE lsms.secondary_database = '{DB Name}';
  • A-hah! This is the table. I was close with log_shipping_secondary_databases!! Damn you Microsoft, rule number 1 of database design, don't have duplicate data! Many thanks.
    – BJury
    Nov 7, 2013 at 18:28
  • Sorry I can't upvote you yet, will do when I get enough rep.
    – BJury
    Nov 8, 2013 at 8:51

I would advise to just recreate logshipping from scratch using the wizard. Also, if your database is huge, then compress the backup (depending on what version you are using) and then initialize the secondary from backup.

  • I'm sure that would work but only because this is not a production DB. If it were, and say one of the machines that is being shipped to had an issue then this problem would persist, so I'd quite like to figure out how to fix the problem!
    – BJury
    Nov 7, 2013 at 17:26
  • Reinitializing a logshipping would not even have any negative effects on your PROD environment.
    – Kin Shah
    Nov 7, 2013 at 17:31
  • Really? Even if you were shipping to a number of different servers? Wouldn't that require you to reinitialise them all?
    – BJury
    Nov 7, 2013 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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