Attach DB with corrupt .ldf?

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_single_file_db, and sp_add_data_file_recover_suspect_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.[posted and mailed]

NF (natalyafaden@.bellsouth.net) writes:
> 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_single_file_db, and sp_add_data_file_recover_suspect_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.

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@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

attach a database

I am trying to attach a database , using sp_attach_db command, that has one
MDF file and one LDF file. The LDF file was corrupt so that did not work.
I then I tried the sp_attach_single_file_db command, I got an error could no
t open the MDF file.
Any ideas on what the problem is and how to proceed?
Thanks a lot in advanceIn order to attach a database, you need to have detached it first. It is
possible that your mdf and ldf files were just copied over after the SQL
Server service was stopped. If that was the case, you could try the steps
provided by Jasper Smith at http://tinyurl.com/2o9w8
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"Daneil Hope" <anonymous@.discussions.microsoft.com> wrote in message
news:CA65F4FA-FF0E-4DC2-88C6-E7A7D1599646@.microsoft.com...
> I am trying to attach a database , using sp_attach_db command, that has
one MDF file and one LDF file. The LDF file was corrupt so that did not
work. I then I tried the sp_attach_single_file_db command, I got an error
could not open the MDF file.
> Any ideas on what the problem is and how to proceed?
> Thanks a lot in advance|||Thanks a lot Peter. I will try that.

attach a database

I am trying to attach a database , using sp_attach_db command, that has one MDF file and one LDF file. The LDF file was corrupt so that did not work. I then I tried the sp_attach_single_file_db command, I got an error could not open the MDF file.
Any ideas on what the problem is and how to proceed?
Thanks a lot in advance
In order to attach a database, you need to have detached it first. It is
possible that your mdf and ldf files were just copied over after the SQL
Server service was stopped. If that was the case, you could try the steps
provided by Jasper Smith at http://tinyurl.com/2o9w8
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"Daneil Hope" <anonymous@.discussions.microsoft.com> wrote in message
news:CA65F4FA-FF0E-4DC2-88C6-E7A7D1599646@.microsoft.com...
> I am trying to attach a database , using sp_attach_db command, that has
one MDF file and one LDF file. The LDF file was corrupt so that did not
work. I then I tried the sp_attach_single_file_db command, I got an error
could not open the MDF file.
> Any ideas on what the problem is and how to proceed?
> Thanks a lot in advance
|||Thanks a lot Peter. I will try that.