I saw a post recently by someone asking how to get a database to be suspect. They said they had added a data file and then remove it, but the database wasn’t suspect. This was true in the past (v6.x), but it seems that as the product has advanced, there are more database states, and things like a missing file do not force a suspect state.

Instead the database on 2008 goes into an Offline status. Gail Shaw, who pointed this out to me in a blog, actually wrote her own blog on this topic, so I won’t duplicate the code.

I stopped my local SQL Server, then renamed one of the db files as shown:

In essence, this file is “lost” to SQL Server, just like it had been deleted, moved, etc. When I restart SQL Server, I see this:

No way to expand out db2 here. If I look at properties, I see it in “Shutdown” status

Strangely, if I right click, I have the “Take offline” available, not not “Take Online”. If I click that, I get the database offline, but I cannot then bring it back online. It gives me an error, as it should since the files are missing. The error log shows the issues with

I can rename the file without taking SQL Server down, and then bring the db online. It’s the same as recovering this disk drive or fixing the file somehow.

I’m glad this changed, since we should have more details about what happened, and a “suspect” database should be reserved for more serious issues. I think having this “shutdown” or “offline” is a better status.

The databases status values are here: http://msdn.microsoft.com/en-us/library/ms190442.aspx