Software Technology Tips

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
IF (SELECT DATABASEPROPERTYEX(@dbname,'RECOVERY')) <> 'SIMPLE' and @dbName <> 'tempdb' 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 SIMPLE
EXEC(@cmd)


PRINT @dbname
end


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