Restore 2

[phonehelp]

Has anyone ever restore the database from a very old backup and the most recent .LDF file. The main MDF is lost /gone.

 

[mrdenny]

You can't restore from an ldf file. You would need the transaction log backups.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

.LDF files are created by the restore, not part of the restore process. As MRDenny said, all you can do is backup from the file and make sure you're not sending the Transaction file to the same exact path as the current .LDF or the process will error out.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLBill]

To add.....

You can't mix what you restore. If you want to restore the .LDF file, you must be restoring the .MDF file that goes with the .LDF file. (And it's not really a restore, you are just putting the files back).

If you are using SQL Server commands to restore the database (RESTORE DATABASE), then you must also use the RESTORE LOG command and restore backups of the log files. However, log backups can only be restored to the most recent full backup as that's the one they are 'chained' to. And you must restore ALL the log backups since that full backup.

-SQLBill

Posting advice: FAQ481-4875

 

[phonehelp]

Thanks all.

After restoring October's backup I was able to export the data in from the LDF files and apply them to the database.

 

[erato]

I'm in a similar scenrio only my full backup is 12 hours old and I lost my mdf file and all but the very last transaction log backup but still have the ldf file. I'm trying to use the full backup and the ldf file to restore to a more recent point.

My questions to phonehelp are this:

1. How did you export the data from the ldf file?
2. Did you have any transaction log backups taking place between your full backup & the date of failure? (I'm asking because my transaction log was most likely truncated when my transaction log backups took place)

To anyone else:

Is there anything else I can do to restore to a more recent point?

 

[ptheriault]

erato,
You won't be able to restore from that last transaction log backup. When you restore log files they must be done by the LSN (log sequence number). If you numbers are out of sync then the log won't restore . Your only going to be able to restore you full backup from 12 hours ago.
You might be able to recreate some of the transactions that are currently in you log file and that log bak file you have but you would need a tool like log explorer by lumigent.

- Paul
- Database performance looks fine, it must be the Network!

 

[phonehelp]

Q1 : What is the size of the Ldf file?

The key is that the LDF file is a record of all transactions "insert ;update; delete;etc..” carried out against the database. It also contains the corresponding data for every transaction.