Better behavior with suspect databases in SQL Server 2008

(Quickie post #1 while it’s Kimberly’s turn to lecture this morning…)

I had a comment on my post dealing with suspect databases, saying that the 2008 behavior is much better. I hadn’t tried this so it was (really good) news to me.

So, trying the same steps from that blog post on 2008 – everything’s the same up to the point where the server has been restarted and the database is suspect. Now, if I try to detach the corrupt database, I get the following:

EXEC sp_detach_db 'DemoSuspect';
Msg 3707, Level 16, State 2, Line 1
Cannot detach a suspect or recovery pending database. It must be repaired or dropped.

VERY COOL!!!! This should *really* cut down the number of times people accidentally get themselves into trouble by detaching the database.

For the sake of completeness, if I really want to detach the suspect database, I can do it in 2008 if I put the database into EMERGENCY mode first (doesn’t mean I would though!)

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.