Recovering MDF

[NotSoCunning]

Hi there,

I've spent the last day or so trying to work out a way of recovering data from an MDF that has a missing or out of data LDF file and to be honest I'm struggling.

I'm using SQL 2000. I've tried to attach the database with the out of date logfile, this reports an error 9003 indicating that the logfile not correct. I've tried deleting the logfile and attaching it, but it then complains that the logile location is invalid.

I have tried using sp_attach_single_file_db with no success, I've even tried the undocumented log rebuild command with again no success.

Does anyone have any suggestions on accessing the data? I can't believe that a valid mdf file containing valid data is suddenly inaccessible.


Background:

The mdf is a replicated copy from a live situation and has real time replication from it's source (this is paused when I try and do anything with it), the source can not be detached to copy and backups are out of date within seconds. There is a 2nd filegroup that contains rapidly changing information that is not vital and so I am using an out of date copy of this (shouldn't effect the main up to date mdf so I've been told). I can not replicate the logfile because of bandwidth restrictions (data changes at a rate of upto about a 1mb a second and simply generates a massive queue on the source server) as the destination is over a WAN.

 

[ptheriault]

when you have tried to attach the db through Enterprise Manager are you supplying a log file and location? If so don't. Completely remove any reference to the log file by clicking remove file. It should create the log file for you in the default location.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[NotSoCunning]

I'm specifying the mdf, the logfile and location are automatically filled. If I remove the location I get 'The physical location is invalid' error with regard to the log.

 

[ptheriault]

you want to remove the log file not the location of the log file. If you are using TSQL from QA it should look like this.

EXEC sp_attach_db @dbname = N'pubs', 
   @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[aalmeida]

Better use the following Sproc:

sp_attach_single_file_db [ @dbname = ] 'dbname'
, [ @physname = ] 'physical_name'

Exemple:

EXEC sp_attach_single_file_db @dbname = 'pubs',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

I hope this helps you.


AL Almeida
Senior DBA

http://www.saxotech.com/

"May all those that come behind us, find us faithfull"

 

[NotSoCunning]

Thanks for the tips, I think I tried that. I did a full mirror of the db this weekend gone and that now attaches fine. I'll try the replication again in the next day or so and post back the results.