Recovering database from just the MDF(s)

[pblazek]

SQL Server 2000 SP4, fairly large (30GB) database. We lost power to the server and the RAID controller refused to come back up. Our backup tape is unreadable. Using a data recovery/RAID rebuilding service, I have recovered the 2 MDFs and the LDF but I get an error processing the log when I try to attach them. The database was not in a detached state when we lost the server. I would like to know some way to get around the log error. This database has had no update activity for months but is important from a historical standpoint.

 

[mrdenny]

If you have the MDFs and they are intact you can attach the database without the log file. Go back into the attach database screen and change the path for the log file to an invalid path. The database will attach and create a new log file.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[pblazek]

When I try to attach the database and select the main MDF (West.mdf) three entries appear: E:\West.mdf, E:\West_1.mdf, and E:\West_log.ldf. I've tried various combinations with the third entry (E:\,E:\test.ldf, etc) and the first dialog box says the filename specified for the log file is incorrect, a new log file may be created. I click Yes to continue and the next dialog box says The physical name 'D:\MSSQL\data\West_log.ldf' may be incorrect; this indicates to me that the server is trying to use the path in the MDF to try to find the LDF instead of creating it. What am I missing?

 

[mrdenny]

I beleive that when it creates the new LDF it tries to use the origional file path to create it. Make sure that the folder D:\MSSQL\Data\ exists and that there is no file called West_log.ldf in that folder.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[pblazek]

I've created the necessary folders(empty) in the path specified, but still get the same error. The entire error message is:

Error 1813: Could not open new database 'West'. CREATE DATABASE is aborted. Device activation error. The physical file name 'D:\MSSQL\Data\West_log.ldf' may be incorrect.

Might there be corruption in the MDF(s) which does not allow the server to create a new LDF?

 

[mrdenny]

It's possible but it would probably throw a different error.

Try using the sp_attach_single_file_db procedure directly to attach the database instead of using the UI.

exec sp_attach_single_file_db @dbname='West', @physname='E:\west.mdf'

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[pblazek]

I can't use sp_attach_single_file_db because there are two MDFs. I've also tried CREATE DATABASE ... FOR ATTACH, since that's what is produced by sp_attach_db. That doesn't work either - I get the same error.

 

[JayKusch]

I do not believe you can physically have 2 MDFs of the same name.

Are you certain one is not an NDF? (secondary DB file)



Thanks

J. Kusch

 

[pblazek]

The MDFs have different names (listed in an earlier post). They are both indeed MDFs. One is about 200 MB, the other about 18GB.

 

[mrdenny]

I would assume that one is the mdf and the second is actually the ndf, it was simply given a .mdf file extention.

Can you post the actual full error message?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[pblazek]

The entire error message is:

Error 1813: Could not open new database 'West'. CREATE DATABASE is aborted. Device activation error. The physical file name 'D:\MSSQL\Data\West_log.ldf' may be incorrect.

if I leave out the LDF, even though the folder D:\MSSQL\Data exists. If I just try to attach the database using all three files the error message is:

Error 9004: An error occurred while processing the log for database 'West'.

 

[mrdenny]

Have you tried this syntax?

CREATE DATABASE West
ON (Name West,
    FileName='E:\West.mdf'),
(Name West_1, FileName='E:\West_1.mdf'),
LOG ON (Name West_Log, FileNAme='E:\Web_New_Log.ldf')
WITH ATTACH_REBUILD_LOG

If that doesn't work another hack you can try is to create a new database with the same number of files which are the same size as the hosed database. After you create the database (the sizes need to be perfect if this has any hope of working) stop the SQL Service. Then move the files from the database you just created out of the way, and drop in the files from the busted database. Start SQL. The database will probably come up in Suspect mode. You should then be able to get it into emergency mode using the ALTER DATABASE Command and either DBCC CHECKDB it and repair the problem, or export the data to another database.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog