1:00 AM
Paul B
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!
