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.
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:
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'
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:
BACKUP DATABASE Adventureworks TO DISK = 'diff.bak' WITH DIFFERENTIAL
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'
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
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 READ_WRITE_FILEGROUPS TO DISK = 'partial_backup.bak'
Also, you can find some more usefull information at this article “BACKUP TYPES & STRATEGIES FOR SQL DATABASES“.