Dowemo


Question:

I have an MDF file and no LDF files for a database created in MS SQL Server 2005. When I try to attach the MDF file to a different SQL Server, I get the following error message.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

I would like to accomplish any one of the following options:

  • Attach the database without data loss (unlikely but would save me some time).
  • Attach the database with data loss (whatever transactions were open are lost).
  • Recover the schema only (no data) from the MDF file.
  • What SQL commands can I try to get my database going again?


    Best Answer:


    FROM a post at SQL Server Forums Attaching MDF without LDF:

    If you want to attach a MDF without LDF you can follow the steps below It is tested and working fine

  • Create a new database with the same name and same MDF and LDF files

  • Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.

  • Start SQL Server

  • Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

  • Sp_configure "allow updates", 1
    
    
    go
    
    
    Reconfigure with override
    
    
    GO
    
    
    Update sysdatabases set status = 32768 where name = "BadDbName"
    
    
    go
    
    
    Sp_configure "allow updates", 0
    
    
    go
    
    
    Reconfigure with override
    
    
    GO
    
    
    
  • Restart sql server. now the database will be in emergency mode

  • Now execute the undocumented DBCC to create a log file

  • DBCC REBUILD_LOG(dbname,'c:dbname.ldf') -- Undocumented step to create a new log file.

    (replace the dbname and log file name based on ur requirement)

  • Execute sp_resetstatus

  • Restart SQL server and see the database is online.

  • UPDATE: DBCC REBUILD_LOG does not existing SQL2005 and above. This should work:

    USE [master]
    
    
    GO
    
    
    CREATE DATABASE [Test] ON 
    
    
        (FILENAME = N'C:MSSQLDataTest.mdf')
    
    
        FOR ATTACH_REBUILD_LOG
    
    
    GO
    
    
    



    Copyright © 2011 Dowemo All rights reserved.    Creative Commons   AboutUs