SQL Server Status
In SQL Server 2000 you could query the sysdatabases table to find out the "status" of a database. This field was actually a bit mask and the sysdatabases table is only available in SQL Server 2005 for backwards compatability. The sysdatabases table "maps" to the sys.databases system view. The old sysdatabases "status" column now spans multiple columns in the new sys.databases system view...
| sysdatabases |
sys.databases |
| 1 = autoclose (ALTER DATABASE) |
is_auto_close_on |
| 4 = select into/bulkcopy (ALTER DATABASE using SET RECOVERY) |
log_reuse_wait |
| 8 = trunc. log on chkpt (ALTER DATABASE using SET RECOVERY) |
log_reuse_wait |
| 16 = torn page detection (ALTER DATABASE) |
page_verify_option |
| 32 = loading |
state |
| 64 = pre recovery |
state |
| 128 = recovering |
state |
| 256 = not recovered |
state |
| 512 = offline (ALTER DATABASE) |
state |
| 1024 = read only (ALTER DATABASE) |
is_read_only |
| 2048 = dbo use only (ALTER DATABASE using SET RESTRICTED_USER) |
user_access |
| 4096 = single user (ALTER DATABASE) |
user_access |
| 32768 = emergency mode |
state |
| 4194304 = autoshrink (ALTER DATABASE) |
is_auto_shrink_on |
| 1073741824 = cleanly shutdown |
is_cleanly_shutdown |
...if I've got any wrong please let me know.
Link to mapping SQL Server 2000 system tables to SQL Server 2005 system views http://msdn2.microsoft.com/en-us/library/ms187997.aspx.