list_all_db_recovery_modes.sql
USE master
GO
SET QUOTED_IDENTIFIER ON
-- Declare the variable to store the value [database name] returned by FETCH.
DECLARE @dbname sysname
-- Declare a cursor to iterate through the list of databases
DECLARE db_recovery_cursor CURSOR FOR
SELECT name from sysdatabases
-- Open the cursor
OPEN db_recovery_cursor
-- Perform the first fetch and store the value in a variable.
FETCH NEXT FROM db_recovery_cursor INTO @dbname
-- loop through cursor until no more records fetched
WHILE @@FETCH_STATUS = 0
BEGIN
-- display each dataabase and recovery model setting not set to FULL
IF (SELECT DATABASEPROPERTYEX(@dbname,'RECOVERY')) <> 'FULL'
BEGIN
SELECT DATABASEPROPERTYEX(@dbname,'RECOVERY'), @dbname
END
-- fetch the next database name
FETCH NEXT FROM db_recovery_cursor INTO @dbname
END
-- close the cursor and deallocate memory used by cursor
CLOSE db_recovery_cursor
DEALLOCATE db_recovery_cursor
set_all_db_recovery_to_full.sql
USE master
GO
-- Declare a variable to store the value [database name] returned by FETCH.
DECLARE @dbname sysname, @cmd varchar(1000)
-- Declare a cursor to iterate through the list of databases
DECLARE db_recovery_cursor CURSOR FOR
SELECT name from sysdatabases
-- Open the cursor
OPEN db_recovery_cursor
-- Perform the first fetch and store the value in a variable.
FETCH NEXT FROM db_recovery_cursor INTO @dbname
-- loop through cursor until no more records fetched
WHILE @@FETCH_STATUS = 0
BEGIN
-- create the alter database command for each database
SET @cmd = 'ALTER DATABASE ' + @dbname + ' SET RECOVERY FULL'
-- alter each dataabase setting the recovery model to FULL
EXEC(@cmd)
PRINT @dbname
-- fetch the next database name
FETCH NEXT FROM db_recovery_cursor INTO @dbname
END
-- close the cursor and deallocate memory used by cursor
CLOSE db_recovery_cursor
DEALLOCATE db_recovery_cursor
set_all_db_recovery_to_simple.sql
USE master
GO
-- Declare a variable to store the value [database name] returned by FETCH.
DECLARE @dbname sysname, @cmd varchar(1000)
-- Declare a cursor to iterate through the list of databases
DECLARE db_recovery_cursor CURSOR FOR
SELECT name from sysdatabases
-- Open the cursor
OPEN db_recovery_cursor
-- Perform the first fetch and store the value in a variable.
FETCH NEXT FROM db_recovery_cursor INTO @dbname
-- loop through cursor until no more records fetched
WHILE @@FETCH_STATUS = 0
BEGIN
-- create the alter database command for each database
SET @cmd = 'ALTER DATABASE ' + @dbname + ' SET RECOVERY SIMPLE'
-- alter each dataabase setting the recovery model to FULL
EXEC(@cmd)
PRINT @dbname
-- fetch the next database name
FETCH NEXT FROM db_recovery_cursor INTO @dbname
END
-- close the cursor and deallocate memory used by cursor
CLOSE db_recovery_cursor
DEALLOCATE db_recovery_cursor
Download: list_all_db_recovery_modes
Download: set_all_db_recovery_to_full
Download: set_all_db_recovery_to_simple