469,271 Members | 1,324 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Restore LDF file after restoring backups in SQL Server 2005?

I'm working on a restore procedure for the case where all MDF files
are missing, but the LDF files are all intact. A full backup is done
every 24 hours, and a log backup is done every 3 hours. After
restoring the last full + log backups, is it at all possible to use
the LDF files to recover data from that point up to a newer point in
time?

I've found a post which explains how to do this on SQL Server 2000
<http://groups.google.com/group/comp.....ms-sqlserver/
browse_thread/thread/3ef5c7cbc0a83334/f3b0c70811d35ed7>, but step 4
fails with the following error message:
BACKUP LOG cannot be performed because there is no current database
backup.

Aug 22 '07 #1
2 8044
l0b0 (vi************@gmail.com) writes:
I'm working on a restore procedure for the case where all MDF files
are missing, but the LDF files are all intact. A full backup is done
every 24 hours, and a log backup is done every 3 hours. After
restoring the last full + log backups, is it at all possible to use
the LDF files to recover data from that point up to a newer point in
time?
At that point it may be a little late. Say that disaster strikes and
your MDF file is dead. At this point you can perform BACKUP LOG WITH
NO_TRUNCATE to back up the log. Then you restore and apply all logs
including this one.
I've found a post which explains how to do this on SQL Server 2000
<http://groups.google.com/group/comp.....ms-sqlserver/
browse_thread/thread/3ef5c7cbc0a83334/f3b0c70811d35ed7>, but step 4
fails with the following error message:
BACKUP LOG cannot be performed because there is no current database
backup.
It seems that Dan left out one point his post. And which point is fairly
obvious: you need to take a full backup of the newly created dummy database.
Since Dan read this group regularly, I hope is able to step in and clarify.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 22 '07 #2
On Aug 22, 12:38 pm, Erland Sommarskog <esq...@sommarskog.sewrote:
l0b0 (victor.engm...@gmail.com) writes:
I'm working on a restore procedure for the case where all MDF files
are missing, but the LDF files are all intact. A full backup is done
every 24 hours, and a log backup is done every 3 hours. After
restoring the last full + log backups, is it at all possible to use
the LDF files to recover data from that point up to a newer point in
time?

At that point it may be a little late. Say that disaster strikes and
your MDF file is dead. At this point you can perform BACKUP LOG WITH
NO_TRUNCATE to back up the log. Then you restore and apply all logs
including this one.
Thanks! I ended up doing the following:
1. BACKUP LOG database TO DISK = 'C:\database.bak' WITH NO_TRUNCATE;
2. Restore full + log backups without recovery
3. RESTORE LOG database FROM DISK='C:\database.bak' WITH MOVE
'database_log' TO 'L:\database_log.LDF', RECOVERY;

It works fine.

Sep 4 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by phil2phil | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.