-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp - all rights reserved.
-- 
-- For more scripts and sample code, check out 
-- 	http://www.SQLskills.com
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-- Execute this whole script to create the sp_SetEmergencyMode stored procedure in Master.
-- Best Viewed with Courier New 12pt. and Tabs saved as 4 spaces not 8. (Tools, Options, Editor)

-- To use the sp_SetEmergencyMode procedure once created, use:
-- sp_SetEmergencyMode @DBName

-----------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------- IMPORTANT ----------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------
-- @DBName must be a user-defined database and should only be used as a ONE time and LAST
-- resort option. You will need to VERIFY and copy your data out of this file and into a new/clean 
-- database. 

-- If the damaged database is one of the following there are MANY circumstances for which you 
-- instead consider calling PSS. Do not change these to emergency mode.
--	master, model, msdb, distribution (did you name it something else?), tempdb
-- For recovering system databases you should read these KB articles:
-- 	Q264474 – PRB: System Database Backups Cannot be Restored on a Different Build of SQL Server
--	Q224071 – Moving SQL Server databases to a new location with Detach/Attach
--	Q253817 – HOW TO: Backup the Last Transaction Log When the Master and the Database Files Are Damaged

-- Again, before setting emergency mode you should consider calling PSS and starting a support 
-- incident. PSS will be able to guide you through this process and they may be aware of other 
-- options/steps you can take to further minimize data loss.

-- TEST, TEST, TEST!!!!!

USE master
go

IF OBJECTPROPERTY(object_id('sp_SetEmergencyMode'), 'IsProcedure') = 1
	DROP PROCEDURE sp_SetEmergencyMode
go

-- A procedure created when these settings are set - retains these rights to update the system tables!
EXEC sp_configure 'allow updates', 1
go
RECONFIGURE WITH OVERRIDE
go

CREATE PROCEDURE sp_SetEmergencyMode
(
	@DBName			sysname		= NULL
)		
AS
SET NOCOUNT ON

-- Written by Kimberly L. Tripp of SYSolutions, Inc.
-- For more code samples go to http://www.SQLskills.com
IF (@DBName IS NOT NULL AND DATABASEPROPERTYEX(@DBName, 'Collation') IS NULL)
		BEGIN
			RAISERROR('Database: %s does not exist or is not available.', 16, 1, @DBName)
			RETURN
		END
	ELSE
		BEGIN
			IF @DBName IN ('master', 'tempdb', 'model', 'msdb', 'distribution')
				BEGIN
					RAISERROR('Database: %s is a system database that CANNOT be be changed to emergency mode. Please call PSS to open a support incident.', 16, 1, @DBName)
					RETURN
				END
		END

UPDATE master.dbo.sysdatabases
	SET status = 32768
	WHERE [name] = @DBName
go

-- Turn this off as fast as possible!
EXEC sp_configure 'allow updates', 0
go
RECONFIGURE WITH OVERRIDE
go

-- EXEC sp_SetEmergencyMode 'pubs'