Change All Databases to Simple Recovery

A bulk maintenance task: this script switches every online database (except master and tempdb) to the SIMPLE recovery model. It first issues the change with NO_WAIT so it does not stall on active sessions, then issues it again normally. Useful when you are preparing a set of databases for export or test use - remember that SIMPLE recovery means no transaction log backups.

SET NOCOUNT ON;

DECLARE @tSQL VarChar(MAX)
DECLARE @DatabaseName sysname

DECLARE DatabaseList CURSOR FAST_FORWARD FOR
	SELECT
		name
	FROM
		sys.databases
	WHERE
		name not in ('master', 'tempdb')
		AND state_desc = 'ONLINE'
OPEN DatabaseList
FETCH FROM DatabaseList INTO @DatabaseName

WHILE(@@FETCH_STATUS = 0)
BEGIN--WHILE
	----------------------------------------------------------------------------------------------------------------------------------

	SET @tSQL =
		'use [master];' + CHAR(13) + CHAR(10) +
		'ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY SIMPLE WITH NO_WAIT'  + CHAR(13) + CHAR(10)
	EXEC(@tSQL)

	SET @tSQL =
		'use [master];' + CHAR(13) + CHAR(10) +
		'ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY SIMPLE' + CHAR(13) + CHAR(10)
	EXEC(@tSQL)

	----------------------------------------------------------------------------------------------------------------------------------
	FETCH FROM DatabaseList INTO @DatabaseName
END--WHILE

CLOSE DatabaseList DEALLOCATE DatabaseList