Attaching a .MDF without a .LDF

A question that seems to come up often is how to attach a data file (.MDF) without a transaction log?  If a database has been detached from SQL Server cleanly, then there should be no information inside the transaction log of any use.  If the database was not detached cleanly (for example, if the machine crashed, and SQL Server has not been restarted, or if a copy of the file is made when SQL Server is still running) then the database may be in an inconsistent state, requiring the information currently contained inside the transaction log file.

Assuming that the database was shut down cleanly, the transaction log file can be recreated.  There are two methods: sp_attach_single_file_db and CREATE DATABASE.

The sp_attach_single_file_db is a shorthand way of calling CREATE DATABASE FOR ATTACH.  You can see exactly what sp_attach_single_file_db does by running “sp_helptext sp_attach_single_file_db” in Management Studio.  The main restriction for FOR ATTACH when used in this fashion is that only one transaction log can be created.  The new transaction log will be created in a default location, or if the database is read only, in the original location specified in the .MDF file.  The basic syntax is:

EXEC sp_attach_single_file_db @dbname='MyDatabase', 
    @physname='E:\Database\MyDatabase.mdf'
 

If you need two transaction log files recreated, you need to use CREATE DATABASE FOR ATTACH_REBUILD_LOG.

CREATE DATABASE MyDatabase ON 
    (FILENAME = 'E:\Database\MyDatabase.mdf') 
FOR ATTACH_REBUILD_LOG

If possible, I recommend taking backups and then restoring the backup, rather than try to attach a .MDF file.  It’s a safer and more reliable method of moving a database from server to server.