How to attach SQL 2000 database (MDF) without a LDF file.

Posted: May 4, 2007 in SQLServer2000

Preface:

When you are working with Microsoft SQL Server 2000, you may find it a very cool DBMS as my opinion. But some times, it drives you mad because of some troubles that are hard to find documents or solutions for them. This post reveals one of the most noticeable problem that I have to dealt wit.

Problem:

  • How to attach the MDF file on the query analyzer
  • How to bring database back on operation when the LDF file is damaged

Solution:

If you need to ignore the current LDF file and if your database is not detached properly and if you do not have the backup, follow the below steps to bring up your database. In the below step-7 is a undocumented DBCC command.

  1. Create a new database with the same name and same MDF and LDF files
  2. 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.
  3. Start SQL Server
  4. Now your database will be marked suspect
  5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start upSp_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
  6. Restart sql server. Now the database will be in Emergency mode
  7. Now execute the undocumented DBCC to create a log fileDBCC 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)

  8. Execute sp_resetstatus <dbname>
  9. Restart SQL server and see the database is online.

site statistics
Comments
  1. Steve says:

    Steve

    In few years we will see a result

  2. oldlight says:

    The easiest way to solve such the problem is to make sure you always have a full-backup of your database. Otherwise, you’ll have to go through the nightmare of re-take your precious data…
    ^^

  3. Prabodh Joshi says:

    Dear Oldlight
    Thank you for such a great post. You save me lot of time and embarrassment today.

    thanks

  4. Saraboji says:

    Mr. Old Night,

    You can’t keep every minute and every second full backup. i took the full backup before 1 hour crashing my database. now you tell me what the solution to retrive the one hour data. my database size is 980 GB. Do you know how many hours it will take for full backup.

    thanks,
    JSaraboji.

  5. oldlight says:

    So you know it, right? Mr. “Sara boy”
    ;))

  6. CyberTerror says:

    Thnk you man

  7. Mike Gledhill says:

    A very interesting article, but I found it’s just as easy to get around the missing-Log-file using this:

    In SQL Server Management Studio:
    – right-click on Databases and “Attach”
    – select the .mdf file for your database.

    In the bottom window, it will automatically show details of this selected file, plus the Log file it’s expecting to be associated with it.
    – click on the Log file, then click on Remove.
    – now click on OK.

    SQL Server will happily attach to your database, but create it’s own log file.

  8. Damerz says:

    Oldlight,

    EXCELLENT posting, appreciate your sharing of knowledge. I used this procedure successfully today.

    Thank you very much.

  9. GRUMPY OLD DBA says:

    Sorry, but Caveat emptor🙂.

    In step 5 sysdatabases.status is being updated without having first selected (and keeping) the original sysdatabases.status. A DBA needs to know the original sysdatabases.status, so that database options can be reverted back within a new step (at about 8.5).

    Step 7 is a “point of no return”. A REBUILD_LOG also resets the database file’s (the .mdf’s) Log Sequence Number (LSN). Even if you were to subsequently rediscover the old tlog file, it can no longer be “put back” after a REBUILD_LOG. Thus, perhaps a copy of the mdf should first be made, at step 0.

    Step 7 fails to mention DBCC REBUILD_Log returns an undocumented prompt to run DBCC CHECKDB. A failure to run CHECKDB at about step 8.25 (whether sp_resetstatus works, or not) is not an “option” IMO. Sure, a DBA can get lucky, in that what the DBA had whacked (out of the tlog via REBUILD_LOG) did not need to be recovered (and thus CHECKDB is error free). But would I gamble my job? I think not🙂.

    When the gamble fails, CHECKDB can report the required repair level is REPAIR_ALLOW_DATA_LOSS. While waiting for CHECKDB REPAIR_ALLOW_DATA_LOSS to whack out no-longer-readable data that may still exist within a “too” old backup (in addition to the “new” transactions which a REBUILD_LOG sent to the big bit bucket in the sky), perhaps that DBA should take the time to polish up their resume …🙂. To put this another way: I know of more than one DBA who has been fired for following the above. On the other hand, a DBA can follow the above, screw their employer (by allowing data loss), and effectively cover up their tracks (thus retain their job), but the next DBA (seconds to years later) may need to face the music (without knowing why).

    If CHECKDB reports REPAIR_ALLOW_DATA_LOSS, I _also_ suggest CHECKCONSTRAINTS be run at about step 8.375. A CHECKCONSTRAINTS can detect orphans caused by REBUILD_LOG if referential integrity is enforced. A CHECKCONSTRAINTS will be unable to detect logical inconstancies caused by REBUILD_LOG when a database design does not enforce referential integrity. A CHECKCONSTRAINTS will not be able to determine whether a CHAR(3) column’s value should be ‘bat’ or ‘cat’.

    If CHECKDB reports REPAIR_ALLOW_DATA_LOSS, the data being lost is not necessarily (or is unlikely to be) the “latest data within the now-gone tlog”. Old pages can be split so that new data can be inserted (and this is logged). Thus old pages _can_ be lost. It can take someone who is intimately familiar with not only with schema but also with individual data values to detect this problem (if lucky). Even a cross database select (between a restored old backup and a REBUILD_LOGged database) may _not_ allow the DBA to determine what data was lost: Should that column contain ‘bat’ or should it be ‘cat’?

    In retrospect, all it would have taken is any old full backup, plus a complete chain of tlog backups (made since that full backup) to avoid these steps entirely – search SQL Server books Online for “How to restore to the point of failure (Transact-SQL)”, and for further reading search for “point of failure”. Keep in mind the only ‘known good’ backup is one that _has been_ successfully restored. Practice restoring, even if it takes a quick and dirty manual log shipping script (unless one is running 2000’s Enterprise Edition). How far a DBA will go depends upon how much their employer values the cost of insurance🙂

  10. SQL newbie says:

    Thanks!
    A real lifesaver!

    Saved a production database

  11. Alfredo says:

    Thanks!
    A real lifesaver! =][2]

    Saved a production database

    =]

  12. katski11 says:

    Very informative… especially to those who are not quite well in these area

  13. alex says:

    thank you a lot! it just work

  14. Biswajit says:

    Thank you !!!!!!!
    Actually i was deleted ldf file from my computer permanently so is this possible to attach the databseagain without that ldf file and properly working???

    • Mike Gledhill says:

      > Actually i was deleted ldf file from my computer permanently so is this possible
      > to attach the databseagain without that ldf file and properly working???

      Yes, this is no problem.
      See my response earlier on this page, for instructions on how to connect to a
      SQL Server database, if you just have the .mdf file (or if you have deliberately
      deleted your .ldf file, as you want SQL Server to create a new one)

  15. Ragecage says:

    Lifesaver! Thanks a million!

  16. insurance mdf lamineted perilam board

  17. I drop a leave a response whenever I like a article on a website or if I
    have something to add to the discussion. It’s caused by the sincerness communicated in the article I looked at. And after this post How to attach SQL 2000 database (MDF) without a LDF file. LuckyStars opinions. I was excited enough to drop a comment🙂 I do have 2 questions for you if you don’t mind.
    Could it be simply me or do a few of the comments look like
    they are coming from brain dead visitors?😛 And, if you are
    posting on other social sites, I would like to
    follow you. Could you list every one of all your social sites like your Facebook page, twitter feed, or linkedin profile?

  18. Thanks for your personal marvelous posting! I genuinely enjoyed
    reading it, you can be a great author.I will make certain
    to bookmark your blog and may come back sometime soon.

    I want to encourage you to definitely continue your great work,
    have a nice weekend!

  19. Hi, Neat post. There is an issue together with
    your website in web explorer, may test this? IE nonetheless is the market chief and a
    good section of folks will pass over your fantastic
    writing because of this problem.

  20. Hi there! Do you know if they make any plugins to help with
    Search Engine Optimization? I’m trying to get my blog to rank for some targeted keywords but I’m
    not seeing very good gains. If you know of any please share.
    Kudos!

  21. ve done this to all their friends and they can claim the offer right away.
    Once you have a fan base you can then drive the fans to do what you want them to –
    buy your DVD, watch your film in the cinema, check it out at a festival, or even petition a distributor.
    The targeting, pricing, and setup seemed to improve with Facebooks newest creation.

  22. This piece of writing will help the internet people for creating new web site or even a weblog from start to end.

  23. Great beat ! I would like to apprentice whilst you amend
    your site, how can i subscribe for a blog web site?

    The account aided me a applicable deal. I were tiny bit familiar of this your broadcast
    offered bright transparent concept

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s