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.

How to attach MDF without LDF into SQL 2005

SQL 2005 does not support DBCC REBUILD_LOG. Thus, you cannot use it to rebuild the LDF. Here is how I do to attach a database only using MDF without LDF present.

If you are only interested in attaching MDF to the SQL 2005 server without data extraction and replication, please see my part 2, which works without Visual Studio installed.

I use VS.2008 to do the trick. VS.2005 doesn't have this ability. This method probably may work in Visual Web Developer 2008 Express Edition.

Open VS.2008 and do the followings:

1. New a Web site or use the existing one.
2. Add the file to the project's App_Data folder.
3. Right click this folder in the Solution Explorer and select Add Existing Item...
4. Locate the MDF file you want to use.
5. Then click Add.
6. Double-click on the MDF file that you just added; it will open Server Explorer for you.
7. Locate and right-click the MDF file data connection on Server Explorer.
8. Select Publish to provider...
9. Go through the wizard; when the wizard asks you where to publish, I select Script to file. It will generate a SQL file that contains all the database schema and its data.

On the SQL 2005 Server (mine is Express version),

1. Create a database with the same name as your MDF.
2. Run the SQL file you just created by Server Explorer in VS.2008.

Your database should be revived now!