1

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!

10.trn
11.trn
<DB Was Restored>
12.trn
13.trn (contains full backup)
14.trn  
15.trn
  • Have you considered completely re-initializing log shipping? You'd already be done in less time than it took to write the question. – Aaron Bertrand Nov 7 '13 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 '13 at 17:29
1

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 '13 at 18:28
  • Sorry I can't upvote you yet, will do when I get enough rep. – BJury Nov 8 '13 at 8:51
0

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 '13 at 17:26
  • Reinitializing a logshipping would not even have any negative effects on your PROD environment. – Kin Shah Nov 7 '13 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 '13 at 17:35

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.