Other Guides

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.

FREE WHITEPAPER
MS SQL Server Backup and Recovery
Check out the ultimate guide with best practices for MSPs:
New call-to-action
Exchange WP icon

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.

  New call-to-action

Create a new job in SQL Server Agent

Click the New button on the Steps tab, type the name for this step, and paste the desired script in the Command field.

Pasting the script for SQL Server differential backup as a job step

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:

  1. Start the backup wizard by clicking the MS SQL Server button on the Home tab:
    Click MS SQL Server button on the Home tab of CloudBerry Backup
  2. 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.
    Select the backup route for SQL Server differential backup
  3. Choose your specific storage location (Amazon S3 in this example):Back up SQL Server to S3: Selecting storage in MSP360 Backup
    If necessary, you can add a new storage account by clicking the corresponding link ‘Add new Storage Account.’
  4. 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 to SQL instanceMSP360 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.
  5. Select the databases you want to protect. We choose "TestDB" in this example.
    TestDB
  6. On the Compression and Encryption Options screen, you can change additional processing options, such as data compression and encryption, or set cloud-specific options.Compression and EncryptionNote: 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.
  7. 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.
    Retention policy
    You can set the expiration time for any backup copy and store the least amount of full copies.
  8. 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.
    Choose schedule for your SQL Server backup
    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.
  9. 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.Configuring daily SQL Server differential backup
  10. 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 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 localCloud 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 recoveryFlexible 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.

Bare-metal recovery from USB or ISO fileEmergency 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.

 

MSP360 Managed Backup.
Simple. Reliable.
Powerful cross-platform backup and disaster recovery that leverages the public cloud to enable a comprehensive data protection strategy.
New call-to-action
MBS CTA image