Friday, January 27, 2012

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
--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 
--clean up objects
CLOSE Cursor_db 
Deallocate Cursor_db 


  1. Good write-up, I am regular visitor of one's web site, maintain up the nice operate, and It's going to be a regular visitor for a lengthy time.
    Triple 8 Brainsaver Rubber Helmet with Sweatsaver Liner

  2. whoah this blog is great i like reading your posts. Keep up the great paintings! You realize, many individuals are searching round for this info, you could help them greatly.
    Sorel Women's Tofino CVS Boot

    1. Hello Arun,

      I do it with the help of SQL server management studio. I have learned something new from this post. Thanks for sharing.

      Mark Willium