Restore database to the point of disaster

This is really basic, but so often overlooked and misunderstood. Basically, we have a database, and something goes south. Can we restore all the way up to that point? I.e., even if the last backup (db or log) is earlier than the disaster? Yes, of course we can (unless for more extreme cases, read on), but many don't realize/do that, for some strange reason.

This blog post was inspired from a thread in the MSDN forums, which exposed just this misunderstanding. Basically the scenario was that they do db backup and only log backup once a day. Now, doing log backup that infrequent is of course a bit weird, but that is beside the point. The point is that you can recover all the way up to the point of disaster. Of course, it depends on what the disaster is (don't expect too much if the planet blows up, for instance).

Since "log backup only once a day" was mentioned, I will first elaborate a bit on frequency for database vs log backups. For the sake of discussion, say we do both db and log backup once a day. You say:"What? Both db backup and log backup once a day - why would anybody do that way? Wouldn't one do log backup more frequently than db backup?" Yes, of course (but I actually see such weird implementations from time to time). But again, that doesn't change the topic at hand, but I will first elaborate on this; just so we don't see blurring comments later arguing this irrelevant argument.

So, lets first sort out two different cases:

A) Log backup before the db backup1: db backup...2: log backup3: db backupcrashHere we will use backup 3 when we later will restore.

B) Db backup before log backup1: db backup...2: db backup3: log backupcrashHere we will use backup 2 and 3 when we later will restore.

You see that A) and B) are really the same thing? What is relevant is that we have all log records available (in ldf file/log backups) since the db backup we chose to use as starting point for the restore. Actually, for A), we could might as well use backup 1 and 2 (and skip 3)!

"Hang on", you say, "we're not done yet. What about the modifications since the last log backup! Gotcha!"No worries, this is where it gets interesting, and below is really the heart of the topic. Clearly, we need to get the log records out of the ldf file into a log backup (file). If we can do that, then we will call this backup number 4, and use as the last backup for our restore. After doing that restore, we have no data loss!

So, how do we produce a log backup after a disaster?It depends on the disaster! Let's discuss a few scenarios:

a) Planet Earth blows up.No can do. I doubt that anyone of you has mirrored data centers on Moon or March; and also people stationed off-Earth for these situations. Of course, I'm being silly. But my point is that you can always have a disaster such that you can't produce that last log backup. No matter how much you mirror: if the disaster takes out all mirrors, then you are toast. Remember that when you talk SLA's. That fact is not popular, but it can't be argued. It is all about limiting the risk exposure - not eliminating it. Anybody who believes we can eliminate risk exposure is dreaming. Agreed? Good. Let's move on to (hopefully) more realistic scenarios:

b) Something happens with the data file(s), lost, corrupt or so. Ldf file is still there.This is the easy case, but so often overlooked. What you do now is to backup the log of the damaged database, using the NO_TRUNCATE option. Something like:BACKUP LOG dbname TO DISK = 'C:\dbname.trn' WITH NO_TRUNCATEYes, it really is that simple. Then restore backups from above, including this last log backup. Don't believe me? Test it.

Create database and table

Insert some data

Do db backup (1)

Insert some more data

Do log backup (2)

Insert some more data

Stop SQL Server

Delete mdf file

Start SQL Server

Do log backup using NO_TRUNCATE (3)

Restore 1, 2 and 3.

c) Something happens with the database. Ldf file is NOT still there.Clearly, if the ldf file is really gone, we can't do a log backup - how much as we might want to. Remember the old days, when redundancy for disks (RAID) wasn't as common as today? "If there's anywhere you want redundancy, it is for the transaction log files!"

d) Something happens with the data file(s), lost, corrupt or so. Ldf file is still there. The installation is toast - we can't start SQL Server.This seems a bit more nerve-wracking, right? Not to worry, just do the right steps and you will be fine. You probably ask now:

"But how can we backup the transaction log when our SQL Server won't start?"

That is a good question. You need to get that ldf file to a healthy SQL Server, and make SQL Server believe this is the ldf file for a broken database on that instance. It is not really complicated. Just use a dummy database on that SQL Server as intermediate - to get the right meta-data into that SQL Server, so in turn it will allow you to produce this last log backup. I will show just that:

I have two instances on my machine (named "a" and "b"). I will create and damage a database on instance a, and then produce a log backup for that orphaned ldf file a different instance, b. I will pretend these are on two different machines, using separate folders for the database files "C:\a" and "C:\b". Here's the T-SQL, starting with instance a:

Do we agree that we have a damaged database, and there has been done modifications since the last log backup? Ok, fine. We now pretend that SQL Server instance "a" doesn't start anymore. So, I will try to produce a log backup from that ldf file on instance "b":

Note how I even named the dummy database differently on instance b, with different physical file names and different file sizes (all compared to what we had on instance a). Typically, you will use same database name and same filename, but I want to show that we don't really have to know a whole lot about the damaged database in order to produce a log backup from the ldf file!

Comment Notification

Comments

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?

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:

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.

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. :-)

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.

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?

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.