Not sure if anyone noticed this. Looks like the status bits defined in SMO object is not actually same as that stored in status column of sysdatabases. As I am using SMO i am not able to exactly figure out the status of the database when status goes to offline. I know that I can use DATABASEPROPERTYEX which gives the status in string but I would like to get the status bit value.
Status bits defined in sysdatabases
Status bits, some of which can be set by using ALTER DATABASE as noted:
1 = autoclose (ALTER DATABASE)
4 = select into/bulkcopy (ALTER DATABASE using SET RECOVERY)
8 = trunc. log on chkpt (ALTER DATABASE using SET RECOVERY)
16 = torn page detection (ALTER DATABASE)
32 = loading
64 = pre recovery
128 = recovering
256 = not recovered
512 = offline (ALTER DATABASE)
1024 = read only (ALTER DATABASE)
2048 = dbo use only (ALTER DATABASE using SET RESTRICTED_USER)
4096 = single user (ALTER DATABASE)
32768 = emergency mode
4194304 = autoshrink (ALTER DATABASE)
1073741824 = cleanly shutdown
Multiple bits can be ON at the same time
Status bit defined in SMO.
// The Microsoft.SqlServer.Management.Smo.DatabaseStatus enumeration is a list
// of constant values that specify the status of a database, whether it be loading,
// recovering, or normal, for example. This enumeration has a FlagsAttribute
// attribute that allows a bitwise combination of its member values.
public enum DatabaseStatus
// The database is available.
Normal = 1,
// The database is going through the restore process.
Restoring = 2,
// The database is waiting to go through the recovery process.
RecoveryPending = 4,
// The database is going through the recovery process.
Recovering = 8,
// The database has been marked as suspect. You will have to check the data,
// and the database might have to be restored from a backup.
Suspect = 16,
// The database has been taken offline.
Offline = 32,
// The database is inaccessible. The server might be switched off or the network
// connection has been interrupted.
Inaccessible = 62,
// The database is in standby mode.
Standby = 64,
// The server on which the database resides has been shut down.
Shutdown = 128,
// The database is in emergency mode.
EmergencyMode = 256,
// The database has been automatically closed.
AutoClosed = 512,