Oct
7
Written by:
Steve Gray
10/7/2010 9:00 AM
This topic is about as close as I get to running around with my hands in the air and yelling ‘The sky is falling! The sky is falling!’
If something is going bad with a SQL Server, a great percentage of the time it has to do with the Recovery Model of the databases (usually set to Simple or Full) and whether or not the data base backup scheme adequately deals with that.
I’ll not be launching into my (predictably) pedantic lecture on Recovery Models here. I’m just sharing code that will easily let you read or set the Recovery Model of some/all dbs on a server. If there are only 2 or 3 dbs this technique is overkill. But if there are 20, this is way easier.
I have two ways here, the first uses an undocumented stored procedure, the second uses manual code.
1: Use Master
2: go
3: EXEC sp_MSforeachdb 'ALTER DATABASE ? SET RECOVERY SIMPLE'
USE master
GO
-- Declare a variable to store the value [database name] returned by FETCH.
DECLARE @dbname sysname, @cmd varchar(1000)
declare @RM varchar(255)
-- 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
SELECT @RM = CONVERT(varchar(255),DATABASEPROPERTYEX(@dbname,'RECOVERY'))
PRINT @dbname + ' - ' + @RM
IF @RM <> '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 FULL
--uncomment to set the Recovery Model
--EXEC(@cmd)
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
As always, I welcome your comments!