Database recovery is a very important thing, especially to DBA’s, and the occasional developer who screws something up. Please note, I am a developer and I would NEVER mess ANYTHING up in a database. I treat all databases equally and with respect. Anyway, we are getting off track. There are three recovery models: Simple, Full and Bulk-Logged.
Simple: This method does not do log backups. This causes an issue on systems where if a failure occurs then recent data needs to be recovered. If you are only loading something like a data warehouse once a day then this may be a good option as you don’t have new transactions writing to the database all day. You only care about the data at the single point in time of loading. Any changes in the database since the last backup will be lost.
Full: This method allows you performs log backups. This method will allow a point in time restore of your environment. All transactions in the logs are kept until a transaction log backup is performed. Individual data pages can also be restored under this method. Also note that the only thing that will clear the transaction logs are transaction log backups. Here is a link to Paul Randal’s blog on backup myths.
Bulk-Logged: Logs are backed up here as well. Here if the log gets damaged or a bulk-logged operation happens between the point of last backup and disaster all those operations must be redone. If there are no bulk-logged operations since the last backup then no data needs to be reloaded. Note that point in time recovery is NOT supported with this method.
Knowing the difference between these options is critical. There comes a time when you will need to know what model your databases are using. Luckily there are several ways to do this, the one in particular that I will show is how to get all databases recovery model they are currently set up with.
SELECT
recovery_model_desc AS RecoveryModel
,name AS DatabaseName
FROM sys.databases
ORDER BY DatabaseName
You can also programmatically change the model of a database by using ALTER DATABASE as shown below.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE
The syntax for the three RECOVERY model options: SIMPLE, FULL OR BULK_LOGGED
