How to attach MDF without LDF into SQL 2005 - Part 2

In my part 1, I mentioned to use VS.2008 to generate the data/schema script first and then import the data back to the database. Here is another way to attach MDF back to the SQL 2005 sever without LDF. In this approach, you don't need to install Visual Studio but you may need another tool for help depending on your result by running the following SQL statement.

In the following illustration, I will use AdventureWorks.mdf as an example.

Execute the following statement to attach your MDF on SQL Management Studio:

USE master
GO
CREATE DATABASE AdventureWorks
ON PRIMARY (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf')
FOR ATTACH_REBUILD_LOG
GO

If everything works fine, you may get the similar message below and have your database attached after execution:

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf" may be incorrect.
New log file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.LDF' was created.

Otherwise, you may have the following similar error:

File activation failure. The physical file name "C:\Users\<SomeUserName>\Documents\MyCode\Test\App_Data\AdventureWorks_log.ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 1813, Level 16, State 2, Line 3
Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.

Depending on how or where you get your MDF file (because your MDF may not be detached properly), your LDF rebuilt may fail and so may your database attach command. In this case, you need to download and use the free tool SQL Server Express Utility (SSEUtil) to interact with the server and create the LDF file for you. This tool also works well with the full version of SQL Server 2005. There are two (2) big steps you need to perform in order to make your MDF finally available on SQL Management Studio.

Step 1: Use SSEUtil to attach MDF into the database server

First, you can copy the MDF to the location where you usually store for your SQL server if you like, e.g., C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\, or keep wherever it is now.

Then at the command prompt, run sseutil's attach database command. Before doing it, please be sure to add your path to sseutil.exe. Otherwise, you need to use its absolute path name,., e.g., c:\util\sseutil.exe.

sseutil -s .\SQLExpress -a "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf" AdverureWorks

where

• -s to specify the instance of SQL server.
• -a to attach the database by specifying its MDF file path name.
• The last parameter is optional. It is the name of database you want to call (e.g., AdvantureWorks).

There are several options that you can use with sseutil, like -user and -pwd to specify the usr name and its password for connection. Please see the readme file of SSEUtil for details.

or

You can use sseutil console to interact with the server.

• To start a sseutil console, at the command prompt, type
  sseutil -c
  
• You will see
  Console mode. Type 'help' for more information.
  1>
• To attach a MDF, type
  !attach "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf" AdventureWorks
• Type quit when you're done. This step is necessary to enable you to add the database into the sysdabases in the next step by removing the lock on file.

Step 2: Add MDF into sysdatabases

Now you have MDF attached to the server and have LDF recreated. But the database will still remain hidden from SQL Server Management Studio. In order to make it visible in SQL Server Management Studio, you need to go through the database attach wizard to add the database back to sysdatabases; or in the management studio, run either one of the following SQL statements:

• USE master
  GO
  EXEC SP_ATTACH_DB
  @filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf',
  @dbname=N'AdventureWorks
  
  or
  
  
• USE master
  GO
  CREATE DATABASE AdventureWorks
  ON PRIMARY (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf')
  FOR ATTACH
  GO
  

I hope that you find this guide useful.