Attach DB with corrupt .ldf?

[NF]

Hey gang,

I've been trying to restore a DB from it's MDF and LDF all morning;
the catch? The LDF appears to be corrupt.

When I first started the SQL Server, the db in question was marked as
"Suspect'. I did some research on this and it has caused me to attempt
detaching, backing up, deleting the LDF, using ATTACH DB and even
sp_attach_singl e_file_db, and sp_add_data_fil e_recover_suspe ct_db.

Nothing works.

So, with an MDF and no LDF ... is it possible to recreate this
database somehow? Can I attach the MDF to another (empty) database to
retrieve it's contents? (Tried it, couldn't get it to work), can I
extract the contents of the MDF - even if it's just the objects and
not the data itself - in some capacity?

I've read in various places about attaching an MDF with no LDF and the
system will recreate the LDF as needed, but that doesn't work either.

Ideas are most, most, most welcome.

[Erland Sommarskog]

Re: Attach DB with corrupt .ldf?

[posted and mailed]

NF (natalyafaden@b ellsouth.net) writes:[color=blue]
> I've been trying to restore a DB from it's MDF and LDF all morning;
> the catch? The LDF appears to be corrupt.
>
> When I first started the SQL Server, the db in question was marked as
> "Suspect'. I did some research on this and it has caused me to attempt
> detaching, backing up, deleting the LDF, using ATTACH DB and even
> sp_attach_singl e_file_db, and sp_add_data_fil e_recover_suspe ct_db.
>
> Nothing works.
>
> So, with an MDF and no LDF ... is it possible to recreate this
> database somehow? Can I attach the MDF to another (empty) database to
> retrieve it's contents? (Tried it, couldn't get it to work), can I
> extract the contents of the MDF - even if it's just the objects and
> not the data itself - in some capacity?
>
> I've read in various places about attaching an MDF with no LDF and the
> system will recreate the LDF as needed, but that doesn't work either.[/color]

There is a way, but I am not going to post it, because it's a
path too dangerous.

My first advice is that you open a case with Microsoft support. Yes,
that will cost you an arm and a leg, but consider how many arms and
legs losing the data will cost you. They have the procedures to recover
as much as possible.

My second advice is to restore from a clean backup.

The way I know means building a log from nothing and at all, and lead
you into the MDF as it was when things went bad. You may find a very
good database. You may also find a database that is just a big mess,
because you got it mid-transaction. And the fact that the LDF is corrupt
is an indication of that. Corruption may exist both in the SQL Server
structures and in your own data.

And if you don't understand the essence of what I'm saying above, don't
ask any further questions, but get on the phone with Microsoft.

If you absolutely want to do this on your own as a last resort before
you throw the database away, drop me a mail.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp