We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

rhdyes
rhdyes asked
on
Medium Priority
15,910 Views
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?
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Top Expert 2006
Commented:

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

Regards,
RKM.

Author

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.
CERTIFIED EXPERT
Commented:
Hi rhdyes,

SQL will not recreate the log file on its own, you must use the sp_attach_single_file_db stored procedure.  
http://msdn2.microsoft.com/en-us/library/ms174385.aspx

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:

http://msdn2.microsoft.com/en-us/library/ms186865.aspx
http://msdn2.microsoft.com/en-us/library/ms189493(SQL.90).aspx
CERTIFIED EXPERT

Commented:
That second link doesn't work correctly, it will need to be copied and pasted into a browser
http://msdn2.microsoft.com/en-us/library/ms189493(SQL.90).aspx
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

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

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

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.

Author

Commented:
This question can be closed
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.