November 22, 2011 at 2:42 pm
I have a logshipping set up and everything is working fine but the question is how do i make the stand by server the primary server when the actual primary server goes down. The database on the standby server is always in a standby/single user mode. i can change it to multi user but how to change the standby mode to active mode?
November 22, 2011 at 2:48 pm
restore database "database name" with recovery
November 22, 2011 at 2:51 pm
This msdn article should give you all the info you need to failover.
November 22, 2011 at 2:53 pm
so basically i have to have a full back up file of the primary server and then preform a restore on the (logshipping server) stand by server using the "with recovery option"?
November 22, 2011 at 3:28 pm
If you already have log shipping setup, then you just need to:
1. Restore all the tran log backups from the primary to the secondary
2. Backup the tran log on the primary WITH NORECOVERY
3. Restore that tran log backup to the secondary WITH RECOVERY
4. Point the clients to the secondary server (now the new primary)
5. Configure log shipping back to the original primary (now the new secondary)
November 22, 2011 at 4:10 pm
Thank you.
November 22, 2011 at 4:12 pm
If all of the logs are already current, all you have to do is issue the command I previoulsy listed. Otherwise, you need to apply any outstanding logs and then issue the "with recovery" statement.
November 22, 2011 at 4:17 pm
Richard Moore-400646 (11/22/2011)
If all of the logs are already current, all you have to do is issue the command I previoulsy listed. Otherwise, you need to apply any outstanding logs and then issue the "with recovery" statement.
This would leave the original primary still online and accessible. This could cause the databases to get out of sync. It is recommended to backup the primary tran log with NORECOVERY so that the primary database goes into an "in recovery" state leaving it inaccessible.
November 22, 2011 at 5:33 pm
The original post asked how it would be done if the primary has gone down. If the primary is truly down or in a failed state, there isn't anyway to issue the command on the existing primary server. If you're talking about a controlled fail over test, then you are absolutely correct. That's not how the original question was phrased.
November 22, 2011 at 5:38 pm
My apologies, I had lost track of the original post.
November 23, 2011 at 3:49 am
you may want to look at renaming a SQL server as well (will try and find a link somewhere) as this is part of our recovery but may not be applicable to you.
we have over 200 apps connecting to a number of servers, so to change all the connection strings would be time consuming, so we rename the Windows host, and use sp_dropserver, sp_addserver to rename the host, change the IP address just incase some developer used the IP and not the hostname, and then restore the database with recovery to bring it online.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply