Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
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
add comment

2 Answers

up vote 1 down vote accepted

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}';

share|improve this answer
    
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
add comment

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.

share|improve this answer
    
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 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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