SQL Server Backup Strategies
Context
SQL Server backup and recovery components provide essential security for the protection of critical data stored in your SQL Server databases. To reduce the risk of catastrophic data loss, you can backup your databases to save the modifications made on a regular basis. A correctly planned backup and restoration strategy allows to protect databases against the loss of data due to various failures. Test your strategy by restoring a set of backups and then recovering your database to prepare for an effective disaster response.
In addition to local storage to store backups, SQL Server also supports backup and recovery from the Azure Blob Storage service. For database files stored using the Microsoft Azure Blob Storage service, SQL Server 2016 offers the possibility to use Azure snapshots for near-instantaneous backups and faster restores. Azure also offers an enterprise-class backup solution for SQL Server instances running on Azure virtual machines.
Summary
Backup Strategies
Data backups must be adapted to a particular environment and must be able to use the available resources. To be effective, backups for recovery purposes must therefore be strategized. A well-designed backup strategy must balance the company's specific needs for maximum availability and minimum data loss, while taking into account the cost of maintaining and storing backups.
Multiple Backup Types
Full Backup
A data backup that contains all the data of a particular database or a set of file groups or files, plus enough of the file log to allow the recovery of that data.
Differential Backup
A data backup based on the latest full backup of either a full or partial database or of a set of data files or file groupes (differential database), and which only contains data that was changed.
Log File Backup
Transaction log backup that includes all log records that were not backed up in a previous log file backup (full recovery mode).
See also
