December 18, 2013 at 2:53 am
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
December 18, 2013 at 6:31 am
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?
December 18, 2013 at 6:43 am
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
December 18, 2013 at 6:52 am
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.
December 29, 2013 at 1:34 pm
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" 😉
January 8, 2014 at 11:06 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply