Technology Toolbox

Your technology Sherpa for the Microsoft platform

Jeremy Jameson - Founder and Principal

Search

Search

Using the Simple Recovery Model for SharePoint Development Environments

Note
This post originally appeared on my MSDN blog:

Since I no longer work for Microsoft, I have copied it here in case that blog ever goes away.

A little more than three years ago, I blogged about the default recovery model for various SharePoint databases. In that post, I described how I would often toggle the SQL Server databases in SharePoint development environments from the default Full recovery model to Simple before migrating content.

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. This alleviates the need to periodically backup your transaction logs and also allows you to use a very small VHD for the database log files.

Here's a short SQL script that changes all user databases and the out-of-the-box model database to use the Simple recovery model:

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

Note that by changing the model database, any new databases created in the development environment (such as content databases created for new Web applications) will be configured to use the Simple recovery model by default.

Comments

No comments posted yet.

Add Comment

Optional, but recommended (especially if you have a Gravatar). Note that your email address will not appear with your comment.
If URL is specified, it will be included as a link with your name.

To prevent spam from being submitted, please select the following fruit: Strawberry

Pear
Watermelon
Grapes
Strawberry
Cherries
Apple
 
Please add 3 and 3 and type the answer here: