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. 

SQL Server Backup Types

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

Full Backup

A full database backup backs up the whole database. It includes some part of the transactional log so that you could restore your database to the point when the full backup was finished. Usually, files with full database backup have ‘.bak’ extension. It is recommended to periodically create full backups, but since it contains transaction log along with whole database data it takes significant space. To create full backup use the following command:

BACKUP DATABASE Adventureworks TO DISK = 'adventureworks.bak'

For relatively small and rarely changing databases a full backup is often sufficient. Otherwise, you need to look at other backup types.

Differential Backup

If your database is big enough, it may become quite space-consuming to create full backups each time. Here a differential backup comes to the rescue. This kind of database backup is related to the last full backup and contains all changes that have been made since the last full backup. Each next differential backup contains the same data that was stored in the previous one, but none of them contain transactional logs. You need a previous full database backup to restore a differential backup. The file extension of a differential backup is usually ‘.dif’.

You can create a differential backup in the following way:

BACKUP DATABASE Adventureworks TO DISK = 'adventureworks.dif' WITH DIFFERENTIAL

Transaction Log Backup

If it is crucial to restore your database close to the point of a failure or at any other point in time then you need to consider transactional log backups. 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 (or the last full backup). To create a transaction log backup to your database using the following command:

BACKUP LOG Adventureworks TO DISK = 'adventureworks.trn'

SQL Server Backup Options

Copy-Only Backup

Use the COPY_ONLY option if you need to make additional full or transaction log backups which will occur beyond the regular sequence of SQL Server backups. To perform copy-only backup simply add the “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 a simple recovery model to make backups of very large databases that have one or more 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 create a partial backup:

BACKUP DATABASE Adventureworks
READ_WRITE_FILEGROUPS 
TO DISK = 'partial_backup.bak'

Overwrite or append backup sets

Another option is the WITH INIT and WITH NOINIT options. By default, the NOINIT option is enabled. It means that the backup will append to other backups in the file. For example, if you already have a full and a differential backup in full.bak, they will all remain after executing:

BACKUP DATABASE Adventureworks TO DISK = 'c:\backup\full.bak' WITH NOINIT

On the other hand, if you want to overwrite existing backups, the WITH INIT option will erase the previous set of backups:

BACKUP DATABASE Adventureworks TO DISK = 'c:\backup\full.bak' WITH INIT

Set expiration dates for backups

If you want your backup to expire, you can use the WITH EXPIREDATE option. The following example shows how to back up with an expiration date on March 28, 2018:

BACKUP DATABASE Adventureworks TO DISK = 'c:\backup\full.bak' WITH EXPIREDATE = N'03/28/2018 00:00:00'T

Another option is the option to expire after a specified number of days. The following example, shows how to retain a backup for 3 days:

BACKUP DATABASE Adventureworks TO DISK = 'c:\backup\full.bak' WITH RETAINDAYS = 3

Encrypt a database backup

Another interesting option is the backup with encryption, this option will allow encrypting our backup. To do that, we will need to create a master key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '2455678KL95234nl0zBe'

Next, we will need to create a certificate:

CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'my certificate'

Finally, we can backup our database:

BACKUP DATABASE [testdb] TO DISK = 'c:\backup\full.bak'
WITH FORMAT,
ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = MyServerCert
),
STATS = 10
GO

Backup compression

In addition, it is possible to compress your backup using the option WITH COMPRESSION. This option will compress your backup:

BACKUP DATABASE Adventureworks TO DISK = 'c:\backup\full.bak' WITH FORMAT, COMPRESSION

Note that the backup compression is only supported on SQL Server Enterprise and Standard editions (and the Business Intelligence Edition in SQL Server 2012).

SQL Server Backup Tips

If your database is big, you will need to combine full, differential, and transaction log backups. If your database is big and it does not change too much, a differential backup will take less space than a full backup and you will save a lot of space.

Do not store your backup on the same drive as the database. If possible try to store your backup on another Server or even better in another physical place. If your hard drive fails and you have the database and backups, you may not be able to recover your data.

Test your backups to make sure that they are working fine.  There are some nice options that can be useful to verify that the backup is OK, like the verify-only option. The following example will create a full backup and then test if it works using the RESTORE WITH VERIFYONLY option to just verify if the backup is OK:

BACKUP DATABASE Adventureworks TO DISK = 'c:\backup\full.bak'
GO

declare @backupSetId as int

select @backupSetId = position from msdb..backupset 
where database_name=N'adventureworks' and backup_set_id=(select max(backup_set_id) 
from msdb..backupset 
where database_name=N'adventureworks' )

if @backupSetId is null
begin raiserror (N'Verify failed. Backup information for database ''adventureworks'' not found.' , 16, 1) end

RESTORE VERIFYONLY FROM DISK = 'c:\backup\full.bak' WITH FILE = @backupsetid