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