Changing database modes via systables

8. January 2008 by Bennett.

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.
*/

Posted in T-SQL, SQL Server | Print | No Comments »