We thought of planning to change all our database recovery model from FULL/BULK-Logged to SIMPLE.
It is little painful to change it by using GUI by checking each database properties and change it like below if you more databases:
Do we have any chance to change it by using all databases by using some script JJ , yes, here is there script which will help us to do the same.
set nocount on
---To check the databases current recovery models
select name, databasepropertyex(name, 'Recovery') as RecoveryModel
from master.dbo.sysdatabases
order by name
--Declaration of variables
DECLARE @DBName SysName, @sql VarChar(100)
-- Declare begin cursor to get the database names and get info from sys.databases catalog
DECLARE Cursor_db Cursor FOR SELECT Name From sys.Databases WHERE databasepropertyex(name, 'Recovery')
not in ('SIMPLE') and Name not in('TempDB','model','master','msdb')
-- Now using the cursor to loop through database names and change recovery model
OPEN Cursor_db Fetch NEXT FROM Cursor_db INTO @DBName
--While Loop with Alter database command
WHILE @@fetch_status = 0
BEGIN--print 'database is ' + @DBName
SET @sql='ALTER Database ' + @DBName + ' SET Recovery Simple'
PRINT @sql
--exec (@sql)
Fetch Next FROM Cursor_db INTO @DBName
END--clean up objects
CLOSE Cursor_db
Deallocate Cursor_db
It will show you the current database recovery models
It will show you the alter database statement to change the recovery model to SIMPLE.
Copy the statements and run it in new query window.
Note : If you want to execute the statement while generating them, just uncomment (exec @sql) in the above statement.
Please mail me if you are facing any issues in this regard so that I will try to help you.
