SQL Server offers several backup methods. These backup types can be combined to create various backup strategies for SQL Server.
Types of Backups in SQL Server
Full Database Backup
This is the classic method of performing a full backup, where all objects (including system objects) are included in the backup. Transactions (changes) that occur during the backup process are also included in the backup.
Differential Backup
A differential backup includes only the changes that have occurred since the last full backup. This is advantageous because you can perform one full backup and then use differential backups, which are faster and require less disk space.
Transaction Log Backups
In this case, only transactions that have occurred since the last transaction log backup are included in the backup. After a backup is made, the transaction log file is cleared, and new transactions are logged until the next transaction log backup.
Backup Strategy
Choosing an appropriate backup strategy depends on specific needs. It is essential to consider what you want to achieve with your strategy and determine how much data loss and recovery time are acceptable in the event of an unfortunate incident.
- RPO (Recovery Point Objective) – This metric, expressed in minutes/hours, determines the amount of data loss (maximum) that is acceptable. For example, you may set an RPO that allows for a maximum data loss of the last 2 hours for an application running on the SQL Server database platform.
- RTO (Recovery Time Objective) – It specifies the maximum time within which you must be able to restore the system from a backup.
Depending on these requirements, you need to select an appropriate database recovery model. If your RPO is set to, for example, 1 hour, you may not necessarily need to set the database to the FULL recovery model, where transaction history is stored in the .ldf file. The SIMPLE recovery model may be sufficient when the transaction log history is not a concern.
It’s important to realize that there is a noticeable difference between the SIMPLE recovery model and FULL recovery model. The difference primarily affects DDL/DML operation performance (ceteris paribus), and there are also significant storage costs (transaction logs are not free). Backups, if we don’t need transaction logs, can look like this:
- FULL Backup once daily
- DIFF Backup every hour (can be done with SIMPLE Recovery model)
If you have a lower RPO, then you will need transaction logs, and backups might look like this:
- FULL Backup once daily
- DIFF Backup every hour
- TRAN Backup every minute
In the second scenario, you will need to switch the Recovery model to FULL, which brings certain disadvantages and higher management overhead for database and disk storage. However, the benefit is the ability to perform restoration to almost any point in the past for which you have a transaction log backup – perform a restore of the FULL backup, followed by DIFF, and then TRAN backups.
Backup should not be underestimated, and it’s good to have some knowledge about backup principles. However, this doesn’t mean you have to struggle with backup on your own. There are many tools available in the market that can help automate backup processes, such as SQLBackupandFTP.
