MSSQL query for changing recovery model of all the databases in a server.

The below SQL query will help you to change the recovery model of all the databases in the server.

Recently i noted one of my development server is having 160+ databases and it occupied more than 600 gig space in HDD.

In development box no need of transaction log backup and every day full backup maintenance plan is running.

tried to change all the database recovery model to SIMPLE from Full or BULK LOGGED and the below script helped me. Hope it will help me in after sometime as well as you.

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

Thanks.