One of the types of backup in MSSQL is the “differential” backup. This article will cover what differential (diff) backups are, how to create and restore them, as well as their specific features and best practices.

What is differential backup in SQL Server?

Differential backups are backups that do not contain all the data but only the changes made since the last full database backup. When you perform an INSERT or UPDATE operation on a record in a table, it does not change the entire database file but only a small portion of it. It is precisely these small changes that are captured in a differential backup.

How does it work?

In simple terms, a database internally operates not with bytes and bits but with “pages,” which are typically 8 KB in size by default. When you modify a row in the database, the data is written to one or several pages.

When you create a full backup, all the pages from the database file are copied to the backup file. During this process, each page is marked with a special flag indicating that it has been backed up.

When a page is modified by operations such as INSERT , UPDATE or DELETE, this flag is reset.

So, when you create a differential backup, it only copies the pages where this flag has been reset, i.e., the pages that have changed since the last full backup.

Neat and straightforward! 😊

How to make a differential backup in SQL Server

Before creating a differential backup, make sure you know the location of your latest full backup. If there isn’t one, create it first:

BACKUP DATABASE [db_name] TO DISK = 'path-to-full-backup' WITH INIT;

The differential backup command is almost identical to the full backup command, except that you need to include WITH DIFFERENTIAL.

BACKUP DATABASE [db_name] TO DISK = 'path-to-diff-backup' WITH DIFFERENTIAL;

This script will create a differential backup named db_name. However, make sure not to lose the full backup, as restoring a differential backup is not possible without it.

Differential backup via SQL Server Management Studio

To create a differential backup using SQL Server Management Studio, simply select differential as the Backup Type in the backup settings.

However, you still need the full backup to restore from this file, so make sure you have it.

How to make differential backups in SQLBackupAndFTP

1. Enable the “schedule backups” option, then click on the gear icon to open the advanced schedule settings:

2. In the opened window, you can configure the following options:

• Backup types and intervals: select the differential backups and set the corresponding time intervals. Alternatively, just click on the Load a typical backup plan… link and select from one of the five predefined backup plans to streamline your configuration.
• “Run between” option: specify a time range for the backup jobs.
• Select specific days of the week or ordinal days of the month when the backup job should be executed.
• Define the date and time for the first backup. Ensure that the first backup falls within the specified “run between” time range.

3. In the “Estimate backup plan” section, you can view the schedule for when the backups will be executed.

How to restore full and differential backup in SQL Server

By query

To restore a differential backup, you first need to restore the full backup using the WITH NORECOVERY option:

RESTORE DATABASE [db_name] FROM DISK = 'path-to-full-backup' WITH NORECOVERY;

RESTORE DATABASE [db_name] FROM DISK = 'path-to-diff-backup' WITH RECOVERY;

By SQL Server Management Studio

Restoring a differential backup in SQL Server Management Studio is similar to restoring a full backup, with one difference — you need to select both files. Here’s how to do it:

Click on the three dots and locate your full and differential backup files.

Select both files using the Shift key.

Make sure the recovery date is correct, and if needed, check the box for “close existing connection to destination database.”

How to Restore a Differential Backup in SQLBackupAndFTP

Backups can be restored using either the backup job or the restore job.

Restore from your backup job:

• Go to the “History & Restore” section on the right side of the app
• Choose the backup that you want to restore and click on the three dots
• Select Restore from Backup…

• In the opened window, click on the Restore… button. In the next window, specify the name of the database to which the backup will be restored. If the backup is encrypted, please enter the password in the designated box. Once all settings have been configured, click the Restore button to proceed.

That’s it! Once the backup is successfully restored, you can close the dialog box.

There are two main advantages of differential backups:

1. Smaller backup size: the backup only contains changes made since the last full backup, reducing the amount of stored data.
2. Faster to create: since fewer data pages are being backed up, differential backups are created more quickly compared to full backups.

What are the disadvantages of differential backups?

