SQL Server – types of backups

by
types of backupsSQL Server offers multiple options when it comes to creating database backups. 
There are multiple types of backups for your databases, which will serve you in different situations. 
In this article, we will try to show you what are the basic types of backups, when and why to use them. 

Although these types of backups are general for multiple database systems, we will present them in the context of SQL Server.

 Types of backups

Backups of your SQL Server database don’t include just the data, they are multiple types of backups that you can do. They are split into three categories, based on what backup they perform.

Database backup:

Database backups are all about creating a copy of the data and the objects found in your database. There are two subcategories in this situation, for different situations. These subcategories are:

This is the most extensive method of backing up your data and involves creating a copy of your entire data and objects.

      • FULL database backup: This type of backup is the most extensive type of backup and this involves all of the data and all of the objects in your database being copied in your .bak (backup) file.
      • Differential database backup: Differential database backups are made after a FULL backup has been created. The data and objects contained inside a differential backup consist only of the new data and objects created since the last FULL database backup.
Transaction log backup:

This type of backup records all changes to a log file (.ldf file), changes that occurred since the last log file backup, or in case it’s the first time you are backing up the log it will contain all of the changes since the last FULL backup.

Similar to the differential backup, the transaction log backup works the same way, but the information is stored in log files.

Every record in the log file has its own LOG Sequence Number (LSN), which is an incremental and unique value.

This number is set up as a baseline by the system at every log backup. The next time you do a backup of the transaction log, the first LSN used will be the last number available, which will be set as a baseline for that operation.

File backups:

This is the last major category in our list with types of backups and it has 3 subcategories.

In the case a database increases its size to Terabytes, doing a FULL backup will have a major impact on performance. For this situation, SQL Server allows us to do a backup of the File System. Data can be split into data files among different files and filegroups, and the process of backing up can be split and reduced to smaller tasks, which will ensure that the performance will not be compromised.

      1. FULL file backup: All data and objects of marked files and filegroups are backed up.
      2. Differential file backup: Similar to the other differential strategies, this process backs up the data and objects of the files and filegroups since the last full file backup.
      3. Partial Backup: This strategy will back up only the writeable portion of the database, and files and filegroups will not be backed up unless specified.
      4. Differential Partial Backup: All data and objects will be backed up since the last FULL partial backup.

Leave a Comment