HELP!!!!!!!!!! How to recover suspect sql 2000 database

Posted on 2006-04-05
Last Modified: 2012-06-22
I have a sql 2000 database that has been made suspect by having the log file deleted then replaced with an older copy.  This is what happened.  The database went into readonly mode because the log file had gotten so big it filled the volume.  Well my programmer deleted the log file thinking he could just put in an empty dummy in its place.  As you know this would not work so he restored a log file that was on a 3 day old backup.  This made the database suspect. I have detached the database at this time and it won't reattach.  Any suggestions?
Question by:rhdyes
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    This is bot tricky, but i found it as usefull

    detach ur db,
    move ur log file to someother location
    Create a new balnk text document (Better in the same folder) and rename it as urLog file (remember to remove the extension )
    then attach the db, point the log as new log file we just created
    LVL 28

    Assisted Solution

    LVL 4

    Expert Comment

    U have to restore the Log file from the mdf file. There is no use with the log file from the back up,because it will definitely will have mismatches.

    Once it is done, u can dettach the database, delete the log file, reattach the mdf file. U need not have to create a dummy log file. SQL Server will create one if it is not present.


    Author Comment

    My database is already dettached.  When Iry to attach without a log file I get an error that tells me "the physical file name may be incorrect.  When I use a dummy empty text file I get an i/o error telling me I reached the end of file trying to read the log file.  When I dettached this database, it was already in the suspect mode.
    LVL 15

    Accepted Solution

    Hi rhdyes,

    SQL will not recreate the log file on its own, you must use the sp_attach_single_file_db stored procedure.

    Depending on when your last SQL backup was you may lose data from the actions that have been taken.  In this situation the DB and log should have been backed up and then the DBCC SHINKFILE command should have been used, check out these:
    LVL 15

    Expert Comment

    That second link doesn't work correctly, it will need to be copied and pasted into a browser
    LVL 68

    Expert Comment

    I agree -- you have very likely damaged the db, possibly beyond the point of recovery.

    For the future, remember these rules:

    Rule 1) *NEVER* detach a db that has any problem.

    SQL may not allow the db to be re-attached.

    Rule 2) *NEVER* use EM to do a detach/attach.  *ALWAYS* use QA.

    EM is too flaky and can't be trusted for that, I've seen too many issues with it.

    Rule 3) *ALWAYS* backup a detached mdf before attempting to (re)attach it if you suspect that for any reason the attach won't work.  

    For each attach attempt, use a fresh copy of the original .mdf.  The attach attempt will update the .mdf, likely preventing future attach even once you correct the previous error(s).

    If the db is very large, you might have to skip this.  If so, if possible, take a complete backup of the db before beginning any activity.
    LVL 27

    Expert Comment

    Sadly you may have to walk away from trying to reattach the damaged db and do a restore with move option to restore the 3 day old backup and lose all of the changes since then (unless you have transaction backups).

    Author Comment

    Thanks for the input from everyone.  I called mircosoft and with some assistance from them I was able to recover my server.  FYI. here are the steps I took.

    I copied my mdf, ndf and ldf files of the orignal database to another location. I then deleted the files from the locations they were at on the drive.
    I "created" the same exact database with the same structure and names. I had my database spread across 2 drives.
    I then took the server down, renamed my log file, copied my original mdf and ndf files over the newly created files.
    Start sql and the database of course came up suspect.

    I started query analyzer to the master or temp database.
    I then ran these series of statements
    exec sp_configure 'allow override',1
    reconfigure with override
    update sysdatabases set status = 32768 where name='dbname'
    dbcc rebuild_log

    This rebuilt the transaction log.  

    I then ran
    dbcc checkdb('dbname')
    all the tables checked out  and everything seems to be fine.

    Author Comment

    This question can be closed

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now