Restore database to the point of disaster

re: Restore database to the point of disaster

Hobbes — Sat, 27 Mar 2010 16:36:24 GMT

Thank you for a great article. I'm a newbie and this really helps me a lot.

But, if I have a mirroring configuration, with 2 hard disks, and this would mean that each hard disk would have the mdf and ldf files of my database. If any one of my hard disk crashes I can easily let the other hard disk take over. Does this mean the same as point-in-time recovery, and that I do not need to do any log backups?

Thank you.

re: Restore database to the point of disaster

Mohd Sufian — Sat, 27 Mar 2010 18:11:10 GMT

Great Stuff , Great learning.

re: Restore database to the point of disaster

TiborKaraszi — Sat, 27 Mar 2010 18:33:50 GMT

Thanks, glad you liked it. :-)

Hobbes: No, I think you have a couple of things a bit backwards.

First, you mention mirroring, but not at what level (could be RAID level, could be some SAN stuff, could be database mirroring as implemented in SQL Server). So, it is difficult to be precise in answer without knowing more about your proposed solution.

Second: If you ever expect to do point in time restore, or up-to-minute restore (whatever you want to call it), you *need* to do log backups. First, the ldf file can be lost or corrupt, meaning that your backups are all you have! Second, if you don't do log backups, nothing will empty the log and you will have huge ldf file after a while.

You mention you are a newbie. That is fine, we all were at some point. Might I suggest a good reference, but oftern overlooked: Books Online. It has good elaborations on the topic, even complemented with nice diagrams etc. For instance:

http://msdn.microsoft.com/en-us/library/ms190217.aspx

re: Restore database to the point of disaster

Hobbes — Sun, 28 Mar 2010 05:13:16 GMT

TiborKaraszi, thank you for your kind reply. I'm actually referring to a raid 1 mirroring setup. So I'm thinking since I have redundancy this way, maybe I can just use simple recovery model and skip the log backups.

But you mentioned that there is a possibility that the database files (mdf or ldf or both) could be corrupted. So there's a risk.

I will also read more about this in the Books Online. If you have any more advice for me regarding this scenario I mentioned, I'd appreciate it.

Thank you for your help.

re: Restore database to the point of disaster

Uri Dimant — Sun, 28 Mar 2010 05:20:09 GMT

Hi Tibor

Great, I always tried to create the database wuth same database name and same filename... learned something today...

Interesting in how does it work , I think the key work is NO_TRUNCATE... here

re: Restore database to the point of disaster

TiborKaraszi — Sun, 28 Mar 2010 07:20:36 GMT

Hi Uri,

Exactly. What we do is to produce a damaged database, but on a different instance than the originating instance. And when you want to do a log backup for a damaged database, you use the NO_TRUNCATE option. :-)

re: Restore database to the point of disaster

James Luetkehoelter — Sun, 28 Mar 2010 22:32:46 GMT

Kudos Tibor - we need more post like this that visit some (seemingly) very basic things. There's a wealth of people with the responsibility to perform these functions with little or no understanding on how to do it.

One with with blog posts is that I find people often wanting to talk about something relatively advanced or esoteric, and in turn those with very little experience cling to small piece of wisdom or bon mots as if they were gospel. The results are rarely pleasant.

Please keep up with these types of posts - and everyone else I encourage you to speak more to the majority than our peers.

re: Restore database to the point of disaster

dugi — Tue, 30 Mar 2010 11:34:14 GMT

Nice job, Tibor!

re: Restore database to the point of disaster

James — Mon, 05 Apr 2010 15:26:56 GMT

Hi Hobbes,

My advice is, "Don't skip the backups." RAID mirroring is a great technology, but ultimately you can still have failures other than single disk outages. I've had mirrors get damaged by a RAID controller failure. I've also had a mirror where both disks came from a bad batch and failed. While both of these scenarios are admittedly rare, both have happened to me in the course of my career. Also, this doesn't even cover other ugly scenarios like a virus getting on to the server, etc. Ultimately the degrees of redundancy and type of technologies that you choose come down to a risk assessment. How much chance of a data loss are you willing to accept?

James.

re: Restore database to the point of disaster

bblack — Mon, 05 Apr 2010 16:36:45 GMT

Great tip. I would not have thought this would work. I have learned.

re: Restore database to the point of disaster

Swaroop — Tue, 06 Apr 2010 07:17:25 GMT

Thanks for sharing TiborKaraszi...

Nice research for making it understandable to newbies like me with all the basic terms.

re: Restore database to the point of disaster

Zafer — Fri, 16 Apr 2010 15:20:40 GMT

Very helpful. Thank you.

re: Restore database to the point of disaster

Alex Feng — Thu, 19 Aug 2010 07:23:26 GMT

This blog post is great and useful. Thnaks Tibor!

re: Restore database to the point of disaster

Hey Tibor — Tue, 20 Sep 2011 14:46:11 GMT

I am looking for job in SQL Server as DBA. so today i have done one interview. in that they asked few questions like,

01. how to do point in time recovery in following situation.

Weekly Full Backup at 11:30 PM

Daily Deferential Backup at 12:00 Mid Night

Every 60 Minuts Transaction Log Backup.

One day database got crashed at 9:30 in the morning.

all the backups are in secured disk, so tell me how to do PIR without loosing that 30 Mins Data.

02. Why we need to create only one clustered index for each table why con't we create more clustered indexes as Non Clustered Indexes.

03. There is a Database which is configured for Mirroring it has High Protection Mode. Now the client want to change from High Protection mode to High Performance Mode without disturbing the Mirroring.

Please tell me the answers.