Can I attach a database with only the mdf file?

Question

Can I attach a database with only the mdf file?

howard-296342

SSC Rookie

Points: 32
More actions
January 30, 2006 at 8:35 am

#170744

Hi,
our forum server suffered a catastrophic failure, which included the RAID failing too.
I only have the mdf file, no ldf file. I did not unmount the db, or do a single file unmount. I've tried a few things to reattach it, but no joy.
Any ideas would be appreciated.
Thanks

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

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

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Howard,

Yes, you can attach a database with .mdf file but you will loose all the transactions, which have not been committed to the database.

SQL Server will create a new .ldf file when you attach a database with a single file (.mdf).

Syntax for attaching the single file:

sp_attach_single_file_db [ @dbname = ] 'dbname'

, [ @physname = ] 'physical_name'

Thanks and have a nice day!!!

Lucky

howard-296342 SSC Rookie Points: 32 More actions · Answer 2

I tried that already with:

EXEC sp_attach_single_file_db @dbname = 'koiforum',

@physname = 'C:\Program Files\Microsoft SQL Server\MSSQL$LISTSERVER\data\koiforum_data.mdf'

I get this error:

Server: Msg 1813, Level 16, State 2, Line 1

Could not open new database 'koiforum'. CREATE DATABASE is aborted.

Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$LISTSERVER\data\KoiForum_Log.LDF' may be incorrect.

Any ideas?

Thanks

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 3

Hello Howard,

Are you sure that you have the .mdf file. B'cos in the syntax you are mentioning the .mdf file but the error is pointing that .ldf file is incorrect.

Please go through this article

http://www.sqlservercentral.com/columnists/ckempster/deattachandreattachdatabases.asp

Thanks and have a nice day!!!

Lucky

mattyk6 Ten Centuries Points: 1152 More actions · Answer 4

This is the syntax that I use to attach a DB with only a MDF file:

Exec sp_attach_single_file_db DB_Name, 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DB_Name_Data.MDF'

Just change the highlighted portions.

Matt

Chrislis SSChasing Mays Points: 622 More actions · Answer 5

If your database was not detached using sp_detach_db or your server was not shutted down cleanly you CAN'T use sp_atach_single_file_db.

Still, there is a workarround on this. I did use once the "dbcc rebuild_log" command to rebuild the ldf file, but my database become inconsistent. But I managed to copy most of my important data.

If your data was so important you should had a backup plan for it.