How to Change Recovery Model to Full for all User Databases in SQL Server - SQL Server Scripts

Scenario:

You are working as SQL Server DBA or developer, You need to prepare some script that should change the recovery model for all the user databases to Full recovery model.

The below script can be used to change the recovery model of all user databases to Full. You can also change the select query to include or exclude databases in the list.

USE master 
go 
DECLARE @DBName VARCHAR(200) 
DECLARE db_cursor CURSOR FOR 
  --Make changes to Query to Include or Exclude database 
  SELECT NAME 
  FROM   sys.databases 
  WHERE  NAME NOT IN ( 'master', 'tempdb', 'model', 'msdb' ) 

OPEN db_cursor 

FETCH next FROM db_cursor INTO @DBName 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      IF ( (SELECT recovery_model_desc 
            FROM   sys.databases 
            WHERE  NAME = @DBName) <> 'FULL' ) 
        BEGIN 
            DECLARE @SQL NVARCHAR(500) 

            SET @SQL='ALTER DATABASE [' + @DBName 
                     + '] SET RECOVERY FULL' 

            PRINT @SQL 

            EXEC ( @SQL) 
        END 

      FETCH next FROM db_cursor INTO @DBName 
  END 

CLOSE db_cursor 

DEALLOCATE db_cursor 

--Check recovery model for all databases 
SELECT NAME AS DBName, 
       recovery_model_desc 
FROM   sys.databases 

No comments:

Post a Comment