1

I have a .MDF file and no .LDF files for a database created in SQL Server 2005. When I try to attach the .MDF file to a different SQL Server, I get the following error message.

I would like to accomplish any one of the following options:

Attach the database without data loss (unlikely but would save me some time). Attach the database with data loss (whatever transactions were open are lost). Recover the schema only (no data) from the .MDF file.

What SQL commands can I try to get my database going again?

Improve this question
1
  • 1
    what error message?!?!? I don't see anything .....
    – marc_s
    Apr 14, 2015 at 9:57

3 Answers 3

Reset to default
4

You can use sp_attach_single_file_db or the newer version CREATE DATABASE database_name FOR ATTACH_REBUILD_LOG. In both cases it will create a new log file for you.

Unfortunately because you have no log you will not be able to recover anything that has not been committed. It is also a good idea to run a DBCC CHECKDB afterwards to make sure there is no other damage.

Improve this answer
2
  • 1
    Slight correction: you will not be able to recover anything that has not been committed and checkpointed to the data file. There's no guarantee that you have a physically or logically consistent database if you don't have the transaction log. It's probably better than nothing, though.
    – db2
    Apr 13, 2015 at 20:27
  • Excellent point. And a backup would be a much better solution, but you do what you can.
    – Kenneth Fisher
    Apr 13, 2015 at 20:37
0

Here are details that cover parts 2) and 3) in case re-creating log doesn’t work which can happen if MDF file is corrupted.

You can recover data and structure only by reading MDF file with some third party tool that can de-code what’s written as binary data but even with such tools you can’t always do the job completely.

In such cases you can try SQL Server Restore Toolbox. From what I know this is the only tool that can do this kind of job but it’s quite expensive.

http://www.sqlserver.restoretools.com/

Much better idea is to try to recover these from any old backups if you have any.

Improve this answer
0

As you have said, have .mdf file only. You can still attach your database without ldf file. You just need to run following commands:

Method 1:

EXEC sp_attach_single_file_db @dbname='Database_Name',
@physname=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Database_Name.mdf'
GO

As suggested above.

Method 2:

CREATE DATABASE Database_Name ON
(FILENAME =N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Database_Name.mdf')
FOR ATTACH_REBUILD_LOG
GO

It will create log file for you.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.