/*
	UpgradeRecoveryModel.sql

	Changes recovery model from 'full' to 'simple' for full versions of SQL Server
	  (prevents the transaction logs from growing too large)
	sets a checkpoint and shrinks existing transaction log
	
	This script is called from the 'ControlSchemaUpdate_230to300.sql' script
	
	MSDE is set to 'SIMPLE' by default
*/

DECLARE @DB nvarchar(32)
SET @DB=db_name()
/* print out version type of database */
DECLARE @ver nvarchar(255)
SELECT @ver=@@version
IF(@ver LIKE N'%7.0%')
    PRINT N'Running SQL Server 7.0 Engine'
ELSE
    PRINT N'Running SQL Server 2000 Engine'
/* Using deprecated 'sp_dboptions' for backward compatibility with SQL 7.0 */
/* check the exisiting property value using a temp table */
IF NOT EXISTS (select * from tempdb.dbo.sysobjects WHERE id = object_id('tempdb.dbo.#temp'))
  CREATE TABLE #temp (propName nvarchar(32),propSetting nvarchar(32))
ELSE   /* delete any existing rows */
  DELETE #temp
DECLARE @PropVal nvarchar(11)
INSERT INTO #temp EXEC(N'sp_dboption ' +@DB + N', trunc')
SELECT @PropVal=propSetting FROM #temp
IF( LOWER(@PropVal) = N'off')
   BEGIN
     EXEC (N'sp_dboption ' + @DB + N', trunc, TRUE')
     DBCC SHRINKFILE (2,TRUNCATEONLY)
     PRINT N'Changed ' + @DB + N' truncate log on chkpt property to ON, SIMPLE recovery model.'
   END
ELSE
   PRINT N'The ' + @DB + N' truncate log on chkpt property is already set to SIMPLE recovery model.'
 /* before checking the model DB, delete any existing rows from temp DB */
IF EXISTS (select * from tempdb.dbo.sysobjects 
  WHERE id = object_id(N'tempdb.dbo.#temp'))
    delete #temp
/* now check if model db should be changed also (this is the default DB for all created DBs)*/
INSERT INTO #temp EXEC(N'sp_dboption model, trunc')
SELECT @PropVal=propSetting FROM #temp
IF( LOWER(@PropVal) = N'off')
   BEGIN
     EXEC (N'sp_dboption model, trunc, TRUE')
     PRINT N'Changed the model database truncate log on chkpt property to ON, SIMPLE recovery model.'
   END
ELSE
    PRINT N'The model database truncate log on chkpt property is already set to SIMPLE recovery model.'

/* delete the TEMP table*/
IF EXISTS (select * from tempdb.dbo.sysobjects 
    WHERE id = object_id(N'tempdb.dbo.#temp'))
  DROP TABLE #temp
