SQL Query: Set All DBs to the Simple Recovery Model–DEV
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
del.icio.us Tags: SQL Query,Set Recovery Model