Backup and DR
Microsoft SQL Server Differential Backup
SQL Server differential backup 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.
Why You Need SQL Server Differential Backup
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 SQL Server differential backups using 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 MSP360 Software
Above, we configured an SQL Server differential backup 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 MSP360 Backup.
MSP360 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.
You need MSP360 Backup for MS SQL Server or MSP360 Backup Ultimate editions to be able to perform SQL Server differential backup.
Configure MS SQL Server differential backup in MSP360 Backup by following these steps:
- Start the backup wizard by clicking the MS SQL Server button on the Home tab:
- In 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.
- Choose 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.’ - Provide the name for this backup plan and proceed. At this point, you need to connect MSP360 Backup to the target SQL instance.
MSP360 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. - Select the databases you want to protect. We choose "TestDB" in this example.
- On 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, MSP360 Backup will use the native SQL Server compression during a backup's processing. Otherwise, MSP360 Backup will use a custom compression algorithm. - Specify 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. - The 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. - Specify 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.
- Set the rest of the advanced options, such as pre/post actions and notifications settings, and complete the backup plan creation.
Summary
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 MSP360 Backup for MS SQL Server and tell us about your experience in the comments below.
Featured Product
MSP360 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
MSP360 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
Protect and restore entire servers using MSP360’s image backup and bare-metal recovery features in case of a system or hardware crash.
Simple. Reliable.
