Unable to attach MDF database files without the accompanying LDF files

Question

Post reply

Unable to attach MDF database files without the accompanying LDF files

tedsmith28

SSC Enthusiast

Points: 128
More actions
July 14, 2010 at 3:23 am

#237678

Hi
I'm not an SQL person as a rule. I have been passed a copy of a hard disk for analysis of the data on it. A couple of the files are large mdf files - one is 50Gb and one is 25Gb.
I have installed MS Server 2005 Enterprise Edition and it's SP3 and created an instance called SQLEXPRESS. I have MS Server Management Studio installed too. It loads OK and I can login OK.
I try to 'attach' these database files by right clicking in the database area, selecting attach, and I navigate to my mdf files. They are added to the dialogue box, but when I click the final Add button I get an error :
I've Googled the error and the MS website suggests installing the latest MS SQL Server service pack, which I have already done. Other threads suggesting restarting the SQL services, which I have also done. Other threads (like this one: http://forums.asp.net/t/1175850.aspx) suggest removing the LDF file from the 'Attach Database' sequence. I have tried that too, but the error still comes up.
I note that the errors suggests a disliking to the fact that the LDF files are missing when they are listed as part of the Add sequence. When they are not listed as part of the sequence, the error is similar but it says that the database was not shut down properly etc and cannot be attached. I've searched the disk drive for the particular LDF files named but cannot locate them. There are several LDF files, in the same dir as where the MDF files were located, but they have different names to those being requested by MS SQL Server. I have tried manually entering these file names but the errors persist. It keeps asking for 'sql_databases2_Log.LDF', specifically.
So my question is - is it possible to restore these database files without the accompanying LDF files? If so, how? What else might I be doing wrong?
Many thanks
Ted

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Denesh Naidoo SSCrazy Points: 2348 More actions · Answer 1

Hi,

Please try the following:

1. Create a new blank database on the server you want to attach your MDF

2. Detach database from SSMS

3. Delete database files for database that you just created and detached

4. Take MDF (the on you want to attach) and copy into folder where you just deleted files for above

Run query below

EXEC sp_attach_single_file_db @dbname = 'TestDB', --this is your database

@physname = 'D:\TestDB.mdf' --this path should be to the folder you copied your MDF into for point 4 above

tedsmith28 SSC Enthusiast Points: 128 More actions · Answer 2

Hi

Thanks for your reply, which I have tried. Having clicked 'Execute' there was a fairly long pause while it tried to do it's thing, but then I was met with a similar error :

File activation failure. The physical file name "F:\MSSQL\data\sql_database_Log.LDF" may be incorrect. The log cannot be rebuilt because the database was not cleanly shut down. Msg 1813, Level 16, State 2, Line 1....

In other words, the MDF file seems inheritnatly dependant on the missing LDF file was presumebly supposed to be in the MSSQL\Data dir, but I can't see it on the disk!

Denesh Naidoo SSCrazy Points: 2348 More actions · Answer 3

Maybe have a look at the following link:

http://social.msdn.microsoft.com/forums/en-US/sqldisasterrecovery/thread/189be01f-23de-48b0-96cc-8f1292c13c54/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Follow the steps here:

http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ashwani Malik Ten Centuries Points: 1140 More actions · Answer 5

Looks like your Log file is not at the same location where your Data file is. By default it looks for log file location where your data file is, though you can change the path to locate for log file. Here is another way to rebuild the log file if you do not have one

CREATE DATABASE Sql_database2

ON

(FILENAME = 'F:\MSSQL\\data\Sql_database2_data.mdf')

for ATTACH_REBUILD_LOG

GO

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Ashwani Malik (7/14/2010)
Here is another way to rebuild the log file if you do not have one
CREATE DATABASE Sql_database2
ON
(FILENAME = 'F:\MSSQL\\data\Sql_database2_data.mdf')
for ATTACH_REBUILD_LOG
GO

That will only work if the database was cleanly shut down. The error message the the OP posted earlier clearly states that the database was not cleanly shut down. That CREATE DATABASE statement will fail.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass