How to check the Database Recovery Model

A recovery model is used to determine what method is used to backup a database. They control transaction log maintenance, disk space capacity and can affect server performance. SQL Server has three types of recovery models Simple, Bulk-Logged and Full.

Simple recovery allows recovery to the last full backup. This method is to be used when your daily transactions do not need to recovered and your data is not critical.

Bulk-Logged provides partial protection against daily transactional data. No bulk-load operations are logged.

Full recovery model allows full point in time recovery of data and complete protection against loss of data. This is the model I recommend for use.

Further details about recovery models at http://technet.microsoft.com/en-us/library/ms189275.aspx

To check the current recovery of the databases on a server you can run either of these scripts

--Script 1

USE Master

SELECT name,

DATABASEPROPERTYEX(name, 'Recovery')

FROM master.dbo.sysdatabases

ORDER BY 1

-- Script 2

USE Master

SELECT Name, Recovery_Model_Desc

FROM Sys.Databases