| Author |
Topic |
|
rohaandba
Starting Member
33 Posts |
 Posted - 2012-09-06 : 23:03:22
|
| 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)Thankyou |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-09-07 : 02:44:11
|
| Restore the ful, or take a diff and restore that.-Chad |
 |
|
|
rohaandba
Starting Member
33 Posts |
Posted - 2012-09-07 : 03:26:36
|
| HI,The secondary database is still on the StandBY\ReadOnly mode.Is there anyway I can drop the database orRestore the full backup from primaryThanks |
|
|
|
JJSQLDBA
Starting Member
23 Posts |
Posted - 2012-09-07 : 10:02:23
|
| Hi Rohaan,I would like to get a clear picture about the scenario. Correct me if I am wrong. This is my understanding about the issue.You have logshipping set up for your production server. And it was working fine until you have set up a backup strategy. After you set up your backup strategy your log shipping has failed. The secondary database is in standby/read-only mode. You want to bring back the log shipping in previous state.Correct me if I am wrong? |
|
|
|
rohaandba
Starting Member
33 Posts |
Posted - 2012-09-07 : 10:07:54
|
| Yes Perfect, You are right.Thanks. |
|
|
|
JJSQLDBA
Starting Member
23 Posts |
Posted - 2012-09-07 : 11:11:45
|
| 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). |
|
|
|
JJSQLDBA
Starting Member
23 Posts |
Posted - 2012-09-07 : 16:43:40
|
| 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. --it only works if you didn't take any full backups since he started with Log Shipping.**Please update us what your status is now. Thank you in advance for sharing your experience. |
|
|
|
rohaandba
Starting Member
33 Posts |
Posted - 2012-09-08 : 02:58:03
|
| Thanks you, Your sugessions really helped me. |
|
|
|
JJSQLDBA
Starting Member
23 Posts |
Posted - 2012-09-08 : 08:28:32
|
| So how did you resolve the issue. Can you briefly explain it? |
|
|
|
rohaandba
Starting Member
33 Posts |
Posted - 2012-09-08 : 14:29:26
|
| Disabled the additional backup job created on the Database. •Stopped all LS jobs for this database•Took a differential backup on the primary•Restored this differential to the secondary•Enabled and start LS backup job on primary•Enabled and start LS copy job on secondary•Enabled and start LS restore job on secondary•Checked for successThank you. |
|
|
|
JJSQLDBA
Starting Member
23 Posts |
Posted - 2012-09-09 : 14:16:06
|
| Thank you Rohan for sharing the experience. |
|
|
|
|
LogShiping Failed