How to Change Recovery Model from Simple to Full for Multiple Databases in SQL Server - SQL Server Tutorial

Scenario:

You are working as SQL Server DBA or Developer and you need to prepare the script that should change the recovery model of databases which are in Simple to Full.

The below script can be used to change the recovery model from simple to full, You can change the select query to include or exclude database for which you want to change recovery model from simple to full.


USE MASTER
GO
DECLARE @DBName VARCHAR(200)
DECLARE DB_Cursor CURSOR FOR
 --Select the database for which you want to change 
 --Recovery model from Simple to Full
  SELECT name
  FROM   sys.databases
  WHERE  name NOT IN ( 'master', 'tempdb', 'model', 'msdb' )
  and recovery_model_desc='Simple'
OPEN DB_Cursor
FETCH NEXT FROM DB_Cursor INTO @DBName
WHILE @@FETCH_STATUS = 0
  BEGIN
     
            DECLARE @SQL NVARCHAR(500)=Null

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

            PRINT @SQL

            EXEC ( @SQL)
        
      FETCH NEXT FROM DB_Cursor INTO @DBName
  END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor

--Check if Recovery Model is changed to Full
  SELECT name, recovery_model_desc 
  FROM   sys.databases


I executed  the above script and it was able to change recovery model for database from simple to full.

How to change recovery model of databases from simple to full in SQL Server

No comments:

Post a Comment