March 22, 2011 at 11:19 am
Given your experience, and based on the following scenario, which of the following restore solutions do you think is the best one?
Initial Scenario:
A DBA has been backing up a SQL Server instance as described below:
•Master, MSDB, Model have a full backup performed daily
•User databases have a full backup performed daily, and log backups every hour
•All database and log backups are moved off to a different location immediately after backups are made
•No other HA options are available nor desired
For whatever reason, there is a major hardware failure on the above server and the DBA needs to get the server up and running as fast as possible, and with as few problems as possible. The existing hardware cannot be used and new hardware is required.
What are the best options for the DBA to get back up to speed?
Disaster Scenario Option #1:
•Rebuild a new physical server and install the same version and patch levels of the OS and SQL Server.
•Name the server the same name as the old server.
•Restore the backups of the old server to the new server, including Master, MSDB, Model, and all user databases
•If the DBA follows all these steps, will everything be exactly like it was before, or will the DBA have to make some modifications to get things running again exactly as before? If so, what are these steps?
Disaster Scenario Option #2:
•The DBA already has a physical server with the same version and patch levels of the OS and SQL Server.
•The name of the server is different than the old server.
•Should the DBA rename the existing server to the older server’s name, then restore the Master, MSDB, Model, and all user databases?
•If the DBA follows all these steps, will everything be exactly like it was before, or will the DBA have to make some small modifications to get things running again exactly as before? If so, what are these steps?
Disaster Scenario Option #3:
•The DBA already has a physical server with the same version and patch levels of the OS and SQL Server.
•The name of the server is different than the old server.
•Should the DBA leave the existing server name as is, but only restore the user databases, not restoring the original Master, MSDB, and Model databases.
•This of course means the DBA would have to fix user logins and recreate jobs, and applications accessing the server will have to be pointed to the new server name.
Which of these options would you choose and why? Or, do you have a better variation that fits the criteria listed above (log shipping, database mirroring, clustering, etc. should not be considered in this scenario).
March 22, 2011 at 12:12 pm
Why does this look like class homework to me?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 22, 2011 at 12:17 pm
GSquared (3/22/2011)
Why does this look like class homework to me?
It isn't. I am trying to find out what most people do in this situation, as I want to follow the best practice. I have searched the Internet and have yet to find a satisfactory explanation.
March 24, 2011 at 6:34 am
I'm not sure how these are realistic scenarios and they definitely aren't apples-apples comparisons.
If you already have hardware available, you already have hardware available, and then you don't need to do option 1. If you don't have hardware available, then options 2 & 3 aren't possible. And why are options that don't involve massive amounts of downtime not up for consideration (log shipping, replication, clustering, mirroring, et al)?
Honestly, the best way to learn how to do disaster recovery is to practice it. Build a couple of virtual machines, put SQL Server on them (Dev Edition is cheap enough to play with this way, and you only need one license if you set it up right). Then practice crashing one of them and getting the other to take over from it. Restore master and msdb from one to the other and learn first-hand what it takes.
If you do that kind of thing, then you'll have a better idea what issues you can run into, and you'll be prepared for them if they ever happen to you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply