:4TheLuvofSharePoint – Ivan Sanders

The Attached Query

The really cool part about this query is how easy it is to modify.

The first section declares the variables, in this case there are 3.

The second section, sets the action for each of he declared variables

The last section loops through each DB using the PROC sp_MSforeachdb and runs each command,,,

Set the Recover Model to Simple, Backs up All Dbs except tempdb, and shrinks the DBs

-- DBCleanup for DEV

declare @cmd1 varchar(500)
declare @cmd2 varchar(500)
declare @cmd3 varchar(500)

-- Set Recovery Model to Simple except for tempdb
SET @cmd1 = 'if ''?'' <> ''tempdb'' ALTER DATABASE [?] SET RECOVERY SIMPLE'

-- Backup All Dbs Except tempdb to C:\temp
SET @cmd2 = 'if ''?'' <> ''tempdb'' backup database [?] to disk=''G:\MSSQL\Backup\?.bak'''

-- Shrink All Dbs Except tempdb 
SET @cmd3 = 'DBCC SHRINKDATABASE ([?], 5)'

-- Execute above commands
EXEC sp_MSforeachdb    @command1 = @cmd1,
                       @command2 = @cmd2,
                       @command3 = @cmd3

Download: SET_RecoveryModelShrinkBackup.sql

Ivan

No Comments