Restoring a SQL Server database Without a Log File

troubleshoot-SQL

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
CREATE DATABASE MyTestDB ON
       (FILENAME = N'D:\Data\MyTestDB.mdf')
       FOR ATTACH
GO

You can also use:

CREATE DATABASE MyTestDB ON
       (FILENAME = N'D:\Data\MyTestDB.mdf') 
       FOR ATTACH_REBUILD_LOG 
GO

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.

Advertisements

1 thought on “Restoring a SQL Server database Without a Log File”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s