PostRestoreSteps.sql 2.88 KB
Newer Older
dwentzel's avatar
dwentzel committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92

IF OBJECT_ID('dbo.PostRestoreSteps') IS NOT NULL
    DROP PROCEDURE dbo.PostRestoreSteps;
GO

CREATE PROCEDURE dbo.PostRestoreSteps
AS
BEGIN
	/*
	Performs a series of steps "post restore" of a db to ensure it is setup correctly.  
	See also:  
			http://www.davewentzel.com/content/post-restore-process
		--turns off autoclose and autoshrink if enabled. 
		--sets compat mode to 100 (SQL 2008).  this is needed for MERGE statement for instance.   
		--sets TRUSTWORTHY and DB_CHAINING 
		--sets the db owner to sa
		--enables Service Broker (see http://git.davewentzel.com/dwentzel/servicebrokercode/blob/master/ServiceBrokerEnable.sql)
		--syncs logins and passwords.  
	
	Feel free to add or change anything you like to match your requirements.  Some other suggestions are:
		--setting up/ensuring proper database hygiene SQL Agent jobs are in place (backup/update stats/reorg)
		--enabling the CLR
		--re-enabling custom events
		--optionally removing "sensitive" data
		--changing other "environment" data that you persist in your db

	*/

	declare @exec_str varchar(2000)
	declare @db varchar(2000) 
	SELECT @db = db_name()
	
	--Set various db options
	SELECT @exec_str = 'ALTER DATABASE ' + quotename(@db) + ' SET AUTO_CLOSE OFF'
	PRINT @exec_str
	EXEC (@exec_str)
	SELECT @exec_str = 'ALTER DATABASE ' + quotename(@db) + ' SET AUTO_SHRINK OFF'
	PRINT @exec_str
	EXEC (@exec_str)
	SELECT @exec_str = 'ALTER DATABASE ' + quotename(@db) + ' SET COMPATIBILITY_LEVEL = 100'
	PRINT @exec_str
	EXEC (@exec_str)
	SELECT @exec_str = 'ALTER DATABASE ' + quotename(@db) + ' SET TRUSTWORTHY ON'
	PRINT @exec_str
	EXEC (@exec_str)
	SELECT @exec_str = 'ALTER DATABASE ' + quotename(@db) + ' SET DB_CHAINING ON'
	PRINT @exec_str
	EXEC (@exec_str)
	
	
	--Check/set db owner.  
	if (	
		select suser_sname(owner_sid) as db_owner
		from sys.databases
		WHERE name = db_name()
		) <> 'sa'
	BEGIN
		SELECT @exec_str = 'ALTER AUTHORIZATION ON DATABASE::' + @db + ' TO SA;'
		PRINT @exec_str
		EXEC (@exec_str)
	END;	
	

	--synch logins/users
	Declare @BadUser varchar (100) 
	Declare @FixUser varchar (100) 

	DECLARE BadUsers_cursor INSENSITIVE CURSOR FOR	
		select u.name
		from sysusers u, master.dbo.syslogins l
		where u.name = l.name collate SQL_Latin1_General_CP1_CI_AS
		and u.sid <> l.sid
	--	and u.sid =  l.sid  (test for the good ones)
	Open BadUsers_cursor
	FETCH NEXT FROM BadUsers_cursor INTO @BadUser
	WHILE (@@FETCH_STATUS <> -1)
	Begin
		Select @FixUser = 'sp_change_users_login ''Update_One'', ''' + ltrim(@BadUser) + ''', ''' + ltrim(@BadUser) + ''''
  		Print @FixUser --(to print commands)
		Exec (@FixUser)
		FETCH NEXT FROM BadUsers_cursor INTO @BadUser
	End
	CLOSE BadUsers_cursor
	DEALLOCATE BadUsers_cursor
	
	IF EXISTS (select * from sysobjects WHERE object_id = object_id('ServiceBrokerEnable'))
	BEGIN
		EXEC ServiceBrokerEnable 
	END;

END
GO