Post reply

.ldf deleted and now cant attach database

  • January 11, 2012 at 5:58 am

    #253148

    Hi All,

    I had deleted my .ldf as it was getting far too large! and then when i went back to use that database i recieve the following error when trying to create a new database with the existing .mdf

    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 'H:\Logs\360MetaVerse_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.

    Msg 1813, Level 16, State 2, Line 1

    Could not open new database '360MetaVerse'. CREATE DATABASE is aborted.

    Thanks in advance

  • January 11, 2012 at 6:08 am

    #1431733

    Restore from backup is probably the best option here.

    The transaction log is not an optional piece of the database and you can't expect to just delete it with no effects.

    p.s. Where did that ldf mentioned come from? An earlier date?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 11, 2012 at 6:23 am

    #1431743

    Hi,

    I found this from googleing my problem and it worked!

    Create database with same name as MDF file you have.

    Stop SQL Server and swap MDF files. Make sure you also keep new database you just created.

    Start SQL Server. Database will be now in suspect state because log file is not correct.

    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

    Thanks anyway

  • January 11, 2012 at 6:30 am

    #1431745

    Really?

    Do you understand what each step of that procedure does?

    If so, would you still recommend it?

    -- Gianluca Sartori

  • January 11, 2012 at 6:34 am

    #1431747

    To be fair, it wasnt critical data and i could have rebuilt manually but would have taken alot longer than using that script.

  • January 11, 2012 at 6:40 am

    #1431749

    What you did there was the absolute, very, final last resort for this kind of problem. You could have transactional inconsistencies, you could have structural inconsistencies.

    Stuff that needs doing:

    Run CheckDB with no_infomsgs and all_errormsgs and hope there are no errors

    Sort out your log maintenance. See - Managing Transaction Logs[/url]

    Also, see the consequences for deleting a log: http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 11, 2012 at 6:55 am

    #1431758

    martin.kerr 34088 (1/11/2012)

    Hi,

    I found this from googleing my problem and it worked!

    Create database with same name as MDF file you have.

    Stop SQL Server and swap MDF files. Make sure you also keep new database you just created.

    Start SQL Server. Database will be now in suspect state because log file is not correct.

    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

    Thanks anyway

    To re attach a database with a missing log file just use either

    sp_attach_single_file_db

    CREATE DATABASE .... FOR ATTACH_REBUILD_LOG

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • January 11, 2012 at 6:57 am

    #1431761

    Perry Whittle (1/11/2012)

    To re attach a database with a missing log file just use either

    sp_attach_single_file_db

    CREATE DATABASE .... FOR ATTACH_REBUILD_LOG

    Which only works if the database was shut down cleanly before the log was deleted, not in all cases.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 11, 2012 at 7:58 am

    #1431807

    Hi Gail

    Just curious about your comment

    "Which only works if the database was shut down cleanly before the log was deleted, not in all cases"

    If I do.

    1) Copy mdf out (suppose I can stop SQL server).

    2) Restart SQL server.

    3) Delete that database

    4) Copy step#1 to normal location.

    4) use GUI to attach the database with remove log file out (it can not find it anyhow)

    (I always use this way to attached learning database, normally it will not come with ldf).

    Will this way work in all cases?

    JJ

    Note: Thanks you for all your topics and comments, I always learn from you.

  • January 11, 2012 at 8:05 am

    #1431810

    jarupan (1/11/2012)

    Will this way work in all cases?

    No. It will only work if the database was cleanly shut down before the log was deleted/removed/not included. Shutting down SQL does not guarantee that the database will have been cleanly shut down (though SQL will try). Detach and offline first are safer, but it's still possible to get a DB that's not cleanly shutdown afterwards (full log is the easiest way to get that)

    If the database was cleanly shut down a simple attach will work. If it wasn't, attach will fail and you'd have to hack the DB in and do an emergency mode repair

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 12, 2012 at 5:02 am

    #1432376

    It's a long shot and it doesn't always work but I've had some success in the past with such a thing.

    1. Make sure the database in question is detached and that the MDF file is in a safe place. "Safe place" means NOT on the drive that you normally store your MDF files, in this case.

    2. Create a new database with the same name as the database in question. Note that the logical and physical file names should match the old ones.

    3. Properly detach the new database.

    4. Copy (not move) the old MDF file from Step 1 over the new MDF file created in Steps 2 and 3.

    5. Try to reattach the new database.

    It doesn't always work but the answer is always "No" unless you "ask".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 12, 2012 at 5:54 am

    #1432390

    GilaMonster (1/11/2012)

    Perry Whittle (1/11/2012)

    To re attach a database with a missing log file just use either

    sp_attach_single_file_db

    CREATE DATABASE .... FOR ATTACH_REBUILD_LOG

    Which only works if the database was shut down cleanly before the log was deleted, not in all cases.

    But it's logically the first place to start, why go to all the effort of hacking the file in if a simple attach may suffice.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • June 18, 2013 at 4:18 am

    #1624939

    Try This:

    ALTER DATABASE EmpDataBase REBUILD LOG ON (NAME=$DatabaseName$,FILENAME=N'$Path\DatabaseName.ldf$')

    GO

    DBCC CHECKDB

    ALTER DATABASE $DatabaseName$ SET MULTI_USER

    GO

  • June 18, 2013 at 6:30 am

    #1624989

    mo7amed.ashraf (6/18/2013)

    Try This:

    ALTER DATABASE EmpDataBase REBUILD LOG ON (NAME=$DatabaseName$,FILENAME=N'$Path\DatabaseName.ldf$')

    GO

    DBCC CHECKDB

    ALTER DATABASE $DatabaseName$ SET MULTI_USER

    GO

    Necro post much? Its over a year old this thread.

  • June 19, 2013 at 5:44 am

    #1625411

    GilaMonster (1/11/2012)

    Perry Whittle (1/11/2012)

    To re attach a database with a missing log file just use either

    sp_attach_single_file_db

    CREATE DATABASE .... FOR ATTACH_REBUILD_LOG

    Which only works if the database was shut down cleanly before the log was deleted, not in all cases.

    .....and afterwards run a CHECKDB to ensure that the database is consistent and that its integrity hasn't been compromised.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply