/* Script to change SQL Server database modes via systables. This lets you set modes, such as EMERGENCY, that cannot be set via the sp_dboption stored procedure. */

-- change database mode via systables
USE Master
GO
-- Determine the original database status
SELECT [Name], DBID, Status
   FROM master.dbo.sysdatabases
GO
-- Enable system changes
sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
-- Update the database status
UPDATE master.dbo.sysdatabases
  SET Status = 16
  WHERE [Name] = 'DatabaseNameGoesHere'
GO
-- Disable system changes
sp_configure 'allow updates',0
GO
RECONFIGURE WITH OVERRIDE
GO
-- Determine the final database status
SELECT [Name], DBID, Status
   FROM master.dbo.sysdatabases
GO


/*Modes
1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.

Note that these are bit values. For example, 24 = 8+16, so 24
means that truncate log on checkpoint AND torn page detection
are both set.
*/