MSP360 Guides
Fundamental technology and business guides for MSPs
SQL Server differential backup

Microsoft SQL Server Differential Backup

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.

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.

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

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

You need CloudBerry Backup for MS SQL Server or CloudBerry Backup Ultimate editions to be able to perform differential SQL Server backup.

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:
Click MS SQL Server button on the Home tab of CloudBerry Backup

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.
Select the backup route for SQL Server differential backup

3Choose your specific storage location (Amazon S3 in this example):Back up SQL Server to S3: Selecting storage in CloudBerry Backup
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.
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.

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.Configuring daily SQL Server differential backup

10Set 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 CloudBerry Backup for MS SQL Server and tell us about your experience in the comments below.

Featured Product

CloudBerry 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

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, Microsoft Azure, Amazon Cloud Drive, Microsoft OneDrive, Google Drive.

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 CloudBerry’s image backup and bare-metal recovery features in case of a system or hardware crash.

 

CloudBerry Backup for MS SQL Server
New call-to-action
CloudBerry Backup icon

Related Articles

Ratings:
1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 5.00 out of 5)
Loading...