What Are The Backup Types In SQL Server

Backup is considered one of the biggest criteria in disaster recovery plans and strategy. Being a DBA, it is very important to take care of the database with backups all in place. A SQL Server database becomes complete with the data files, filegroups, and log files. Further, these files are inclusive of both user and system databases. Understanding the database backups can help us in better planning of disaster recovery strategy.

Let’s check a simple overview of backup types in SQL Server.

1. Full Backup

This is the most common backup type in SQL Server. A full backup pushes all the data and objects in the data files for a given database. The objects are nothing but the tables, views, procedures, functions, triggers, indexes, etc.

While other backups are little toiling to restore, the full backup becomes one of the simplest restoration processes. This is due to a single .bak file that comprises all the data including the log transactions.

2. Differential Backup

This is the second common backup that a DBA thinks of. A differential backup pushes all the data and objects for a given database that are modified since the last full backup. So basically, it means that the data modified after the recent full backup can rationally be smaller in size. However, the size of the differential backup is hypothetical and can vary depending on the number of changes done after the last full backup.

The differential backup file where the data is pushed is the .bak same as the full backup but consists of an additional statement of WITH DIFFERENTIAL in the backup script. The advantage of having a combination of full backup and differential backup strategy can reduce the risk of data loss. Additionally, they can even reduce the size of the backup files. Restoring a differential backup will mandatorily require the last full backup which will be considered a base.

3. Transaction Log Backup

Transaction log backup is one such backup that a DBA understands to restore a backup for a point-in-time recovery. The transaction log backup pushes all the log records into the .LDFfile since the last log backup. This backup type works only when the database is set to “Full” or “Bulk-logged” recovery model.

Once a transaction log backup is created, the .LDF logical file will be ready for reuse. In the production environment, the log backups are taken periodically to ensure point-in-time recovery for databases.

4. Full File Backup

Full file backup pushes all the data and objects to the specified data files or filegroups. This backup type is basically used lesser when compared to the other three backups mentioned above. Full file backup becomes a useful option when your database is big and has a huge data file.

5. Differential File Backup

Differential file backup pushes all the data and objects to the specified data files or filegroups that have changed since the last full file backups. So, if you are considering a differential backup with a huge size, then a differential file backup can be created. However, the differential file backup is considered not relevant as they are mostly related to just one data file.

6. Partial Backup

Partial backup is a backup type that pushes a complete writable portion of the database but excludes any read-only files or filegroups. The option was first introduced in SQL Server 2005. Additionally, there are chances where we may have read-only filegroups in our databases. So, this option of partial backup can help take the backup by excluding all the read-only filegroups in the database.

The backup type works only for full or differential backups but doesn’t work for transaction log backups. While a filegroup is changed from Read-Only to Read-Write, the backup will be taken automatically in the next Partial backup. However, while a filegroup is changed from Read-Write to Read-Only, a new filegroup should be created.

7. Differential Partial Backup

Differential partial backup pushes all the data and objects that have changed since the last partial backup. The feature was added in SQL Server 2005 and was designed for databases where the filegroups are broken up. A restore of the differential partial backup requires the last full partial backup. Like the full and differential backup scenario, the differential partial backup is smaller in size when compared to the partial backup. Due to which, the differential partial backups are always faster.

8. COPY ONLY Backup

A COPY ONLY backup is a backup type that works for either full or differential backup. The backup type with COPY ONLY option is designed to avoid any disruption to backup sequence for the respective database. Additionally, the backup option was introduced in SQL Server 2005. Therefore, the hassle of backup sequence disruption can be avoided using the COPY ONLY backup option. This scenario can be helpful when there are multiple Adhoc backups happening on a given database and the actual sequence is being interrupted.