MSP360 For IT PROs
Articles about cloud backup, cloud storage and more
BLOG MENU
Don’t Miss New Articles!
Thank you for subscribing!
Every month we deliver quality content about cloud storage, backups and security. Leave your email to get weekly and monthly digests

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

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.

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

     

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