Differential backup of Microsoft SQL Server means backing up only the data that has changed since the last full backup. This type of backup requires you to work with less data than a full database backup, while also shortening the time required to complete a backup. In this article, we are going to explain how differential backup works in Microsoft SQL Server and how it can help preserve database data.
Table of Contents
Why You Need Differential Backup in SQL Server
SQL Server database consists of small pieces of data, which are each 64KB in size. These pieces are called the extents. When changes are made to the database, the server marks the affected extents as having been modified.
Differential backup copies all extents marked as modified, so it will contain every single change made since the user started working with the database. Each consequent differential backup will contain all extents since the full backup, including ones from the previous differential backups.
In the long run, differential backup might eventually approach the size of the full backup, which is inefficient. The best way to avoid this is to create full database backups regularly; after running a full backup job successfully, Microsoft SQL Server resets all modification flags for the database, thereby allowing you to start the backup chain over and shorten total backup size.
SQL Server differential backup can save a lot of storage space for a daily backup routine and reduce the time required to complete backups.
How to Create SQL Server Differential Backup Using Built-In Tools
Now, let’s take a look at how to create differential backups using SQL Server’s built-in tools.
Here’s a SQL Server script to create a differential backup:
BACKUP DATABASE TestDB TO diff_backups WITH DIFFERENTIAL; GO
Note that we are now backing up the "TestDB" database to the new backup device "diff_backups."
You can test the script in SQL Query Analyzer, or paste it as a step into the SQL Server Agent job, to make it a regular operation. Remember to start this service via SQL Server Management studio, since it is disabled by default.
Click the New button on the Steps tab, type the name for this step, and paste the desired script in the Command field.
We are going to create a differential backup ('WITH DIFFERENTIAL'') of the database "TestDB" onto the backup device "diff_backups." You can create backup devices as files on the disk using the Server Objects view in the navigation tree.
If you have not created a full backup before, you can add its script prior to adding the differential backup:
BACKUP DATABASE TestDB TO full_backups GO BACKUP DATABASE TestDB TO diff_backups WITH DIFFERENTIAL; GO
SQL Server Differential Backup with CloudBerry Software
Above, we configured a differential backup for SQL Server using built-in scripting tools. If you don’t want to have to work with scripts and perform tedious configurations, another option is to use CloudBerry Backup.
CloudBerry Backup allows you to automate Microsoft SQL Server backup and upload backup data to the cloud storage of your choice or to any local device. It also includes a system image backup (we usually call it image-based) and bare-metal recovery features. For disaster recovery, you can perform server recovery directly to a cloud-based storage service, such as Amazon EC2, Microsoft Azure VM or Google Cloud Engine.
Configure MS SQL Server differential backup in CloudBerry Backup by following these steps:
1Start the backup wizard by clicking the MS SQL Server button on the Home tab:
2On the next step, you select whether you want to perform a Local or Cloud Backup or a mix (Hybrid Backup). In this guide, we will choose the cloud backup destination.
3Choose your specific storage location (Amazon S3 in this example):
If necessary, you can add a new storage account by clicking the corresponding link ‘Add new Storage Account.’
4Provide the name for this backup plan and proceed. At this point, you need to connect CloudBerry Backup to the target SQL instance.
CloudBerry Backup will verify the necessary permissions. If you have created a separate backup account in MS SQL Server, use the Authentication menu to specify its credentials.
5Select the databases you want to protect. We choose "TestDB" in this example.
6On the Compression and Encryption Options screen, you can change additional processing options, such as data compression and encryption, or set cloud-specific options.Note: If your current SQL Server version/edition supports the compression feature, CloudBerry Backup will use the native SQL Server compression during a backup's processing. Otherwise, CloudBerry Backup will use a custom compression algorithm.
7Specify the Retention Policy for your backup to save only the necessary number of copies. This helps to prevent you from running out of space or paying more than necessary when using cloud storage.
You can set the expiration time for any backup copy and store the least amount of full copies.
8The scheduling step allows you to create a “run once” job or configure one of the available schedule templates. In most cases, we suggest using a Recurring (predefined templates) schedule that already includes a set of full, differential, and log backups, which you can modify depending on your needs.
If that schedule is not what you are looking for, check Recurring (advanced schedule) and create your own template. It is also a good idea to check the Run missed… option, which will perform missed backup jobs of your server that were not available at the time of a backup run.
9Specify recurrence details from a set of templates. In the example below, we configure weekly full backups, daily differential backups and transaction log backups every hour.
10Set the rest of the advanced options, such as pre/post actions and notifications settings, and complete the backup plan creation.
Differential backup is a must-have part of any SQL Server database data protection plan. It allows you to simplify the data restoration process and keeps backups smaller than generic full backups.
All of the tools needed to perform a differential backup are built into SQL Server, but they require you to write scripts and perform manual configuration. If you would like to simplify your backup configuration, as well as use cloud or hybrid backup storage, consider using CloudBerry Backup for MS SQL Server and tell us about your experience in the comments below.
CloudBerry offers an easy-to-use and reliable solution for SQL Server backup to the cloud or local storage.
Compression and Encryption
Compression allows you to reduce storage space (and thus save money) while improving backup time. With AES-256 encryption, you can be sure that all your backup files are protected.
Cloud and Local
CloudBerry Backup allows you to store your backups on local storage and any of more than 20 cloud storage providers, including Amazon S3 and Amazon Glacier, BackBlaze B2, Wasabi Hot Storage and Microsoft Azure.
Flexible Retention and Recovery
Store as many versions as you need for as long as you need with flexible retention settings. Recover the latest version or restore to the point in time of your choosing.
Emergency Recovery for Windows Server