Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We had a power outage and one of our SQL 2000 databases is marked as suspect. We have a backup, but it's more than a day old (power outage happened just before the backup was scheduled) and we'd prefer not to loose that days worth of data.

Is there a way to restore the database, then copy the missing data out of the suspect database tables?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

I'll preface this by saying that once a DB is marked as "suspect" you're in a pretty bad place -- there's a good chance there will be some data loss no matter how you recover from this.

That said, you should be able to bring the database up in emergency mode and start your repair process (checkdb) from there. http://gargmanoj.wordpress.com/2008/07/17/how-to-repair-a-sql-server-2005-suspect-database/ has more details geared toward SQL Server 2005, but I believe they'll work in 2000 as well (commands reproduced below for posterity).

--- Find out why the DB is marked suspect
DBCC CHECKDB (‘YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS

--- Reset the DB to Emergency Mode
EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY

--- Initial checkdb to get things working
DBCC checkdb(‘yourDBname’)

--- Single-User Mode & "Dangerous" repair (possibility of data loss!)
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)

--- Make the DB usable again
ALTER DATABASE yourDBname SET MULTI_USER

Upon completing your repair (or better, prior to starting) you should get your boss to spring for a UPS for the database server. It's often easier to shake the money tree with the threat of data loss hanging over people's heads.

share|improve this answer
    
Nice tip, the idea of asking for the UPS before start ;) +1 –  SDReyes May 17 '11 at 15:33
    
We have a UPS, but it happened in the middle of the night, and apparently the power was off for a couple hours... –  AndyD273 May 17 '11 at 18:46
    
So the bad news is that when we tried to put it into emergency mode, the database got detached, and we couldn't reattach it. We ended up just restoring from backup, and a good chunk of the missing data had been uploaded to the webserver, so we were able to just reimport it from there. the rest wasn't a huge loss. it would be nice if there was a way to reattach it now with another name, just for future reference, but thankfully the emergency seems to have passed. –  AndyD273 May 17 '11 at 18:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.