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?
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:
Since these are detailed discussions, I will not rehash them. But read carefully.
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.