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.
