Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database that has been marked SUSPECT because the volume that both the data and the log file were on suddenly disappeared (an accident - this is a developer workstation). I tried bringing the database back online:

ALTER DATABASE D SET ONLINE

That did nothing. On the web the procedure sp_resetstatus is being recommended but it appears to be obsolete.

I do not wish to run DBCC CHECKDB, at least not right now. The database is very big. What is the official way to bring a SUSPECT database online as quickly as possible?

share|improve this question
add comment

2 Answers

As of SQL Server 2012, sp_resetstatus is still supported. However, Paul Randal has some excellent counsel, including do not detach that database.

The first and best answer is to restore a good backup.

If that does not work and you can retrieve the files from somewhere (where?), check out Paul's posts at:

http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/ http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/

Since these are detailed discussions, I will not rehash them. But read carefully.

share|improve this answer
    
He uses CHECKDB in those (excellent) posts which I am trying to avoid. I'm quite sure that the database is ok. I wish to get back to using it much faster than it would take to CHECKDB it (hours). It should be a metadata-only operation like sp_resetstatus does. –  usr Sep 26 '13 at 13:13
    
From your comment I understand that you have reattached the volume and you do have the data and log files. However, losing the disk could very well have caused corruption. Paul's second post on Emergency mode apparently matches your case. If you want to try something else, try copying the two files to a backup location, then (breathe deep) detach the database and then reattach the files. (But this is exactly what Paul recommends against doing.) If it seems to work, you really should still do a DBCC. –  RLF Sep 26 '13 at 13:30
add comment
up vote 0 down vote accepted

Given that the database is physically intact you can bring it online using

ALTER DATABASE DatabaseName SET EMERGENCY
ALTER DATABASE DatabaseName SET ONLINE

These statements execute very quickly. They just seem to remove the SUSPECT status and start recovery if necessary.

share|improve this answer
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.