Attaching a database without log file

To attach a database without a log file is to use a simple T-SQL as below:

I am taking an example of AdventureWorks 2008R2 database to attach without a log file; T-Sql as below:

CREATE DATABASE AdventureWorks
ON
(FILENAME ='C:\Database\Data\AdventureWorks2008R2_Data.mdf')
FOR ATTACH_REBUILD_LOG ;

you will need to specify the filename of where the .mdf file is. and be sure to include ‘FOR ATTACH_REBUILD_LOG’ option to rebuild the log file.

Below is the output you should expect to see. The log file will be created in you default log location C:\Database\Log in my case.

File activation failure. The physical file name
"C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\
DATA\AdventureWorks2008R2_Log.ldf" may be incorrect.
New log file 'C:\Database\Log\AdventureWorks_log.LDF' was created.

Now if you see the database properties you should be able to see the log file along with the data file. ( Run sp_helpdb Adventureworks and see the file paths)

This entry was posted in Attach database without log file, SQL Server and tagged , . Bookmark the permalink.

Leave a Reply