As database administrators, we are charged with not only protecting our data but also being able to recover it in case of emergencies. For that reason, knowing the different types of backups that are available to you in SQL Server, how they work, and how to properly manage them is a critical skillset to possess. In this post, we will discuss the following database recovery models and how they function so that you can be prepared for an emergency:
In the Full recovery mode, SQL Server logs every part of every operation. When a transaction occurs, the transaction log keeps this record until a transaction log backup occurs, which then clears the transaction log.
A database in Full recovery mode allows you to do "point-in-time recovery." This model requires the most administration because all operations are logged and the transaction log is not truncated until a transaction log backup is performed.
Be careful not to max out the disk space. Here's the problem: databases are set to Full recovery mode by default (unless you have altered the Model database to default to another model and the new database is created from that template). If the database is left in Full recovery mode and regular transaction log backups are not taken, the log could fill up and grow until disk space runs out.
The only way to clear a transaction log is to perform a transaction log backup. So, when you are using the Full recovery mode, you should take transaction log backups as often as you can afford to. To start taking transaction log backups, you must start what is referred to as the "backup chain" by performing a Full backup of the database. To do this, follow these steps:
After the first full backup is complete and the backup chain has been established, you can perform a transaction log backup. To do this, repeat the steps above. However, this time, click Transaction Log in the Backup type box. Leave everything else the same, and click OK to start the backup.
The next recovery mode is the Bulk-logged recovery mode, which is used more for optimizing bulk operations. It minimally logs bulk operations (yet still fully logs other operations), which is better for performance and requires less log space than the Full recovery mode requires.
Some examples of operations that are minimally logged include index rebuilds, bulk import operations, SELECT INTO operations, and WRITETEXT and UPDATETEXT statements. To see a detailed list of operations that can be minimally logged, read "Operations That Can Be Minimally Logged" on MSDN.
This recovery model is recommended to be used on databases that are already using Full recovery and performing bulk load operations (i.e., data warehouse load). Due to the differences in logging between the Full and Bulk-logged recovery modes, the Bulk-logged recovery mode does not allow for point-in-time recovery. For more detailed information about how backing up databases in the Bulk-logged recovery mode works, see the "Backup Under the Bulk-Logged Recovery Model" topic in SQL Server Books Online on TechNet.
Finally, the Simple recovery mode is the easiest to both back up and restore. In the Simple recovery mode, the logging works the same as in the Bulk-logged recovery mode. But, transaction log backups are not needed because the inactive transactions in the log are automatically truncated after a checkpoint.
A checkpoint occurs in the Simple recovery mode automatically when the number of log records reaches the lesser of these two values:
For more information, see "Checkpoints and the Active Portion of the Log" on MSDN.
There are three recovery modes in SQL Server, and each has its own complexities and advantages. Understanding how each works and how to utilize each helps you ensure your databases and data are safely backed up.