Monday, March 21, 2011

Keeping SharePoint VM lean - Simple Recovery Model for Sharepoint


Just received a forwarded blog from my lead (Winson Woo), about the keeping the SharePoint Development VM small…
Since you typically don't care about potential data loss in SharePoint development VMs -- and consequently never bother to configure scheduled database backups -- you might as well always use the Simple recovery model for all of your development databases.” – Jeremy Jameson;


SQL databases are default to Full Recovery Model, which will take up lot of disk space, for SharePoint Development VM, we mostly concern about the VM size and will try to minimize the amount of disk space consumed, so the following script will be handy.


In SQL Management Studio, just execute the following script.
Note: This script will set every database recovery model to Simple, except the 'master', 'msdb' and 'tempdb'.

IF OBJECT_ID('tempdb..#CommandQueue') IS NOT NULL DROP TABLE #CommandQueue

CREATE TABLE #CommandQueue
(
ID INT IDENTITY ( 1, 1 ) , SqlStatement VARCHAR(1000)
)

INSERT INTO #CommandQueue
(
SqlStatement
)
SELECT
'ALTER DATABASE [' + name + '] SET RECOVERY SIMPLE'
FROM
sys.databases
WHERE
name NOT IN ( 'master', 'msdb', 'tempdb' )

DECLARE @id INT

SELECT @id = MIN(ID)
FROM #CommandQueue

WHILE @id IS NOT NULL
BEGIN
DECLARE @sqlStatement VARCHAR(1000)

SELECT
@sqlStatement = SqlStatement
FROM
#CommandQueue
WHERE
ID = @id

PRINT 'Executing ''' + @sqlStatement + '''...'

EXEC (@sqlStatement)

DELETE FROM #CommandQueue
WHERE ID = @id

SELECT @id = MIN(ID)
FROM #CommandQueue
END



Alternative:
You can set the Recovery Model in the each database, just follow the step below.
  • Right Click on the database name
  • Click on Properties
  • Click on Options
  • In the right panel, change the “Recovery Model” to Simple


References of this article were from:

No comments:

Post a Comment