Attaching an MDF file without an LDF file can be a little tricky. I had to go through this recently when I downloaded the AdventureWorks2008R2 database, which was just an MDF file. I know I’ve done this before, but I had to stop and look things up, which means that I should blog about it (hint, hint).

How do I attach a new database?

If I go into SSMS, I can pick the “Attach” dialog by right clicking the “databases” folder.

Once I pick my MDF, I see the dialog populated.

However if I click “OK”, I get an error.

<mini-rant>

Don’t click OK for precisely this reason. If there’s an error, it can be hard to figure out what happened, and the error handling in SSMS isn’t the greatest. I’ve even seen people click “Cancel” without realizing there was an error”.

If you use the GUI, please click the “script” button instead and cancel out of the dialog.

</mini-rant>

The error occurred because the log file didn’t exist. If you select the log file in the lower dialog, and click Remove, you can use the GUI here.

However removing the log file and pressing the click button gives me this code:

USE [master]
GO
CREATE DATABASE [AdventureWorks2008R2] ON 
( FILENAME = N'D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Data.mdf' )
 FOR ATTACH
GO

.csharpcode, .csharpcode pre

{

font-size: small;

color: black;

font-family: consolas, “Courier New”, courier, monospace;

background-color: #ffffff;

/*white-space: pre;*/

}

.csharpcode pre { margin: 0em; }

.csharpcode .rem { color: #008000; }

.csharpcode .kwrd { color: #0000ff; }

.csharpcode .str { color: #006080; }

.csharpcode .op { color: #0000c0; }

.csharpcode .preproc { color: #cc6633; }

.csharpcode .asp { background-color: #ffff00; }

.csharpcode .html { color: #800000; }

.csharpcode .attr { color: #ff0000; }

.csharpcode .alt

{

background-color: #f4f4f4;

width: 100%;

margin: 0em;

}

.csharpcode .lnum { color: #606060; }No mention of a log file. If I run this I’ll get an error in the messages pane, but the database will attach:

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.DENALIRCO\MSSQL\DATA\AdventureWorks2008R2_log.ldf" may be incorrect.

New log file ‘D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_2_log.ldf’ was created.

Converting database ‘AdventureWorks2008R2_2′ from version 705 to the current version 706.

Database ‘AdventureWorks2008R2_2′ running the upgrade step from version 705 to version 706.

That works well, but what about the rebuild log option?

If you look in the CREATE DATABASE topic, you’ll find the option ATTACH_REBUILD_LOG. What if I use this?

create database AdventureWorks2008R2_2
 on ( filename = 'D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Data2.mdf')
  for attach_rebuild_log

.csharpcode, .csharpcode pre

{

font-size: small;

color: black;

font-family: consolas, “Courier New”, courier, monospace;

background-color: #ffffff;

/*white-space: pre;*/

}

.csharpcode pre { margin: 0em; }

.csharpcode .rem { color: #008000; }

.csharpcode .kwrd { color: #0000ff; }

.csharpcode .str { color: #006080; }

.csharpcode .op { color: #0000c0; }

.csharpcode .preproc { color: #cc6633; }

.csharpcode .asp { background-color: #ffff00; }

.csharpcode .html { color: #800000; }

.csharpcode .attr { color: #ff0000; }

.csharpcode .alt

{

background-color: #f4f4f4;

width: 100%;

margin: 0em;

}

.csharpcode .lnum { color: #606060; }If I run this, I get essentially the same messages:

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.DENALIRCO\MSSQL\DATA\AdventureWorks2008R2_log.ldf" may be incorrect.

New log file ‘D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_2_log.ldf’ was created.

Converting database ‘AdventureWorks2008R2_2′ from version 705 to the current version 706.

Database ‘AdventureWorks2008R2_2′ running the upgrade step from version 705 to version 706.

I’m not sure why this option exists, or if it’s been deprecated in 2012 since the functionality appears to exist in FOR ATTACH. The documentation mentions that if the log file is not there, it will rebuild. I guess this is included in case you wish to force a rebuild, which is a good thing.

In any case, if you run into issues attaching a database with the GUI, this should help you.

Filed under: Blog Tagged: administration, sql server, syndicated