Differential Database Backups for SQL Server

By: Jeremy Kadlec   |   Updated: 2007-05-02   |   Comments (8)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Backup

Problem

I know the 'typical' backups are full database backups executed every day.  I have looked into transaction log backups and I am not sure if that is something I can support.  I have seen some information on differential backups, but I am not sure exactly how they work, how they are different and how they could help me?  Could you please explain some of the considerations with differential backups to determine if they would be beneficial to me and my situation?

Solution

Depending on the frequency of data changes in your SQL Server database(s) and the amount of time\data that your business is willing to lose, should dictate your overall backup strategy.  If a days worth of data is acceptable to lose, then using the full backup strategy could be a viable one, if not, then you need to start looking into differential and transaction log backups.  Regardless of the situation, make sure you understand the business needs and make sure your backup and recovery solution will meet the needs.  Unfortunately, Murphy's law will kick into place one of these days and having the right solution in place can make all of the difference in the world for you and your organization.

Should I consider differential backups in my overall backup strategy?

As far as the differential backups are concerned, they are a viable backup option and may or may not be helpful in a number of situations (see the advantages and disadvantages section below).  Understanding how the differential backups work and how the recovery process would work is vital when you need to restore your databases in short order and get your SQL Server back up and running. 

How do the differential backups work from a backup perspective?

• The catalyst in the differential backup process is issuing a full database backup. 
• Then the differential backups can be issued at a regular interval depending on your needs. 
  • For example, every 2 hours from 7:00 AM to 7:00 PM or at 7:00 AM, 12:00 PM and 7:00 PM.  The schedule is up to you, but keep this in mind during the restoration process outlined in the next section.
• If data changes on any one of the pages in an extent, a flag is set at the extent level to indicate that the extent must be backed up.

What is sample syntax for a differential database backup?

SQL Server 2005

BACKUP DATABASE AdventureWorks TO DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Full.bak'
GO 
BACKUP DATABASE AdventureWorks TO DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Diff_1.bak' WITH DIFFERENTIAL
GO
BACKUP DATABASE AdventureWorks TO DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Diff_2.bak' WITH DIFFERENTIAL
GO

SQL Server 2000

BACKUP DATABASE Northwind TO DISK = 'C:\Temp\DatabaseBackups\Northwind_Full.bak'
GO 
BACKUP DATABASE Northwind TO DISK = 'C:\Temp\DatabaseBackups\Northwind_Diff_1.bak' WITH DIFFERENTIAL
GO
BACKUP DATABASE Northwind TO DISK = 'C:\Temp\DatabaseBackups\Northwind_Diff_2.bak' WITH DIFFERENTIAL
GO

How do the differential backups work from a restore perspective?

Depending on your backup schedule and the time when the failure occurred would dictate the detailed steps that would need to be taken.  If you need to have your current SQL Server databases back up and running, then the full backup would be restored followed by the last differential and then transaction logs if you are issuing them. 

If you are trying to keep 2 SQL Servers in sync via differential backups, then at a high level, the full backup would need to be restored followed by the differential backups.

• To take this down a notch, the full backup would need to be restore using the WITH NORECOVERY option.
• The differential backups would also need to be restored using the WITH NORECOVERY option with the exception of the last differential backup (if no transaction log backups need to be restored) which would use the WITH RECOVERY option.
• Example code is shown in the section below.

What is the sample syntax for a differential database restore?

SQL Server 2005

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Full.bak' WITH NORECOVERY
GO 
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Diff_1.bak' WITH NORECOVERY
GO
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Diff_2.bak' WITH RECOVERY
GO

SQL Server 2000

RESTORE DATABASE Northwind FROM DISK = 'C:\Temp\DatabaseBackups\Northwind_Full.bak' WITH NORECOVERY
GO 
RESTORE DATABASE Northwind FROM DISK = 'C:\Temp\DatabaseBackups\Northwind_Diff_1.bak' WITH NORECOVERY
GO
RESTORE DATABASE Northwind FROM DISK = 'C:\Temp\DatabaseBackups\Northwind_Diff_2.bak' WITH RECOVERY
GO

What are the advantages and disadvantages for using differential database backups and restores?

• Advantages
  • May need less time for the backup if a small percentage of extents are marked to be backed up.
  • May use less storage for the backups if a small percentage of extents are marked to be backed up.
  • May be able to keep more backups on disk as compared to full backups because the differential backups are much smaller.
  • May use less bandwidth when moving backup files around the network because the files are smaller as compared to the full backups.
• Disadvantages
  • Depending on the time interval between the last differential backup and the failure, data loss may occur.  If the amount of data loss is unacceptable, then transaction log backups are needed.
    • Point in time recovery
  • The restore time may not be any faster if the most recent full backup and the last differential backup must be restored either from local disk or tape as compared to issuing full backups on a daily basis and restoring the last full backup.
  • If data is changed across a high percentage of extents, the differential backups may be as large as the full backups.  This means that the time and disk savings for the backups is minimal and the restore time is still as long as the full restore.
  • May need more storage from a recovery perspective because the last full backup and the differential backup would be needed on disk for the fastest possible restore with the native tools.
  • The backups on disk or tape are not encrypted with the native tools.
  • May not be able to use the differential backups as a portion of an automated backup and recovery process to update development, test, pre-production or training environments.
    • COPY_ONLY Backups with SQL Server 2005
  • A third party solution that compresses and encrypts the backups may be the best source for storage savings for your backups and time savings for your restore process.
    • SQL Server 2005 Backup Product Options

Next Steps

• Determine if differential backups would be time and disk savers based on the data changes in your environment.
• Determine if the frequency of the differential backups would be acceptable or if moving to transaction log backups is needed.
• Determine if the need to restore a number of backups would be too time consuming and leveraging a third party product would be a better solution.
• Conduct some testing in your environment and see if differential backups would be beneficial for particular databases by understanding how the data changes then determine the potential time and disk savings.
• Check out the MSSQLTips.com Backup and Recovery tips.
• Stay tuned for tips covering the new partial differential backups and file differential backups.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips