Today's tip is a nice piece of code that will allow you to change the recovery model of all databases on a server.
You take all the time to set up a database backup plan for a server, and someone sticks a new database on it that does not conform to your backup schema. Depending on whether or not you backup the transaction logs, you'll need the Recovery Model to be set to 'simple' or 'full'. Tyically if one database is set the wrong way, your finely tuned backup plan will fail.
--delcare variables
declare @vchrTable varchar(200),
@vchrMsg varchar(200)
--declare the cursor
DECLARE curTables CURSOR FOR
SELECT name from master..sysdatabases where not name in ('tempdb')
--open the cursor
OPEN curTables
--loop through the cursor lines
FETCH NEXT FROM curTables INTO @vchrTable
WHILE @@FETCH_STATUS = 0
BEGIN
set @vchrMsg = 'ALTER DATABASE [' + @vchrTable + '] SET RECOVERY SIMPLE;'
print @vchrMsg
exec (@vchrMsg)
FETCH NEXT FROM curTables INTO @vchrTable
END
--clean up
CLOSE curTables
DEALLOCATE curTables