Steps to Create a Differential Backup In SQL Server

Differential backup in SQL Server is a distinctive backing up technique.This backs up only the changes that are made since last full backup. The added data full database backup is called the differential base. This backup technique reduces work pressure, complexity of backup, cost, and saves the resources too.

Table of Content

• Introduction
• Technical Overview
• Benefits
• Steps
• SSMS Solution
• Conclusion

Introduction

Differential backups can be performed on any recovery model, including full, bulk-logged or simple model. Let us discuss about this backup technique in detail, including how to create a differential backup of a database. Before that we will see the benefits of maintaining a differential backup.

Differential Backup In SQL Server: A Technical Overview!

Differential Backups catches only the extents that have been altered for the previous backup. Extent is the collection of eight continuous pages physically. A series of frequent backups have updated extents.

Consider the above given scenario, here 24 data extents are shown out of which only 6 are updated so the backup includes on these 6 extents. Moreover, each extent updated on the backup is indicated on the bitmap as setting 0 to 1. The differential backups are independent of the copy-only backups.

The differential backup increases in size as the backup interval increases, which makes difficulty in restoration process. The restoration of differential backups must be preceded with the restoration of the differential base.

IMPORTANT: It is mandatory to have a full back up of the databases before opting for the deferential database backup strategy.

Benefits Out of Differential Backup In SQL Server

Differential backups have certain gains over the full database backups. Let’s see some of the beneficial aspects of maintaining differential backups.

• Differential backups are easy to create as compared to full backups since it stores only few changes of the database.
• In full recovery model the differential backup helps in reducing the number of logs that you have to store.
• Consumes only less storage space on the hard disk since the backup file area is small compared to the other backup.

Steps to Create a Differential Backup for SQL Server

Different databases offer different methods to create a Differential Backup in SQL Server. Here, we have discussed about the two main modes i.e. Transact-SQL and SQL Server management Studio.

Transact-SQL

It is very simple to create a differential backup in T-SQL. Execute the query BACKUP DATABASE with DIFFERENTIAL clause. The syntax for the query is:

While executing the query we need to specify the database name to be backed up, device where it should be stored, and the clause ‘WITH DIFFERENTIAL’.

Additionally, we can specify other clauses: INIT, SKIP and FORMAT.These clauses serve different purposes.

• INIT is used for overwriting the existing media.
• INIT and SKIP are used together to SKIP the overwriting of existing media that has not been expired.
• FORMAT is used when the media is used for first time to rewrite the existing media header.

Example:

The command creates a full backup of the database MY_DB and then the differential backup in SQL Server.

Create SQL Server Differential Backup Using SSMS

To create a differential backup using SQL server management studio you have to follow certain steps as described below.

1. Connect the appropriate Database Engine to Explorer Object and expand the Server tree.
2. Select any database from system databases.
3. Right click on databases then Tasks>>Back Up
4. From the Back Up database dialog box verify the required databases name.
5. From the backup types select Differential
6. Click Database backup component
7. Either accepts the default name suggested in Name textbox or custom define a different name.
8. You can give a description to the backup in the description box.
9. Net is to set the expiry period for the backup. You can specify the number of days or accept the default expiration period set in the server properties.
10. Chose the backup media: Disk or Tape. The selected path will be listed in the Backup to List Textbox.
11. To have advanced options, go to Options
12. Select the Overwriting Media from choices
    • Back up to the existing media set
    • Back up to a new media set, and erase all existing backup sets
13. For more Reliability Options select
    • Verify backup when finished
    • Perform checksum before writing to media
14. Regardless of default compression done by server, you can check any one of the options to compress or do not compress:Compress Backup or Do not Compress Backup.

NOTE: If you are choosing a tape drive for backup, then Unload the tape after backup option will be active. Click this option to activate the Rewind the tape before unloading process.

Observations

One of the barriers to this database backup technique is that it does not allow any type of transaction to the database backup. Either implicit or explicit transactions are not allowed. Even though it does not allow transactions, it is an effective way to maintain simple backups for large databases. Also, the periodic differential backup in SQL Server are very small in size and easy to handle. Thus this is an efficacious backing up technique.