Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Log shipping to two secondary databases and one has failed Expand / Collapse
Author
Message
Posted Wednesday, December 18, 2013 2:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 5, Visits: 54
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
Post #1523999
Posted Wednesday, December 18, 2013 6:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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?
Post #1524097
Posted Wednesday, December 18, 2013 6:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 5, Visits: 54
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
Post #1524104
Posted Wednesday, December 18, 2013 6:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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.

Post #1524107
Posted Sunday, December 29, 2013 1:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:54 PM
Points: 6,282, Visits: 13,538
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"
Post #1526364
Posted Wednesday, January 8, 2014 11:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 10:40 AM
Points: 11, Visits: 221
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
Post #1529205
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse