August 30, 2013 at 6:01 pm
I have to test and document possible recovery scenarios in SQL Server 2008 R2.
I can think of the following scenarios. Can you please suggests me more scenarios?
1. restore a database that was accidentally deleted
2. recover the database to point in time
3. recover the database if mdf file is deleted
4. recover the database if ldf file is deleted
5. restore a table that was accidentally deleted from a database
Thanks
August 31, 2013 at 6:16 am
Some of the scenarios you list are more or less the same. If someone deletes a file from the database, this is more or less the same as if the entire database was dropped. The exception is possibly if you have a multi-filegroup database, and someone drops a file from a secondary filegroup.
Rather than the database being dropped, it could also be that the database is corrupt due to mal-functioning hardware.
You mention restore to point-in-time, and in disaster-recovery scenarios this typically what you want. That is, you want to restore to a point in time, you believe to be good.
Specific scenarios involves piecemeal restore where you only recover the primary filegroup initially so that some data can be accessed. Whether to test this depends on whether this is a need for you organisation.
The one scenario that is different from the rest in your list is the last one. It does not have to be a table that has been dropped, but a substantial amount of data have been deleted or incorrectly updated - and you don't want to restore the entire database to the point before the accident occurred. In this case you need to restore a backup in a parallel database and recover the from that database.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 31, 2013 at 12:26 pm
Can you please list out some recovery scenarios in general to test and document?
August 31, 2013 at 12:45 pm
I tried to discuss that in my post, but here is a more concise list of the basic scenarios:
1) Restore most recent backup + logs to the same database.
2) Restore backup + logs to a database on the same server.
3) Restore backup + logs to a different server. Note! Need to test that logins etc work.
4) Restore a week old backup + logs. (Your most recent backup may include corruptions.)
5) Restore backup + log to a certain point in time.
6) Restore backup + tail-log backup, this can save you when the MDF file is damaged.
If you have databases that have multiple filegrops and/or filestream data, this may adds more scenarios to test.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 3, 2013 at 2:03 pm
Don't forget restore damaged instance. That is the most complex recovery. Particularly test for master, model and msdb.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 3, 2013 at 2:24 pm
ScottPletcher (9/3/2013)
Don't forget restore damaged instance. That is the most complex recovery. Particularly test for master, model and msdb.
Very good point, Scott!
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply