Tag Archives: database

Set multiple SQL databases Recovery MODE to Simple

It is always handy to have an automated script to perform task for you. Manual click vis SSMS could be tedious and prone to error.

I came out with a simple SQL which allow me to set recovery mode of all database in one instance to Simple or Full.

GO
DECLARE @name varchar(255),@sql varchar (500)
DECLARE databases CURSOR FOR
SELECT name FROM sys.databases where name not in (‘master’,’tempdb’,’model’,’msdb’)

OPEN databases

— Perform the first fetch.
FETCH NEXT FROM databases
INTO @name
— Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
— This is executed as long as the previous fetch succeeds.
SET @sql =
‘USE [‘+@name+’];
ALTER DATABASE [‘+ @name + ‘] SET RECOVERY SIMPLE;’
+ ‘DBCC SHRINKFILE([‘+@name+’_log], 1)’
print @sql
exec(@sql)
FETCH NEXT FROM databases
INTO @name
END

CLOSE databases
DEALLOCATE databases
GO

 

Note that if you are doing Log Shipping, you need to use FULL recovery mode or Bulk-logged