restoring broken .mdf and/or .ldf

[alexaweb]

Hi, I have a part of an .mdf file and the complete .ldf file of a SQL2000 database.

1. Can I restore the DB from the .ldf file by itself?
2. Can I restore the part that is stored inside the broken .mdf file (to recover at least some sp's for instance)?

Thanks,
Alexander

 

[MaffewW]

Hi

1) No you cant restore from just the .ldf file, this is the transactions made since the last database backup
2) When you say you have part of the .mdf file, how do you mean when you say its broken?

 

[alexaweb]

I have never made a backup of this database, shouldn't then the ldf contain all the transactions made?

Whe I mean the file is broken (or i only have part of it) is that I got the initial 80 MB out of a total of 100MB that made the whole file. (The other twenty are on a defective Harddrive that was part of a single volume - I could only recover the initial 80MB which where on a healthy diskdrive)

 

[SQLBill]

To do a restore you need to have a full backup. You restore the full backup with no recovery and then restore any differential or transaction log backups after the full backup. But the point is, you must have a full backup to do a restore.

-SQLBill

 

[MaffewW]

Yes, unless anyone else has any ideas, I think you're out of luck. Like SQLBill said, to do a restore you would need a full back up. If you had the full mdf file you could use the sp_attach_db option to reattach the files on a new machine.

For future reference the syntax would be

EXEC sp_attach_db @dbname = 'yourdbnmame',
@filename1 = 'physicalpath\yourdata.mdf',
@filename2 = 'physicalpath\yourlog.ldf'

 

[tlbroadbent]

Never made a backup!? Hard lesson to learn, right?

While there is no way to restore the data using SQL Server, you can probably get help from a company specializing in data recovery. I've not used any of this kind of service but have seen ads. Perform a web serach for SQL Server data recovery and see if you can find some help. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[SQLBill]

alexaweb,

The reason just having the .ldf files won't work is that when done properly, you will have done at least one full backup. Then the transaction log is 'marked' as belonging to that full backup. Without a full backup being done (as in your case), the transaction log is 'marked' as belonging to the initial install/creation of the database it goes with. So even if you reinstall/recreate the database, the database will have a new 'identifier' that won't match the 'identifier marking' on the transaction log. I believe the date/time of the full backup and/or db creation is used for the 'identifier'.


-SQLBill