Setting the Recovery Model of all SharePoint Databases
Since my customer is using Data Protection Manager 2007 to protect their SharePoint Farm workloads - they decided they wanted to set all the SharePoint databases to use the Simple Recovery Model. They have 30+ Farms so they needed a way to do this quickly per each Farm.
The following SQL script creates a Stored Procedure that will loop through all the Databases in the SQL instance, skipping the system databases, and setting the rest of the databases returned to use the Simple model:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[SetSharePointRecoveryModel]
as
/* variables */
declare @dbname varchar(80)
declare @dbmodel varchar(80)
declare @msg varchar(120)
/* create a cursor for all the databases */
declare system_databases cursor for
select Name, Recovery_Model_Desc from Sys.Databases
open system_databases
fetch system_databases into @dbname, @dbmodel
if (@@fetch_status = 2)
begin
print 'No databases found.'
close system_databases
return
end
while (@@fetch_status = 0)
begin
if (@dbname = 'tempdb' or @dbname = 'master' or @dbname = 'model' or @dbname = 'msdb')
begin
select @msg = @dbname + ' -- Not updating recovery model of system databases.'
print @msg
end
else
if (@dbmodel = 'FULL')
begin
EXECUTE ('ALTER DATABASE [' + @dbname + '] SET RECOVERY SIMPLE WITH NO_WAIT;');
select @msg = @dbname + ' -- Set to SIMPLE.'
print @msg
end
else
begin
select @msg = @dbname + ' -- Already set to SIMPLE.'
print @msg
end
fetch system_databases into @dbname, @dbmodel
end
close system_databases
deallocate system_databases
This creates the sproc in the Master database. Using it then is simply:
USE Master
EXEC dbo.SetSharePointRecoveryModel
More information on SQL Recovery Models:
Overview of the Recovery Models
http://technet.microsoft.com/en-us/library/ms189275(SQL.90).aspx
