SQL Server - Set simple recovery model

Switch recovery model to SIMPLE for all databases in loop. This simple cursor query is useful when you need to keep your transactions log maintained automatically by SQL Server. And of course point in time recovery does not matter. That can be cases like UAT, STAGE or any non production environments. Fro more info about recovery models read this article.

TIP: Not very difficult to rewrite for switching into any recovery model

WARNING: Executing this script will alter all you databases. If you execute by mistake then you need to take care of proper backup chains when there is need to switch back to full recovery model

USE [master]
GO

-- declare variable for each database name
DECLARE @DatabaseName NVARCHAR(128)

-- define the cursor
DECLARE DatabaseCursor CURSOR
	-- Define the cursor dataset
FOR
SELECT 
	[name]
FROM sys.databases 
WHERE name <> 'tempdb' 
	AND recovery_model_desc = N'FULL'

-- start loop
OPEN DatabaseCursor

-- get information from the first row
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

-- loop until there are no more rows
WHILE @@fetch_status = 0
BEGIN
	PRINT 'Setting recovery model to simple for database [' + @databaseName + ']'

	EXEC ('ALTER DATABASE [' + @databaseName + '] SET RECOVERY SIMPLE')

	-- get information from next row
	FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
END

-- end loop and clean up
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
GO

Leave a Reply

Your email address will not be published. Required fields are marked *