Change Recovery Model of all your SQL Server databases

In the below script, we use a system table to get all the database names on our server. Then we use a cursor to loop through all the records and then change the recovery model.


--Declaration of variables
DECLARE @DBName SysName, @sql VarChar(100)
-- Declare begin cursor to get the database names and get info from sys.databases catalog
DECLARE Cursor_db Cursor FOR SELECT Name From sys.Databases WHERE Name != 'TempDB'
-- Now using the cursor to loop through database names and change recovery model
OPEN Cursor_db Fetch NEXT FOM Cursor_db INTO @DBName 
--While Loop with Alter database command
WHILE @@fetch_status = 0
BEGIN--print 'database is ' + @DBName 
SET @sql='ALTER Database ' + @DBName + ' SET Recovery Simple'
PRINT 'sql is ' + @sql
exec (@sql)

Fetch Next FROM Cursor_db INTO @DBName 
END--clean up objects
CLOSE Cursor_db 
Deallocate Cursor_db