Sunday, April 12, 2009

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!

5 comments:

  1. This article has very useful information about sql recovery.Once my sql database has been corrupted that time i have used third party sql recovery software named as Stellar Phoenix SQL Recovery Software.Its great software to repair and recover your sql database.

    ReplyDelete
  2. I am very grateful to you. Solved my issue. Thanks a lot

    ReplyDelete
  3. I read your post regarding How to attach MDF without LDF into SQL 2005. It's really very informatics and helpful post. I really appreciate for that.

    MDF | MDF Boards

    ReplyDelete
  4. SQL Data Recovery tool can repair SQL database and restore SQL database objects from corrupt MDF file.

    ReplyDelete