September 7, 2010 at 8:38 am
Guys,
I have setup log shipping for our OLTP database - the restore on the secondary occurs each night. However, if my primary database fails what are steps that will need to be followed for me to recover from the secondary database especially since the secondary in single user/read only mode and that we cannot backup by the secondary database.
Any inputs/suggestions would help.
Thanks
September 7, 2010 at 8:45 am
1) change the database back to operation mode
2) restore the latest log with recovery.
3) once your primary up, then if you wish you cna put secondary back to read only.
----------
Ashish
September 7, 2010 at 9:38 am
1) change the database back to operation mode
2) restore the latest log with recovery.
3) once your primary up, then if you wish you cna put secondary back to read only.
After step2 assuming that primary db crashes, I can take backup of secondary at step 2 and restore it as primary. AT that point I will start the log shipping from the primary again to ship log to the secondary. Is this possible?
September 7, 2010 at 9:43 am
yes why not.
Its just name and you need to decide which one to be primary and which one to be secondary.
At any point of time, you can switch to either.
----------
Ashish
September 7, 2010 at 10:06 am
In case that was a bit unclear, there's a few steps here...assuming you have logs taken at regular intervals (every 15 mins, every hour, etc):
1) Run the restore job to catch up on all logs on the secondary
2) Run 'RESTORE DATABASE [name] WITH RECOVERY' to bring it online
3) Disable log backup/copy/restore jobs while the primary is down
When the primary server is available again...
1) Take app offline, set secondary database to read_only
2) Take full backup, restore onto primary, also restore onto secondary (WITH STANDBY, as you would usually set up log shipping)
3) Make sure primary is set to full recovery and enable log backup/copy/restore jobs again
There is no way to switch back to the master without having downtime of some kind on the app, because the only way you can truly have a dual-master setup is with peer to peer replication. There is also no way to bring a database that is operational back into standby or norecovery mode.
September 7, 2010 at 10:57 am
IF the transaction log of the primary is available when the primary fails run a backup of the tail of the log - that is
backup log dbname to disk = 'pathname' with norecovery
copy that last log to the secondary and restore that to the secondary with the recovery option to bring the secondary online.
With logshipping the secondary is meant to be there to service your application in the event of the loss of the primary, rather than just a source to restore the database from.
---------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply