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 Mars; 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.
- 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:
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 = 10MB, FILEGROWTH = 10MB)
LOG ON
(NAME = N'x_log', FILENAME = N'C:\a\x_log.ldf', SIZE = 5MB, FILEGROWTH = 5MB)
GO
ALTER DATABASE x SET RECOVERY FULL
CREATE TABLE x.dbo.t(c1 INT IDENTITY)
INSERT INTO x.dbo.t DEFAULT VALUES --1
BACKUP DATABASE x TO DISK = 'C:\x.bak' WITH INIT
INSERT INTO x.dbo.t DEFAULT VALUES --2
BACKUP LOG x 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 = 9MB, FILEGROWTH = 8MB)
LOG ON
(NAME = N'x2_log', FILENAME = N'C:\b\x2_log.ldf', SIZE = 6MB, FILEGROWTH = 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 INIT, NO_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 x 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 x FROM DISK = ‘C:\x1.trn’ WITH NORECOVERY
RESTORE LOG x 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.