Attaching SQL Server database without log file

I needed to attach SQL Server 2008 database to server. There was no log file, just mdf. I don’t know why but it is not very easy to get this database online. After some digging in internet I found solution.

I suggest you to read and try out example by Paul S. Randal TechEd Demo: Creating, detaching, re-attaching, and fixing a suspect database. I also suggest you to bookmark this posting, you never know…. I found simplest solution from stackoverflow: How to recover database from MDF in SQL Server 2005? It works also for SQL Server 2008.

  1. Create database with same name as MDF file you have.
  2. Stop SQL Server and swap MDF files. Make sure you also keep new database you just created.
  3. Start SQL Server. Database will be now in suspect state because log file is not correct.
  4. Run the following script:
    USE [master]
    GO
    ALTER DATABASE [MyDatabase] SET EMERGENCY
    GO
    ALTER DATABASE [MyDatabase] SET SINGLE_USER
    GO
    DBCC CHECKDB ([MyDatabase], REPAIR_ALLOW_DATA_LOSS)
    GO
    ALTER DATABASE [MyDatabase] SET MULTI_USER
    GO
    ALTER DATABASE [MyDatabase] SET ONLINE
    GO

When I ran this script I got the following output.

Msg 5173, Level 16, State 1, Line 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.
Log file ‘c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS2008\MSSQL\DATA\MyDatabase_log.ldf’ does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.
Warning: The log for database ‘MyDatabase’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
DBCC results for ‘MyDatabase’.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
… Long list of GBCC messages …

CHECKDB found 0 allocation errors and 0 consistency errors in database ‘MyDatabase’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

You can see the error message in the output but that was no problem after all. My database is now online and works as expected.

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    11 thoughts on “Attaching SQL Server database without log file

    • December 17, 2009 at 3:56 pm
      Permalink

      I tried it your way , however it doesnt work , Im using SQL 2008 ^_^

    • December 17, 2009 at 5:38 pm
      Permalink

      Are you sure you didn’t miss some important steps? Can you describe what you did and at what point you faced the problems?

    • July 7, 2011 at 6:30 am
      Permalink

      This worked for me. Thank you.
      My server crashed and the _log file was corrupted. I followed these exact instructions and my 1.6 GB _mdf file data is back working again. /phew

    • July 28, 2011 at 2:32 am
      Permalink

      hi it is not working for me also. Could you please help me. it is a bit important for me.

      i have done everything that you have mentioned. when i run your script i even got the same error message. After that, when i clicked on the database i am getting this error.

      the database is not accessible.

    • August 29, 2011 at 7:12 am
      Permalink

      Thanks Sir, This work with me.

    • December 14, 2011 at 4:31 pm
      Permalink

      This was a lifesaver. Thanks for posting!

    • December 24, 2011 at 11:24 am
      Permalink

      Thanx for the post!
      In my case, I had to rearrange steps a bit to make it work (my DB has neverending “in recovery” state):

      1.Create database with same name as MDF file you have.

      USE [master]
      GO
      ALTER DATABASE [MyDatabase] SET EMERGENCY
      GO
      ALTER DATABASE [MyDatabase] SET SINGLE_USER
      GO

      2.Stop SQL Server and swap MDF files. Make sure you also keep new database you just created.
      3.Start SQL Server. Database will be now in suspect state because log file is not correct.
      4.Run the following script:

      USE [master]

      DBCC CHECKDB ([MyDatabase], REPAIR_ALLOW_DATA_LOSS)
      GO
      ALTER DATABASE [MyDatabase] SET MULTI_USER
      GO
      ALTER DATABASE [MyDatabase] SET ONLINE
      GO

      and it works!

    • March 8, 2012 at 12:27 pm
      Permalink

      Thank you so much! You saved me!

    • April 18, 2012 at 8:53 am
      Permalink

      You can attach a data file without the log file as follows. Right-click at the database level and select “Attach…”. In the “Attach Databases” window, click “Add” and select the MDF file. In the “Database details” box click on the log file and click “Remove”. You can then click “OK” and a new log file will be created.

    • July 26, 2012 at 7:29 am
      Permalink

      after swapping MDF file, you must delete LDF file and the start sql server.

    • October 15, 2020 at 12:04 pm
      Permalink

      Thanks! It worked for me (SQL Server 2008)

    Leave a Reply

    Your email address will not be published. Required fields are marked *