/*============================================================================
  File:     DBAlterForBatchOperation.sql

  Summary:  This script is functional yet really just pseudo code to give you an
			idea of how batch processing can be automated to switch recovery model.

  Date:     April 12, 2005

  SQL Server Version: 9.00.1116.08 (s/b IDW14)
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

-- 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