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?
Best Answer
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.