Set the recovery model to “SIMPLE” programmatically

No Comments

Written by Luis Miguel – Originally published on September 18, 2010.

Here is a tiny stored procedure to set the recovery model to SIMPLE on every user database in your instance.

CREATE PROCEDURE [dbo].[Dba_reset_recovery_model]
AS
  DECLARE @db_name VARCHAR(128) -- Database name
  DECLARE @alterdb VARCHAR(512) -- Dynamic SQL command
  DECLARE db_cursor CURSOR FOR
    -- Get the USER DATABASES that ARE ONLINE and their recovery model IS NOT SIMPLE
    SELECT name
    FROM   sys.databases
    WHERE  database_id > 4
           AND [state] = 0
           AND [recovery_model] <> 3
  OPEN db_cursor
  FETCH NEXT FROM db_cursor INTO @db_name
  WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @alterdb = ‘ALTER DATABASE ’ + @db_name + ‘ SET RECOVERY SIMPLE’
        EXEC (@alterdb)
        FETCH NEXT FROM db_cursor INTO @db_name
    END
  CLOSE db_cursor
  DEALLOCATE db_cursor 

Warnings:
1 – This script is known to run well in SQL Server 2008, I have not tested it in any other version of SQL Server.
2 – Do not use this script in a Production system without prior extensive testing in a lab environment.

Powered by Netfirms