THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

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 backup
1: db backup
...
2: log backup
3: db backup
crash
Here we will use backup 3 when we later will restore.

B) Db backup before log backup
1: db backup
...
2: db backup
3: log backup
crash
Here 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_TRUNCATE
Yes, it really is that simple. Then restore backups from above, including this last log backup. Don't believe me? Test it.

  1. Create database and table
  2. Insert some data
  3. Do db backup (1)
  4. Insert some more data
  5. Do log backup (2)
  6. Insert some more data
  7. Stop SQL Server
  8. Delete mdf file
  9. Start SQL Server
  10. Do log backup using NO_TRUNCATE (3)
  11. 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:

IF DB_ID('x'IS NOT NULL DROP DATABASE x
GO
CREATE DATABASE x
ON PRIMARY 
(NAME N'x'FILENAME N'C:\a\x.mdf'SIZE 10MBFILEGROWTH 10MB)
LOG ON 
(NAME N'x_log'FILENAME N'C:\a\x_log.ldf'SIZE 5MBFILEGROWTH 5MB)
GO
ALTER DATABASE SET RECOVERY FULL
CREATE TABLE 
x.dbo.t(c1 INT IDENTITY)
INSERT INTO x.dbo.t DEFAULT VALUES --1
BACKUP DATABASE TO DISK = 'C:\x.bak' WITH INIT
INSERT INTO x.dbo.t DEFAULT VALUES --2
BACKUP LOG TO DISK = 'C:\x1.trn' WITH INIT
INSERT INTO x.dbo.t DEFAULT VALUES --3
SELECT FROM x.dbo.t

--Stop SQL Server and delete below file
--C:\a\x.mdf
--Start SQL Server

--Oops, damaged database...:
SELECT FROM x.dbo.t

--Stop SQL Server, pretend installation is toast

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":

IF DB_ID('x2'IS NOT NULL DROP DATABASE x2
IF DB_ID('x'IS NOT NULL DROP DATABASE x
GO
CREATE DATABASE x2
ON PRIMARY 
(NAME N'x2'FILENAME N'C:\b\x2.mdf'SIZE 9MBFILEGROWTH 8MB)
LOG ON 
(NAME N'x2_log'FILENAME N'C:\b\x2_log.ldf'SIZE 6MBFILEGROWTH 7MB)
GO

--Stop SQL Server and delete below files
--C:\b\x2.mdf
--C:\b\x2_log.ldf

--Copy the C:\a\x_log.ldf to C:\b\x2_log.ldf

--Start SQL Server

--Produce our last log backup:
BACKUP LOG x2 TO DISK = 'C:\x2.trn' WITH INITNO_TRUNCATE

--Restore the database, up to last transaction.
--Investigate logical file names for MOVE options first:
RESTORE FILELISTONLY FROM DISK = 'C:\x.bak' 

RESTORE DATABASE FROM DISK = 'C:\x.bak' 
WITH
 
NORECOVERY
,MOVE 'x' TO 'C:\b\x.mdf'
,MOVE 'x_log' TO 'C:\b\x_log.ldf'

RESTORE LOG FROM DISK = 'C:\x1.trn' WITH NORECOVERY
RESTORE LOG FROM DISK = 'C:\x2.trn' WITH RECOVERY

--Data there?
SELECT FROM x.dbo.t
--See? That wasn't so difficult.

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!

Case closed.


 

Published Saturday, March 27, 2010 12:50 PM by TiborKaraszi
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Hobbes said:

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.

March 27, 2010 12:36 PM
 

Mohd Sufian said:

Great Stuff , Great learning.

March 27, 2010 2:11 PM
 

TiborKaraszi said:

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

March 27, 2010 2:33 PM
 

Hobbes said:

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.

March 28, 2010 1:13 AM
 

Uri Dimant said:

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

March 28, 2010 1:20 AM
 

TiborKaraszi said:

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

March 28, 2010 3:20 AM
 

James Luetkehoelter said:

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.

March 28, 2010 6:32 PM
 

dugi said:

Nice job, Tibor!

March 30, 2010 7:34 AM
 

James said:

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.

April 5, 2010 11:26 AM
 

bblack said:

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

April 5, 2010 12:36 PM
 

Swaroop said:

Thanks for sharing TiborKaraszi...

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

April 6, 2010 3:17 AM
 

Zafer said:

Very helpful. Thank you.

April 16, 2010 11:20 AM
 

Alex Feng said:

This blog post is great and useful. Thnaks Tibor!

August 19, 2010 3:23 AM
 

Hey Tibor said:

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.

September 20, 2011 10:46 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement