DIFFERENTIAL Database Backups in SQL Server Step by Step Tutorial with Examples
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 database backups are very useful especially when subset of database is modified most often than the rest of the database.
Microsoft SQL Server Differential Backup
SQL Server differential backup 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 SQL Server Differential Backup
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.
Internally when you run a differential database backup 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.
A differential backup contains only the data that has been changed since the last full database backup was created. Creating differential backup usually takes less time than a full backup, because you back up only modified data instead of backing up everything.
However, when you create multiple differential backups, each subsequent differential backup contains additional modified data compared to the previous ones, and is, therefore, larger in size. It might eventually approach the size of the full backup, which will lead to longer restore times (since the full plus the differential backup must be restored). To prevent longer backup times and keep differential backups from getting too large, you need to run new full backups at regular intervals.
When your database is in SIMPLE recovery model and when you plan to restore a database then you first need to restore the latest FULL backup with NORECOVERY, followed by restoring the latest DIFFERENTIAL backup with RECOVERY. When a database is in SIMPLE recovery model you cannot achieve POINT IN TIME recovery of the database. If a database restore is needed, you’ll need to restore the full backup and the differential backup that is closest to the time at which the issue occurred (all of the other differential backups can be ignored). This will allow you to restore your data to a more current state than if you had only a full backup of the database that was not created as recently.
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.
Each successive differential database backup will be larger than its predecessor, hence it will require more backup time, disk space, and restore time. Therefore, Microsoft recommend that you take a new FULL database backup at an appropriate intervals to provide a new differential base to reduce the database recovery time and to reduce the data loss.
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.
How to Create SQL Server Differential Backup Using Built-In Tools
Now, let’s take a look at how to create SQL Server differential backups using 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.
Click the New button on the Steps tab, type the name for this step, and paste the desired script in the Command field.
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
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 Database 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
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.