SQL Server Backup Types

Regular backups are required to protect your database and ensure its restoration in case of failure. Various backup types provide different protection to your database. The most popular database backup types are full backup, differential backup, and transaction log backup. Before you create your own backup plan you need to consider two things: your recovery point objective (RPO) – how much data can you afford to lose? And your recovery time objective (RTO) – in case of disaster, how much time can you spend restoring your database? Now let’s check out some of the backup types.

Backup Types

In this article, we are going to review some backup types, which you can use as a reference information, and we better start with the most important type of backup:

Full Backup

A full database backup is the simplest kind of SQL Server backup that does not depend on the recovery model. It contains all data in a particular database and enough log that is required to recover the database. To execute full backup use the following command:

BACKUP DATABASE Adventureworks TO DISK = 'full.bak' 

Differential Backup

A differential database backup is related to the last full backup and contains all changes that have been made since the last full backup. You can perform a differential backup in the following way:


Transaction Log Backup

This SQL Server backup type is possible only with full or bulk-logged recovery models. A transaction log backup contains all log records that have not been included in the last transaction log backup. To apply a transaction log backup to your database use the following command:

BACKUP LOG Adventureworks TO DISK = 'log.bak'

Copy-Only Backup

Use COPY_ONLY option if you need to make an additional full or transaction log backups which will occur beyond the regular sequence of SQL Server backups. To perform copy-only backup simply add “COPY_ONLY” clause:

BACKUP DATABASE Adventureworks TO DISK = 'full.bak' WITH COPY_ONLY

File and Filegroup Backups

These backup types allow you to backup one or more database files or filegroups. To execute file backup use the following command:

BACKUP DATABASE Adventureworks 
FILE = 'File' 
TO DISK = 'File.bck'

Use this command to perform filegroup backup:

BACKUP DATABASE Adventureworks 
FILEGROUP = 'Group' 
TO DISK = 'Group.bck'

Partial Database Backup

Typically partial backups are used in simple recovery model to make backups of very large databases that have one or mode read-only filegroups. However, SQL Server also allows making partial backups with full or bulk-logged recovery models. Use the following T-SQL command to execute a partial backup:

BACKUP DATABASE Adventureworks
TO DISK = 'partial_backup.bak'

Also, you can find some more usefull information at this article “BACKUP TYPES & STRATEGIES FOR SQL DATABASES“.

[Total: 15    Average: 4.4/5]