How to recover the Database with full Transaction Log

Question

How to recover the Database with full Transaction Log

godwin dsouza

Valued Member

Points: 70
More actions
June 13, 2007 at 8:41 am

#117435

I have to recover a database from a full transaction Log. The data got corrupted and I have to go back to the state the database is on June 10, 2007.
The ldf file is dated June 11, 2007.
The mdf file is dated June 12, 2007. The previous bak file I have is from Dec 6, 2006
How can I achive this?
Thank you.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

Restore the full database backup that was taken before jun 10th. Then if you have any differentials backup restore the same. else restore the log back point in time to where you want to recover. remember that the last restore should be with recovery clause and the others with no recovery clause.

By the way do you mean to say that you dint take any backups after 6th Dec, 2006.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

godwin dsouza Valued Member Points: 70 More actions · Answer 2

Yes, the last full database backup that we have is from Dec. 06

There are no diffierential backups.

After I restore the database from bak file, take it offline, copy the ldf file. What are the commands I have to run? Please note that this is SQL server 2005.

Appreciate your help.

mngong SSCertifiable Points: 6167 More actions · Answer 3

Not sure I understand full transaction log properly .Have you ever had a transaction log or full backup at any time between Dec -06 and June 12..

Mike

godwin dsouza Valued Member Points: 70 More actions · Answer 4

We have the transaction log (*.ldf) file with recovery option: full. But there is no backup of the database between between Dec -06 and June 12.

This is what I have:

db.ldf dated 11 Jun 2007

db.mdf dated 12 Jun 2007

db.bak dated 6 Dec 2006

Thank you.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 5

Are you sure you have not taken any backups after dec 6th. If so you can try this. Take a full backup as of now. Then take a transaction log backup. Then do a point in time restore as of the point in failure. The mdf and ldf files are database files and hope you can't restore them to point in time unless you have a third party supported tool.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

mngong SSCertifiable Points: 6167 More actions · Answer 6

I believe if you take a full backup a transaction log backup will be the changes after the full backup which you probably do not need.

Fortunately just with a full backup in 2005 you should be able to restore to a point in time.

Mike

skeane SSC Eights! Points: 877 More actions · Answer 7

If I understand this correctly, then you have a database in Full recovery mode, a full backup of the database from December, and you have never done a log backup (at least not since the December backup.) If this is correct, then you should:

1) Do a fresh full database backup (BACKUP DATABASE...). If the rest of this fails, you will be left with a choice of returning to your current database or returning to December. Taking a backup now is what gives you this choice.

2) Do a log backup (BACKUP LOG ...). This will backup the transactions since the last log backup (or since the database was created if the log has never been backed up.)

3) Restore the December backup but don't recover it (RESTORE DATABASE ... WITH NORECOVERY)

4) Restore the log backup from step 2 to a point in time (RESTORE LOG ... WITH STOPAT ...)

See BOL for the full syntax.

Oh, I almost forgot:

5) Create a proper backup/recovery plan that includes regular database and log backups.

In the event that all of this fails, now would be a good time to update your resume.