Change all SQL databases recovery model (Full, Simple or Bulk-Logged)

If you ever have the need to change every databases recovery model, it can take a while to do this manually for each one. I had this need, basically to reduce the space required for backups as the individual transactions weren’t required to be ever rolled-back, only that that the databases were backed up regularly.

The following will loop through each database on the server instance and change the recovery model to simple:

-- Variables to be used in the script
DECLARE @name VARCHAR(50) -- the name of the database
DECLARE @strSQL nvarchar(150) -- the SQL that will be executed against each database

USE Master

-- Declare a cursor that will return all user databases
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ( 'master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name -- Bring the first database name into the @name variable

WHILE @@FETCH_STATUS = 0 -- loop through each of the databases, until no records left
BEGIN
-- Create a SQL statement that will be executed
SET @strSQL = 'ALTER DATABASE [' + @name + '] SET RECOVERY SIMPLE' -- Concatenate the SQL statement with the database name variable
EXEC SP_EXECUTESQL @strSQL -- run the statement

FETCH NEXT FROM db_cursor INTO @name -- Return the database
END

CLOSE db_cursor -- Without closing & deallocating the cursor, running the same code will generate a failure
DEALLOCATE db_cursor

You can of course change the recovery model to anything in the concatenating line, or even execute another SQL line against each database (i.e. backup the database).

Please view the following MSDN article for further details on the various recovery models available:
http://msdn.microsoft.com/en-us/library/aa173531%28SQL.80%29.aspx