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

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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
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
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.

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

rhdyesAuthor Commented:
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.
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:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
That second link doesn't work correctly, it will need to be copied and pasted into a browser
Scott PletcherSenior DBACommented:
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.
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).
rhdyesAuthor Commented:
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.
rhdyesAuthor Commented:
This question can be closed
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.