none
Recovering DB with only the .MDF file

    Question

  • I saw the other thread that is related, but let me give you our circumstances.  A drive failed and that drive had the SQL installation and the *.ldf files on it.  The MDF files were unscathed.

    The drive has been rebuilt and I have reinstalled SQL 2000 SP3A on there (that is what was there before).  I have tried attaching the MDF through the GUI and letting it create the log for me.  This failed.

    I tried sp_attach_single_file_db, but this failed since the db was never dettached.

    I tried creating the db again, dettaching it, copying the old (good) MDF file over the newly created one and then attaching (using multiple attach approaches).  It knows that the log file and mdf are not matched and won't let me do the attach.

    I also found DBCC REBUILD_LOG, but the article in SQL Mag supposes that your instance is still in working order and that there is an entry for the db in sysdatabases.  Such is not true in my case.

    Can you help?  We are trying to get old backup tapes shipped to the site, looking up our Gold Partner contact info, etc., but in the meantime, I am trying to get the data restored even if we lose some transactions.

    Thanks for any help!

    Thursday, August 17, 2006 8:08 PM

Answers

  • Here is what worked for me.  Thanks for SQL Server Mag for the core pieces of this:

     

    -- LISTING 1: Undocumented DBCC Command REBUILD_LOG

    /* Create a new db named the same as the inaccessible one (in this case "Ops") */

    EXEC sp_configure 'allow updates', 1
    RECONFIGURE WITH OVERRIDE
    GO

    BEGIN TRAN

    UPDATE master..sysdatabases
    SET status = status | 32768
    WHERE name = 'Ops'

    IF @@ROWCOUNT = 1
    BEGIN
       COMMIT TRAN
       RAISERROR('emergency mode set', 0, 1)
    END
    ELSE
    BEGIN
       ROLLBACK
       RAISERROR('unable to set emergency mode', 16, 1)
    END

    GO

    EXEC sp_configure 'allow updates', 0
    RECONFIGURE WITH OVERRIDE
    GO

    -- 3. Stop SQL Server
    -- 4. Replace the newly created Ops.mdf file with the old good one
    -- 5. Rename the newly created Ops_log.ldf file
    -- 6. Start SQL Server

    -- 7. Run the following DBCC command:
    DBCC REBUILD_LOG('Ops','E:\Program Files\Microsoft SQL Server\MSSQL\Data\Ops_log.LDF')

    -- You should get the message:
    -- Warning: The log for database 'Ops' has been rebuilt. Transactional consistency has been lost.

    -- DBCC CHECKDB should be run to validate physical consistency. Database options will have to be reset,

    -- and extra log files may need to be deleted.
    -- DBCC execution completed. If DBCC printed error messages, contact your system administrator.


    -- 8. Run data consistancy check
    /*Perform physical and logical integrity checks at this point.
    Bcp data out if your integrity checks demonstrate that problems exist.
    */
    DBCC CHECKDB (Ops)


    ALTER DATABASE Ops SET MULTI_USER
    GO

    -- Set database options and recovery model as desired.
    GO

    -- Make a backup and start to use the DB again

    Thursday, August 17, 2006 10:43 PM

All replies

  • Here is what worked for me.  Thanks for SQL Server Mag for the core pieces of this:

     

    -- LISTING 1: Undocumented DBCC Command REBUILD_LOG

    /* Create a new db named the same as the inaccessible one (in this case "Ops") */

    EXEC sp_configure 'allow updates', 1
    RECONFIGURE WITH OVERRIDE
    GO

    BEGIN TRAN

    UPDATE master..sysdatabases
    SET status = status | 32768
    WHERE name = 'Ops'

    IF @@ROWCOUNT = 1
    BEGIN
       COMMIT TRAN
       RAISERROR('emergency mode set', 0, 1)
    END
    ELSE
    BEGIN
       ROLLBACK
       RAISERROR('unable to set emergency mode', 16, 1)
    END

    GO

    EXEC sp_configure 'allow updates', 0
    RECONFIGURE WITH OVERRIDE
    GO

    -- 3. Stop SQL Server
    -- 4. Replace the newly created Ops.mdf file with the old good one
    -- 5. Rename the newly created Ops_log.ldf file
    -- 6. Start SQL Server

    -- 7. Run the following DBCC command:
    DBCC REBUILD_LOG('Ops','E:\Program Files\Microsoft SQL Server\MSSQL\Data\Ops_log.LDF')

    -- You should get the message:
    -- Warning: The log for database 'Ops' has been rebuilt. Transactional consistency has been lost.

    -- DBCC CHECKDB should be run to validate physical consistency. Database options will have to be reset,

    -- and extra log files may need to be deleted.
    -- DBCC execution completed. If DBCC printed error messages, contact your system administrator.


    -- 8. Run data consistancy check
    /*Perform physical and logical integrity checks at this point.
    Bcp data out if your integrity checks demonstrate that problems exist.
    */
    DBCC CHECKDB (Ops)


    ALTER DATABASE Ops SET MULTI_USER
    GO

    -- Set database options and recovery model as desired.
    GO

    -- Make a backup and start to use the DB again

    Thursday, August 17, 2006 10:43 PM
  • Note that in SQL Server 2005, all of this can be accomplished using the

    CREATE DATABASE FOR ATTACH_REBUILD_LOG  command.

    All of the same caveats apply: if you attach a database without a log, you stand a good chance of losing data and/or ending up with a corrupt database.  If you've got no choice, you've got no choice.  That's what backups are for.

    Friday, August 18, 2006 6:22 PM
    Moderator
  • dear Mr.BusmasterJones 

    I know it was a long time since you posted this but can you help me with the same problem but in SQL SERVER 2005

    I had a lot of error messages when i TRIED the way you described...
    there was an error when I tried the first part and error message was about adding ad hoc is not allowed
    then calling the method was rejected too.

    Please help me!!!!
    THANKS IN ADVANCE
    Tuesday, August 7, 2007 10:59 PM