-- ---------------------------------------------------------------------------------------
-- Written by Kimberly L. Tripp - all rights reserved.
-- 
-- For more scripts, sample code and Kimberly's schedule check out www.SQLSkills.com
-- For "More than Just Training," see the Industry Experts at www.SolidQualityLearning.com
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ---------------------------------------------------------------------------------------


-- This is a sample script based on the idea that Bulk_Logged Recovery Model should
-- only be used for the length of time that the batch operation is running.

-- A quick overview of the steps are as follows:
-- 1) Perform a Log Backup
-- 2) Change the Recovery Model to Bulk_Logged
-- 3) Perform the Batch Operation
-- 4) Change the Recovery Model back to Full
-- 5) Perform a Log Backup (remember this might be VERY large)

-- OK - now for the automation through TSQL script!

-- You might want to create a JunkDB to test this on?
CREATE DATABASE JunkDB
-- Back it up first...
BACKUP DATABASE JunkDB TO DISK = 'c:\Program Files\Microsoft SQL Server\mssql\backup\JunkDBBackup.bak'
	WITH NOINIT, STATS = 10
---------------------------------------------------------------------------------------------------------------------
-- 1) Perform a Log Backup
----------------------------------------------------------------------------------------------------------------------
BACKUP LOG JunkDB TO DISK = 'c:\Program Files\Microsoft SQL Server\mssql\backup\JunkDBBackup.bak'
	WITH NOINIT, STATS = 10

----------------------------------------------------------------------------------------------------------------------
-- 2) Change the Recovery Model to Bulk_Logged
----------------------------------------------------------------------------------------------------------------------

-- May want to programmatically check the status before setting.
IF DATABASEPROPERTYEX('JunkDB', 'Recovery') = 'FULL'
	ALTER DATABASE JunkDB 
		SET RECOVERY BULK_LOGGED
		-- Users CAN be actively processing in the DB when the Recovery Model is changed
		-- this may or may not be desired? Adding the "terminate" clause DOES NOT affect 
		-- current users if ONLY the recovery model is changed. In order to use the terminate
		-- option you must supply a change a database "state" change. In this case you might
		-- want to set RESTRICTED_USER. 
			SET RECOVERY BULK_LOGGED, RESTRICTED_USER WITH ROLLBACK AFTER 10
		-- Or you may want to change the state from READ_ONLY to READ_WRITE?
		-- Use the following WITHOUT the RESTRICTED_USER setting
			SET RECOVERY BULK_LOGGED, READ_WRITE WITH ROLLBACK AFTER 10
		-- OR if both settings are desired
			SET RECOVERY BULK_LOGGED, RESTRICTED_USER, READ_WRITE WITH ROLLBACK AFTER 10 
		-- Changing a state option with ROLLBACK AFTER will terminate active users and 
		-- rollback their processing transactions after n (10) seconds. 
		-- NOTE: In order to rollback the acess transactions, this command may take MORE than 
		-- 10 seconds.
go

----------------------------------------------------------------------------------------------------------------------
-- 3) Perform the Batch Operation
----------------------------------------------------------------------------------------------------------------------

-- The batch operation should be broken into smaller components if possible and 
-- you should consider performing Log backups at various intervals throughout the 
-- batch operation so at to further minimize the potential work loss exposure.
-- NOTE: You can only backup the log after a batch TRANSACTION has completed.

-- Run though your processes to update all of the data via
-- automated mass modifications, scripts, batches, etc.

----------------------------------------------------------------------------------------------------------------------
-- 4) Change the Recovery Model back to Full
----------------------------------------------------------------------------------------------------------------------

-- Again, may want to programmatically check the status before setting.
IF DATABASEPROPERTYEX('JunkDB', 'Recovery') = 'Bulk_Logged'
	ALTER DATABASE JunkDB 
		SET RECOVERY FULL, MULTI_USER
		-- Or if you also changed the state
		-- Use the following WITHOUT the RESTRICTED_USER setting
		SET RECOVERY FULL, READ_ONLY
		-- OR if both settings are needed
		SET RECOVERY FULL, MULTI_USER, READ_ONLY

go

---------------------------------------------------------------------------------------------------------------------
-- 5) Perform a Log Backup (remember this might be VERY large)
----------------------------------------------------------------------------------------------------------------------
BACKUP LOG JunkDB TO DISK = 'c:\Program Files\Microsoft SQL Server\mssql\backup\JunkDBBackup.bak'
	WITH NOINIT, STATS = 10

-- For more syntax in SQL Server 2000 check out the BOL
-- Under ALTER DATABASE
-- TRY TO AVOID sp_dboption as it's not feature rich and is ONLY 
-- provided for backward compatibility.

-- NOTE: Some have asked if this script could be used to set FILEGROUP properties... 
-- While the basic principle is the same there are a couple of differences.
-- First, changing a filegroup to READONLY\READWRITE requires that users aren't actively
-- using the database yet unfortunately the terminate option only works when changing
-- a state option for the entire database. If you want to set a filegroup property, I'd recommend
-- setting the entire database to restricted_user first and then set it back to multi_user (maybe?). 
-- In the state change for the database you can terminate connections. For example

-- ALTER DATABASE JunkDB
-- 	ADD FILEGROUP FGName
-- 
-- ALTER DATABASE [JunkDB] 
-- 	ADD FILE
-- 		(NAME = N'JunkDBNewFile', 
-- 		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql\data\JunkDBNewFile.NDF' , 
-- 		SIZE = 10, FILEGROWTH = 10%) 
-- 		TO FILEGROUP FGName
go
ALTER DATABASE JunkDB 
	SET RESTRICTED_USER WITH ROLLBACK AFTER 10

ALTER DATABASE JunkDB 
	MODIFY FILEGROUP FGName READWRITE

-- IMPORTANT NOTE: Up to and including SQL2K SP2 however, I DO NOT recommend using
-- the read only filegroup property. For more information see the SQL Server Magazine
-- Article "Before Disaster Strikes" by Kimberly L. Tripp and the "Isolated Disk Failure"
-- sidebar in September 2002 (www.sqlmag.com), InstantDoc #26067. 
go