DIFFERENTIAL Database Backups in SQL Server Step by Step Tutorial with Examples

What is a Differential Backup SQL Server?

Differential Backup SQL Server will only record the data which has changes since the last successful full database backup.

Differential Backup SQL Server | Differential Database Backup in SQL Server

Differential backup (Differential database backup) will only record the data which has changes since the last successful full database backup. This backed up data is known as differential base. When compared to full database backups the differential database backup are quick to complete and will also be smaller in size as this has only the changes thereby reducing the work loss exposure. Differential Backup in SQL Server or (Differential Database Backup in SQL Server) are very useful especially when subset of database is modified most often than the rest of the database.

This is Part 9 of 16 Part SQL Server Database Backup Tutorial. Click here to read it from the beginning….

Internally when you run a differential backup in SQL Server it will backup all extents which has changes since the time last full backup was run. SQL Server stored data in 8KB page and an extent comprises of 8 of such 8 KB pages equaling to 64KB of data. Whenever any data changes within a 8KB Page a flag is turned on to let SQL Server know that if at all a differential backup is initiated then it should include the data from this extent within the backup. Once a FULL backup is completed the flags are turned off. Therefore, every time you take a differential backup it will backup all the extends which has changed after the last successful FULL backup.

In FULL or BULK-LOGGED recovery model you can use DIFFERENTIAL backups along with TRANSACTION LOG backup

When your database is in FULL or BULK-LOGGED recovery model you can use DIFFERENTIAL backups along with TRANSACTON LOG backup as every differential backup will have extends which have changed there by eliminating the number of transaction logs that needs to be restored to recover the database. To restore a database then you first need to restore the latest FULL backup with NORECOVERY, followed by restoring the latest DIFFERENTIAL Backup with NORECOVERY, followed by restoring all the transaction log backup files which were created since the last differential backup; thereby reducing the time to recover the database.

Best Practices for Scheduling Differential Database Backups in SQL Server

For very large databases, a full database backup will always require significant amount of disk space and time to complete the backup. Hence, you can save time and disk space by having a full backup to start at the beginning of a week followed by a series of differential database backups at regular intervals throughout the week.

Recommendations when you use differential database backups

• After a successful full database backup, schedule differential database backups periodically. For example, you might take a differential database backup every two/four hours or, for highly active systems, or even more frequently.
• Take full database backups at intervals that makes sure that your differential backups do not become too large. For example, you might back up the full database one time a week every Sunday followed by differential database backups every two/four hours depending upon how active is your system.

Create a DIFFFERENTIAL Database Backups Using TSQL commands

BACKUP DATABASE MyTechMantra
TO DISK = 'C:\DBBackups\MyTechMantra.DIF'
WITH DIFFERENTIAL, NOFORMAT, NOINIT,
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 25

/* Verify the Database Backup File Once the Backup has completed successfully */

RESTORE VERIFYONLY
FROM DISK = 'C:\DBBackups\MyTechMantra.DIF'
GO

Create a Differential Backup Using SQL Server Management Studio

• Open SQL Server Management Studio, connect to the appropriate SQL Server Database Instance, in Object Explorer, click the SERVER NAME to expand the server tree
• Expand Databases node and then right click the database which you want to take a differential backup
• Select Tasks, and then click Back Up… option as shown to open up Back Up Database dialog box
• Select Backup Type as Differential and Select Destination as Disk
• Click on Add button to add a backup file and specify the backup file name and click OK to save the changes
• Finally to take Differential database backup click OK

Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.

Related Articles

• SQL Server WHERE Clause T-SQL Tutorial with Examples
• SQL Server ORDER BY Clause T-SQL Tutorial with Examples
• SQL Server SELECT TOP Clause T-SQL Tutorial with Examples
• SQL SELECT | SQL QUERY | SQL SELECT Statement T-SQL Tutorial with Examples
• SERVERPROPERTY Function T-SQL Enhancement in SQL Server 2016
• FORMATMESSAGE Statement T-SQL Enhancement in SQL Server 2016
• STRING_SPLIT Function and STRING_ESCAPE Function in SQL Server 2016
• COMPRESS and DECOMPRESS T-SQL Enhancement in SQL Server 2016
• ALTER DATABASE SET AUTOGROW_ALL_FILES T-SQL Enhancement in SQL Server 2016
• ALTER DATABASE SET AUTOGROW_SINGLE_FILE T-SQL Enhancement in SQL Server 2016
• ALTER TABLE WITH (ONLINE=ON | OFF) T-SQL Enhancement in SQL Server 2016
• DROP IF EXISTS SQL Server T-SQL Enhancement in SQL Server 2016
• TRUNCATE TABLE WITH PARTITIONS T-SQL Enhancement in SQL Server 2016
• T-SQL Enhancements in SQL Server 2016 for Developers and DBAs
• TAIL-LOG Backup in SQL Server Step by Step Tutorial with Examples
• PARTIAL Backup in SQL Server Step by Step Tutorial with Examples