Have you ever been asked to attach a database and found it doesn’t have a matching log file? This can be initially confusing, but it isn’t very difficult to overcome once you understand the commands. You can create a database on your target server, using the mdf data file in the FILENAME part of the CREATE DATABASE command:
-- Use CREATE DATABASE ... FOR ATTACH or ATTACH_REBUILD_LOG
(FILENAME = N
You can also use:
CREATE DATABASE MyTestDB ON
(FILENAME = N'
This is also explained in the MSDN article for the CREATE DATABASE command. You should note how ATTACH_REBUILD_LOG works:
Specifies that the database is created by attaching an existing set of operating system files. This option is limited to read/write databases. There must be a <filespec> entry specifying the primary file. If one or more transaction log files are missing, the log file is rebuilt. The ATTACH_REBUILD_LOG automatically creates a new, 1 MB log file. This file is placed in the default log-file location.
You should also note how ATTACH works:
If a read/write database has a single log file that is currently unavailable, and if the database was shut down with no users or open transactions before the attach operation, FOR ATTACH automatically rebuilds the log file and updates the primary file. In contrast, for a read-only database, the log cannot be rebuilt because the primary file cannot be updated. Therefore, when you attach a read-only database with a log that is unavailable, you must provide the log files, or the files in the FOR ATTACH clause.