Web developers wiki ASP.NET Sitecore Sharepoint Kentico by Evident Interactive

Often you want all databases on a specific server to use the same recovery model (e.g. because your maintenance plan may not be able to handle all recovery models properly). In this case we will assume that all databases should be using the recovery model “simple”.

Here is a sql script for setting the recovery model to “simple” of all databases having the recovery model “full”:

declare @dbName nvarchar(222)
declare @sql nvarchar(max)

-- Retrieve all databases with recovery_model "full".
declare cursorSelectDbs cursor forward_only read_only for
	select name from sys.databases
	where recovery_model = 1 
	order by name

open cursorSelectDbs

fetch next from cursorSelectDbs into @dbName 

while (@@FETCH_STATUS = 0)
begin
	-- Create a sql-statement for setting the recovery_model to "simple".
	set @sql = 'alter database [' + @dbName + '] set recovery simple'

	print @sql

	execute (@sql)

	fetch next from cursorSelectDbs into @dbName 
end

close cursorSelectDbs
deallocate cursorSelectDbs

 © Evident Interactive BV