Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log shipping to two secondary databases and one has failed


Log shipping to two secondary databases and one has failed

Author
Message
ron.grout
ron.grout
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 90
I have log shipping set up to update two secondary databases, one on our backup server here in the UK and one on a server in our US office.
I have just noticed that the restore job on the UK one failed since the 6th of December. The US database is fine and up to date.
The database is very large and takes an eternity to copy across, I am talking maybe 24 hours, especially to the US.
I wondered whether, to avoid that copy, I could switch off log shipping, wait until the restore jobs in the US are up to date, then use that database as the secondary on that server as it should be identical to the live one, when I restart log shipping.
I know that I will have to backup the live database and restore it to the backup server here but that does not take quite so long.
Any thoughts would be appreciated.
regards
Ron
MysteryJimbo
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
I'm not sure I follow 100%.

You have a primary db (somewhere) and two secondary's (US & UK). The UK restore has failed for some reason you don't mention whilst the US is working.

You shouldn't need to touch the US secondary at all.

What error have you got on the UK secondary? Missing log or corrupt log file?
ron.grout
ron.grout
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 90
I am not sure why the restore job on the UK backup originally failed but it was on the 6th December. I have not been able to find any information on that as it does not go back that far in the job history.
The message we get now when it fails is that it cannot find a log file old enough to restore. We drop the log files after 7 days so there will not be one for the date/time it was last restored.
The restore job on the US server never failed so the database there is fine and up to date.
I wondered whether I can disable the backup job on the live server here and the copy and restore jobs on the UK backup then copy the database from the live server, restore it on the backup server then enable the jobs again. It sounds to me that it would work, just looking for confirmation.
Thank you for replying.
regards
Ron
MysteryJimbo
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
You would have to copy a more recent full backup from the live server and restore using NORECOVERY, that should work. There no way of copying DB files and attaching them in a recovering state.

There's a slim possibility you could get away with using differential restore if you have enough and the timing was right. You can check the last restored file in [msdb].[dbo].[log_shipping_secondary_databases]. You could use the differentials to bring you back into transaction log restore territory.
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8780 Visits: 16554
ron.grout (12/18/2013)
I have log shipping set up to update two secondary databases, one on our backup server here in the UK and one on a server in our US office.
I have just noticed that the restore job on the UK one failed since the 6th of December. The US database is fine and up to date.
The database is very large and takes an eternity to copy across, I am talking maybe 24 hours, especially to the US.
I wondered whether, to avoid that copy, I could switch off log shipping, wait until the restore jobs in the US are up to date, then use that database as the secondary on that server as it should be identical to the live one, when I restart log shipping.
I know that I will have to backup the live database and restore it to the backup server here but that does not take quite so long.
Any thoughts would be appreciated.
regards
Ron

Presumably you have regular full backups in force? If so it will not be possible at this late stage to repair log shipping using a differential backup as the differential base LSN will have moved on with each full backup.
If you can locate any t-log backups and apply these through the last full backup you would be fine, only problem is at this late stage the file retention will have kicked in and purged the log backup files.
You may have to re init from the beginning otherwise.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
harikumar.mindi
harikumar.mindi
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 527
I feel there are 2 methods to resolve this issue:
Method-1
Considering that you have full and txn logs happening,
>>check on the US server which was the last txn log restored. use the query select * from msdb.dbo.logshipping_monitor_secondary where secondary_database = '<DBName>'
>>Check if the subsequent log files are available on the destination Shared Location, if do not have and if your organization supports backing to any tape device retrieve from that location and place them in the shared location on US server.
>>Manually initiate the restore job from SSMS and check if it works.

Method-2
>>Since you are speaking about a large DB, split the full backup into smaller chuncks while taking the backup and copy them over to US (ftp is suggestible).
>>restore the DB with NORECOVERY and manually initiate the copy job and ensure that all the latest log files are copied and once this is done initiate the restore job

Hope this helps and if you need for clarification let me know.

Thanks,

HariMindi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search