SQL Differential Backup

The crucial difference between SQL differential backup and a full backup is that differential backup includes all data that have been changed since the last full backup despite the fact that they have been included in the previous differential backup.  It means that SQL differential backup is not incremental but cumulative. Look at the following picture to understand what data is stored in a differential backup: SQL Differential Backup

How SQL Differential Backup Works

Each Global Allocation Map interval (GMA interval) covers 64,000 extents (one extent is a collection of eight physically contiguous pages) or almost 4GB portion of each data file has a special database page – differential bitmap. Differential bitmap tracks which extent has changed since the last full backup. Then SQL differential backup reads all bitmaps and stores those data extents that are marked as changed.SQL differential backup

Note that a differential database backup size directly depends on the changes that have been marked in differential bitmaps. In order to prevent a differential backup from being larger than the full backup, it is necessary to make a full database backup regularly. It will reset all bitmaps in the differential bitmap. A full backup zeroizes the SQL differential backup size.

Three Ways to Perform SQL Differential Backup

Let’s consider three ways how to make SQL differential backup

With the help of the T-SQL command:

With the help of the following T-SQL command, you can make SQL differential backup, it’s similar to a command for making full backup but with “WITH DIFFERENTIAL” clause:

BACKUP DATABASE your_database TO DISK = 'diff.bak' WITH DIFFERENTIAL
Use SQL Server Management Studio (SSMS):

Also, you can use SQL Server Management Studio (SSMS) to make SQL differential backups. To do this you need to follow a few simple steps:
a.  Right-click on the database you want to backup

b. Select “Tasks”
c. Click “Back up…”
d. Choose “Differential” type
e. Add backup destination and click “OK”

Use our SQLBackupAndFTP utility:

If you prefer third-party tools, you can use our SQLBackupAndFTP utility to create regular SQL full and SQL differential backup. To start making backups with the help of SQLBackupAndFTP, do this:

a. Download and install the database backup software
b. Open the utility and connect to your DBMS
c. Select the database that you are going to backup
d. Choose the places where backups will be stored
e. Set up a backup schedule

Leave a Reply

Your email address will not be published. Required fields are marked *