Backup and Recovery in SQL Server: Understanding the Basics (Part 2)

This blog is the continuation of the 2-part series to explain recovery models in SQL Server.

Each of these aims at different requirements to provide partial or complete data recovery. The Database Administrator selects the recovery model depending on the resource and data requirements of the organization. The goal of the recovery model is to balance the logging overhead with data recovery criticality.

Types of Recovery Models for SQL Server

Given below are the recovery models you can use in your backup and restore strategy:

Simple: This model does not support transaction log backups. SQL Server directly truncates the checkpoint operations log when using this recovery model. This frees up transaction log space to store additional transactions.

Although the Simple Recovery model is the simplest with regards to t-log backup management, it makes the user unable to carry out point-in-time database restores. This can lead to devastating data losses when your data changes on a frequent basis and your backups (full or differential) aren’t run regularly.

In other words, the frequency of your backups will determine the amount of data loss you are likely to experience if you have to restore your database using the Simple Recovery model.

Full: The use of this model will ensure the t-log file holds all your transactions until you run a t-log backup. No automatic truncation will take place here, unlike in the Simple Recovery model. Moreover, the Full Recovery model enables users to restore their database to any point in time as long as the transaction log backup has it. This minimizes data loss but is more likely to affect oracle database performance.

It is important to remember when using this model that the t-log will keep saving information as you make changes to the database. Therefore, you’ll have to carry out transaction log backups on a frequent basis to keep them from getting too large. Creating a t-log backup clears the previously-stored data, making space available to store new transactions.

The amount of disk space the transaction log takes does not change, nor should the user expect it to do so. That said, while you will have to pre-size the transaction log on the basis of expected activity, you can set its size to auto-grow in case it uses up all of its space. However, try to refrain from shrinking these files unless you don’t have a choice. These files are typically shrunk with the help of T-SQL commands in SQL Server.

Bulk-Logged: It shares a lot of similarities with the Full Recovery model, except for its minimal logging feature. In it, certain bulk operations aren’t fully logged in the transaction log, such as TRUNCATE, BULK import, and SELECT INTO. These operations are called minimally logged operations, thanks to which your t-logs won’t grow as much in size as compared to the Full Recovery model.

On the other hand, this type of operation keeps users from carrying our point-in-time restores. This is a disadvantage for many as it increases the chances of critical data loss. Therefore, experts recommend sticking with the Full Recovery model in cases where you’re unsure whether this model is the perfect choice according to your requirements. Despite its performance-affecting results, you will be able to guarantee data availability. You can still use SQL tuning tools to rectify this issue.

In Conclusion

Using the right recovery model, you can recreate and restore the entire database data in one step. This process overwrites the current database or creates it in case the database no longer exists. The ‘new’ database will be identical to the condition of the database when the backup took place, without the proceeding uncommitted transactions. These are rolled back once database recovery has taken place.