Nov 25, 2009

How to Change The Recovery Model On All Databases at Once in SQL 2005 Management Studio

In Microsoft SQL there are three ways to set your recovery model. Each method has their own pluses and minus. Each of them have their own reason for using them.

The three recovery models are Full, Bulk-Logged and Simple. The first one, Full, is set by default in SQL 2005. According to SQL-Recovery.com, Full recovery model is:

This is your best guarantee for full data recovery. The SQL Server fully logs all operations, so every row inserted through a bulk copy program (bcp) or BULK INSERT operation is written in its entirety to the transaction log. When data files are lost because of media failure the transaction log can be backed up.

Bulk Logged:

This model allows for recovery in case of media failure and gives you the best performance using the least log space for certain bulk operations, including BULK INSERT, bcp, CREATE INDEX, WRITETEXT, and UPDATETEXT.

Simple:

It allows for the fastest bulk operations and the simplest backup-and-restore strategy. Under this model, SQL Server truncates the transaction log at regular intervals, removing committed transactions. Only full database backups and differential backups are allowed.

Changing the recovery model is as simple as right clicking on the database in SQL 2005 management Studio, Clicking on Properties, Clicking on Options, and selecting your recovery model from the drop down menu. This can be tedious though if you have multiple databases. Some web servers have 50 plus databases. Do you want to manually change each one? I didn’t think so!

Below is a simple T-SQL script you can run to change all of your databases to the the recovery model of choice!

USE master

GO

-- Declare a variable to store the value [database name] returned by FETCH.

DECLARE @dbname sysname, @cmd varchar(1000)

-- Declare a cursor to iterate through the list of databases

DECLARE db_recovery_cursor CURSOR FOR

SELECT name from sysdatabases

-- Open the cursor

OPEN db_recovery_cursor

-- Perform the first fetch and store the value in a variable.

FETCH NEXT FROM db_recovery_cursor INTO @dbname

-- loop through cursor until no more records fetched

WHILE @@FETCH_STATUS = 0

BEGIN

IF (SELECT DATABASEPROPERTYEX(@dbname,'RECOVERY')) <> '<RECOVERYMODEL>' and @dbName <> 'tempdb' BEGIN

-- create the alter database command for each database

SET @cmd = 'ALTER DATABASE "' + @dbname + '" SET RECOVERY <RECOVERYMODEL>'

-- alter each dataabase setting the recovery model to <RECOVERYMODEL>

EXEC(@cmd)

PRINT @dbname

end

FETCH NEXT FROM db_recovery_cursor INTO @dbname

END

-- close the cursor and deallocate memory used by cursor

CLOSE db_recovery_cursor

DEALLOCATE db_recovery_cursor

NOTE: Replace <RECOVERYMODEL> with your model of choice (I.E. SIMPLE, BULK-LOGGED, FULL)

What recovery model do you use on your servers in your environment? Why? Hit us up in the comments!



Twitter Delicious Facebook Digg Stumbleupon Favorites More

 
Design by Free WordPress Themes | Bloggerized by Lasantha - Premium Blogger Themes | stopping spam