1. A full backup is required for restore.
2. Restore will always take longer compared to an only full backup.
3. Periodic full backups are necessary; otherwise, differential backups will become too large.

Are differential backups always applicable?

The benefits of differential backups are based on the fact that they only copy what has changed. However, if there is a high rate of change across different tables in the database, then the differential backup will also become large.

For example, if your database stores only operational data for a warehouse — such as daily updates on stock levels and prices — a differential backup will quickly grow in size because almost all the data changes every day.

How often should you perform a full backup to prevent differential backups from growing too large?

Every new record and every row update will increase the size of the differential backup. Over time, the size of the differential backup will approach that of a full backup. Therefore, it doesn’t make sense to perform a single full backup and then rely only on differential backups indefinitely.

To reduce the size of differential backups, you need to periodically perform a full backup.

It’s impossible to give a precise recommendation for a schedule, as it depends heavily on the specific production environment and backup requirements, but common scenarios include:

FULL on the first day of each month
Diff every day 24h

FULL every weak 168 h
Diff every day 24h

FULL every 24h
Diff every 3h

Difference between differential and transaction log backup

A transaction log backup is also a type of incremental backup for SQL Server, but it operates on a different principle. SQL Server maintains a transaction log file that records all transactions. When you perform a transaction log backup, it copies the contents of this log file to a backup file and then truncates the log to free up space.

From an administrative perspective, a transaction log backup includes only the changes made since the last transaction log backup (unlike differential backups, which include changes since the last full backup).

Advantages of differential backups over transaction log backups:

1. You only need two backups to restore your data — full and differential — rather than managing potentially hundreds of transaction log backups.
2. You don’t need to manage the transaction log file, which can grow over time and potentially impact database performance.
3. Restore from a full and differential backup combination is generally faster than applying many transaction log backups.

Advantages of transaction log backups:

1. The size of transaction log backups remains relatively stable over time and is proportional to the amount of data that has been changed.
2. The smaller size allows for more frequent transaction log backups, providing more granular data protection.
3. Full support for point-in-time recovery. By using the STOPAT= parameter, you can restore your data to a specific point in time, down to the second, which is not possible with differential backups.

Can differential and transaction log backups be combined?

Yes, and doing so can significantly reduce both the size of backups and the recovery time.

For example, if you use the following backup strategy:

FULL -> T-LOG-1 -> ... -> T-LOG-67 -> DIFF-1 -> T-LOG-68 -> T-LOG-69

So, to restore T-LOG-4, you would use the following chain: FULL + DIFF-1 + T-LOG-69 + T-LOG-68. For the restoration process, you don’t need the transaction log backups that were made between the last differential backup and the full backup.

However, managing such backup chains manually can be complex, both during the restoration process and when deleting old backups. Paid utilities like SqlBak or SQLBackupAndFTP can assist with this.

Common combinations of full, differential, and transaction log backups include:

FULL – every 24 hours
Differential – every 3 hours
Transaction Log – every 15 minutes

FULL – every 24 hours
Differential – every 6 hours
Transaction Log – every 1 hour

FULL – every 168 hours
Differential – every 24 hours
Transaction Log – every 3 hours

What is the extension of differential backups?

Differential backups have the .bak extension, just like full backups. This is because, similar to full backups, differential backups are also a page-level copy of the database, but they only copy pages that have changed since the last full backup.

No, it’s not possible. A differential backup can only be restored in conjunction with a full backup.

The only option available is to restore the full backup and leave the database in read-only mode, which can be done using the WITH STANDBY option in the full backup restore command.

How differential backups and copy-only work together

Creating differential backups with the copy-only option is not possible because it doesn’t make sense — differential backups are independent of other backups.

However, be cautious with full backups. If you create a full backup using the copy-only option and then create a differential backup, you can restore using the full-copy and differential combination. However, the size of the differential backup will be calculated based on the last not copy-only full backup, instead of a copy-only one.