All but the most novice SQL Server DBA understands that creating database backups is an important part of their job. A viable backup can be the deciding factor that allows an organization to continue to function after a disaster strikes. The prevalence of ransomware that threatens to shut down critical systems and the unpredictability of extreme weather events are just two of the potential issues that could require your databases to be rebuilt from backups.
A critical point to consider is that there are multiple types of SQL Server database backups. A combination of the different kinds of backups can work together to provide the best protection for valuable enterprise databases. Each type of backup has its uses and is important on its own. The types of backups you take depend on the way they are intended to be used.
At times a single backup method may be sufficient to recover systems. If you are migrating to a new server, shutting down the database and taking a full backup should be sufficient to get the job done. Effectively recovering from an unexpected outage will require a full backup as well as the supplemental backups that enable the database to be restored to a specific point in time.
The recovery point objective (RPO) is a metric that indicates the amount of data you are prepared to lose when performing a recovery. Mission-critical systems will have a more aggressive RPO than less important databases. Successfully achieving an aggressive SQL Server RPO requires a strategic approach to backing up the systems using a variety of backup options.
Types of Database Backups
Three main backup types are used to protect SQL Server databases.
Full database backups back up the complete database including all objects such as tables, functions, views, and indexes. Part of the transaction log is included in a full backup which enables the simplest method of recovering a database. A full backup enables recovery to the point at which the backup was taken. Full backups form the foundation for all other types of SQL Server backups and are a mandatory component of a viable data protection strategy.
Differential backups record all changes made to a database since its last full backup. They vary in size based on the dynamic nature of a given database. Many changes will result in large backups. Multiple differential backups can be taken between full backups, but they will tend to increase in size over time and may approach that of a full backup. Your recovery time objective (RTO) will be affected by the size of the differential backup required to be restored. Recovery time constraints will often necessitate full backups to be taken frequently. This is to avoid an extended restoration that requires both a full and large differential backup to be used for the recovery.
Transaction log backups do exactly what their name implies. They back up the database transaction log which is a history of all database modifications. Each backup contains all log records not included in the previous transaction log backup. Transaction log backups are incremental and only contain new information. When recovering to a specific point in time, you need to recover a full backup, a recent differential if available, and all necessary transaction logs to get close to the desired recovery objective.
SQL Server Recovery Models
SQL Server can be configured to use one of three different recovery models. The model used has an impact on how the system is recovered and what types of backups are required to perform the recovery. Following are the recovery models that can be used with your SQL Servers.
Simple recovery
The simple recovery model does not use transaction log backups. It relies on full backups and does not support log shipping or point-in-time restores. Any changes made to the system since the last backup are unprotected, as there is only a full backup available for recovery. This information will be lost in recovery and will need to be recreated.
Full recovery
In the full recovery model, transaction log backups are used to recover without losing data. Using this model enables SQL Server databases to be recovered to a specific point-in-time. The full recovery model allows for the highest degree of flexibility when restoring a database.
Bulk logged recovery
This recovery model streamlines full backups by enabling high-performance bulk copy operations. It requires transaction log backups but can only recover to the end of a given backup. Point-in-time recovery is not supported by bulk logged recovery.
Organizations may adopt multiple recovery strategies for subsets of their SQL Server environment. Mission-critical systems that require point-in-time recovery should use the full recovery method. Test instances may rely on the simple method to minimize complexity.
A Dedicated SQL Server Backup Tool
SQL Safe Backup offers database teams a flexible and reliable tool for backing up their SQL Server environment. It allows backups of physical, virtual, and cloud instances to be managed across the environment from a single console. Policy-based management lets you define and automate backup, restore, and log shipping schedules. Multiple user interfaces enable the team to interact with the tool using the most convenient method.
Technologies such as advanced compression and multi-threading result in reduced backup time and increase the window for performing other SQL Server tasks. Compression levels are tuned dynamically to obtain the smallest backup files in the shortest amount of time. The tool provides multiple recovery techniques including an instant recovery that allows systems to be brought up quickly to eliminate downtime. Data is streamed from the backup file to address user demands while the backup completes in the background.
The right combination of a strategic backup plan and SQL Safe Backup will keep the valuable information in your SQL Servers backed up and available for recovery when needed. DBAs can rest easy with the knowledge that enterprise data resources are well-protected and concentrate on other pursuits.