Logshipping is not working.
-
Hi,
We have a log-shipping set up on the production server for reporting purpose.
Unknowingly, I have set up a backup strategy which also includes trans-log backups.
So, my log-shipping failed. Restoration job on the second server failed.
Is there any way I can get back the secondary database on Server2.
Secondary Database is in (StandBy mode/Readonly)
Can anyone help me getting back my secondary server database back?
(I will be getting a daily full back up at 12AM and Tran-log backup for every hour)
-
jjohnsqldba (9/7/2012)
Hi,We have a log-shipping set up on the production server for reporting purpose.
Unknowingly, I have set up a backup strategy which also includes trans-log backups.
So, my log-shipping failed. Restoration job on the second server failed.
Is there any way I can get back the secondary database on Server2.
Secondary Database is in (StandBy mode/Readonly)
Can anyone help me getting back my secondary server database back?
(I will be getting a daily full back up at 12AM and Tran-log backup for every hour)
If you want to get your Log Shipping back and Running you may need to restore a Full backup again. In case you have all the transaction log backups from the moment your Secondary run out of synch, you can try restoring all the transaction log backups from your backup strategy combined with the log backups from Log Shipping. You should consider taking the transaction log backups out of your backup strategy and use the transaction log backups from Log Shipping. By using both you will need both log backups in case of a disaster (the ones from your backup strategy and the ones from Log Shipping).
Why would you like to backup the secondary database?
-
Taking a differential backup from the primary and restoring it to the secondary will re-establish your log chain, just remember to supply the proper option on the restore (STANDBY or NORECOVERY). After the differential is applied to the secondary your existing setup will continue per the usual LS backup, LS copy and LS restore job schedules.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (9/7/2012)
Taking a differential backup from the primary and restoring it to the secondary will re-establish your log chain, just remember to supply the proper option on the restore (STANDBY or NORECOVERY). After the differential is applied to the secondary your existing setup will continue per the usual LS backup, LS copy and LS restore job schedules.That will only work if he didn't take any full backups since he started with Log Shipping!
-
Ignacio A. Salom Rangel (9/7/2012)
opc.three (9/7/2012)
Taking a differential backup from the primary and restoring it to the secondary will re-establish your log chain, just remember to supply the proper option on the restore (STANDBY or NORECOVERY). After the differential is applied to the secondary your existing setup will continue per the usual LS backup, LS copy and LS restore job schedules.That will only work if he didn't take any full backups since he started with Log Shipping!
Agreed. It sounds like he is in the midst of the issue though.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (9/7/2012)
Ignacio A. Salom Rangel (9/7/2012)
opc.three (9/7/2012)
Taking a differential backup from the primary and restoring it to the secondary will re-establish your log chain, just remember to supply the proper option on the restore (STANDBY or NORECOVERY). After the differential is applied to the secondary your existing setup will continue per the usual LS backup, LS copy and LS restore job schedules.That will only work if he didn't take any full backups since he started with Log Shipping!
Agreed. It sounds like he is in the midst of the issue though.
I'm not sure about that, but if he has not make any new full backups your advice will save him some time 🙂
-
Ignacio A. Salom Rangel (9/7/2012)
opc.three (9/7/2012)
Ignacio A. Salom Rangel (9/7/2012)
opc.three (9/7/2012)
Taking a differential backup from the primary and restoring it to the secondary will re-establish your log chain, just remember to supply the proper option on the restore (STANDBY or NORECOVERY). After the differential is applied to the secondary your existing setup will continue per the usual LS backup, LS copy and LS restore job schedules.That will only work if he didn't take any full backups since he started with Log Shipping!
Agreed. It sounds like he is in the midst of the issue though.
I'm not sure about that, but if he has not make any new full backups your advice will save him some time 🙂
And if he has, your advice will be the way to go 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Thank you for your time and particiapation. i restore the full backup and went on with your advice. it is back on action now. Thank you one more time.
-
jjohnsqldba (9/7/2012)
Thank you for your time and particiapation. i restore the full backup and went on with your advice. it is back on action now. Thank you one more time.You are very welcome!
-
Differentials can indeed be used to repair a LS scenario
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Perry Whittle (9/8/2012)
Differentials can indeed be used to repair a LS scenarioI agree, but for that to work there must be no new full backup since Log Shipping was started. I'm glad that the problem is now solved!
-
Ignacio A. Salom Rangel (9/8/2012)
but for that to work there must be no new full backup since Log Shipping was started.Is that from your experiences?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Perry Whittle (9/8/2012)
Ignacio A. Salom Rangel (9/8/2012)
but for that to work there must be no new full backup since Log Shipping was started.Is that from your experiences?
😉
-
This is moot because the OP got a solution, but since we're discussing...
Taking a FULL backup resets 'the differential base' unless taken with COPY_ONLY. NO_TRUNCATE may or may not reset the base (not sure on the conditions for that) however that is not in play in this scenario.
Resetting the differential base with a new FULL backup means subsequent differential backups will not contain pages modified before that FULL backup.
We actually do not know for sure if a differential would have helped the OP because they went straight to resetting LS from a FULL backup, but if the sequence had gone...
- setup log shipping
- LS backup job plus some other backup process took a log backup
- log shipping broke
...then a differential backup taken from the primary could have been applied to the secondary and the LS jobs would have automatically started succeeding again.
If the sequence had gone...
- setup log shipping
- LS backup job plus some other backup process took a log backup
- log shipping broke
- scheduled job or something else took a new FULL backup
...then a differential backup would not have helped in this case.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (9/9/2012)
This is moot because the OP got a solution, but since we're discussing...Taking a FULL backup resets 'the differential base' unless taken with COPY_ONLY. NO_TRUNCATE may or may not reset the base (not sure on the conditions for that) however that is not in play in this scenario.
Resetting the differential base with a new FULL backup means subsequent differential backups will not contain pages modified before that FULL backup.
We actually do not know for sure if a differential would have helped the OP because they went straight to resetting LS from a FULL backup, but if the sequence had gone...
- setup log shipping
- LS backup job plus some other backup process took a log backup
- log shipping broke
...then a differential backup taken from the primary could have been applied to the secondary and the LS jobs would have automatically started succeeding again.
If the sequence had gone...
- setup log shipping
- LS backup job plus some other backup process took a log backup
- log shipping broke
- scheduled job or something else took a new FULL backup
...then a differential backup would not have helped in this case.
I really like the way you explain it. In this way someone else reading it will know in which cases to use a differential. Well done!;-)
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply