Change Recovery Model of all your SQL Server databases in one shot

Change Recovery Model of all your SQL Server databases in one shot

/*--------------------------------------------------------------------------------

Sometimes it is necessary to change properties of all your databases in one shot

In this script, we use a system table to get all the database names on our server.

Next we use a cursor to loop through all the records and then change the recovery model using Alter Database command

--------------------------------------------------------------------------------*/

--Declaration of variables

declare
@dbnm 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'

-- Using a cursor to loop through database names and change recovery model

open cursor_db
fetch next from cursor_db into @dbnm

--While Loop with Alter database command

while @@fetch_status = 0

begin

--print 'database is ' + @dbnm

set @sql='alter database ' + @dbnm + ' set recovery simple'
print 'sql is ' + @sql
exec (@sql)


fetch next from cursor_db into @dbnm
end

--clean up objects

close cursor_db
deallocate cursor_db

Tags: SQL Server 2008, Recovery Model, While loop, Cursor

Source:

http://sqlserver2008tutorial.com/member.htm