Single mdf file attach in sql 2005

Question

Single mdf file attach in sql 2005

imtiazm

Say Hey Kid

Points: 691
More actions
February 4, 2010 at 6:28 am

#144756

Good day all.
I lost my drive due to some hardware fault.
My log files were on the faulty drive.
i do not have access to a backup now.
i have the mdf file though,
The ldf files are gone.
Can i attach in sql 2005 to a single mdf file
The DB was not detached clean,
i tried the
exec sp_attach_single_file_db 'XXX','H:\AAA\xxx.mdf'
Msg 5173, Level 16, State 1, Line 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Log file XXX.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
Msg 5173, Level 16, State 1, Line 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
File activation failure. The physical file name "xxx.ldf" may be incorrect.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'XXX'. CREATE DATABASE is aborted.
i also cant rebuild the log as that feature is not in sql 2005,
Please if someone had this problme , how did you get around, i need to attach my mdf , without ldf .

Viewing 9 posts - 1 through 8 (of 8 total)

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

DHeath SSCertifiable Points: 5200 More actions · Answer 1

Hello,

Do you have any chance of getting backed up information that is going to be the best bet but it sounds like that is a long shot. Have you tried to use the sp_attach_single_file_db procedure...the syntax is below.. hope it helps

EXEC sp_attach_single_file_db @dbname = 'AdventureWorks',

@physname = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\AdventureWorks_Data.mdf';

This might be able to bring you some resoultion

-D-

DHeath

imtiazm Say Hey Kid Points: 691 More actions · Answer 2

Yes i have tried that.

My main posting has the results,

Thank you for your reply anyway.

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 3

If the databases still shows in your databases list, the first thing to do is drop it. To do this, take it offline and then detach.

After that attach the mdf through ssms but remove the entry for the log. SQL should then creat a new log for you. Code below:

USE [master]

GO

CREATE DATABASE [DATABASE] ON

( FILENAME = N'PATHE TO MDF FILE')

FOR ATTACH

GO

Adam Zacks-------------------------------------------Be Nice, Or Leave

imtiazm Say Hey Kid Points: 691 More actions · Answer 4

This is the reply i get

USE [master]

GO

CREATE DATABASE DBname ON

( FILENAME = N'H:\XXX.mdf')

FOR ATTACH

Result

File activation failure. The physical file name "f:\XXX.ldf" may be incorrect.

The log cannot be rebuilt because the database was not cleanly shut down

The f:\ drive refers to the drive that was lost.

When trying to attach the mdf , it reads the location of the f drive that is no longer there.

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 5

Was the database still shwon in the databases list?

Have you tried attaching via th ssms gui (removing the entry for the log)?

Did you google the output?

Adam Zacks-------------------------------------------Be Nice, Or Leave

Lowell SSC Guru Points: 323518 More actions · Answer 6

imtiazm (2/4/2010)
This is the reply i get
...
File activation failure. The physical file name "f:\XXX.ldf" may be incorrect.
File activation failure. The physical file name "f:\XXX.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down
....

ouch, it looks like you cannot attach the mdf because of that issue....the database was not cleanly shut down. I think that's why you are having the errors.

I'm sure you already looked at your backups, how recent are they?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 7

This seems to address your issue (i found it with a quick google).

Try what it suggests (make a copy of you mdf first) and get back to us.

sorry forgot link: http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/189be01f-23de-48b0-96cc-8f1292c13c54

Adam Zacks-------------------------------------------Be Nice, Or Leave

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

Have a look at this blog post. Step by step for getting a DB back when the log's deleted. The process for when it's detached starts just past half way through.

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