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:
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.
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
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
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
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