Differential Database Backups for SQL Server

By:   |   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.
    • 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.
    • 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.
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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec 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


Comments For This Article




Thursday, May 22, 2014 - 11:45:07 AM - Phil Back To Top (30892)

Thanks!  Was able to restore with your code, whereas with Studio Express was not.

Monday, October 14, 2013 - 10:59:05 AM - Shalini Back To Top (27144)
 
I am getting error no 10061 while accessing sql server through my application..I have found that in service control manager sqlserver instance is not running..i had tried to restart it .but it doesn't start and showing some error..

Monday, October 14, 2013 - 10:42:19 AM - Shalini Back To Top (27143)

I got the solution..

first I RESTORE full .bak WITH NORECOVERY
then

RESTORE Diff1.bak  WITH NORECOVERY
and finally

RESTORE Diff2.bak   WITH RECOVERY

using SSMS wizard or script

thankyou sir


Saturday, October 12, 2013 - 4:37:09 PM - Jeremy Kadlec Back To Top (27132)

Shalini,

I am not sure if I understand your question, but I assume you have having an issue restoring with an SSMS wizard.  Can you use scripts instead of an SSMS wizard?  Three are some sample sets of code in this tip to use as a point of reference.

Thank you,
Jeremy Kadlec
Community Co-Leader

Tuesday, October 8, 2013 - 4:41:28 AM - Shalini Back To Top (27075)

In MS SQL Server 2005 whenever i select differential backup file from device to restore it does not populated on the list instead it display the old file..please suggest any solution because each time on restoring it shows an error msg no file is ready to rollforward..

 

Monday, April 26, 2010 - 1:08:01 PM - sql_noob Back To Top (5286)

we do all differential back ups where i work and no log backups. the size issue is the worst thing. i have some 300GB databases where the diff backup is 80% or more of the full backup size.

 since all of our backups are directly to tape i worked around this by having the expiration time for these backups being around 2 weeks. this way the tapes are always being cleared up.


Friday, July 11, 2008 - 1:49:23 PM - admin Back To Top (1412)

bkshilo,

You are correct, if you need to get to the most recent point in time, only the last differential backup is needed, not each of the individual differential backups.

In the circumstance I was faced with, we used differential backups as a means to perform pseudo log shipping.  What I mean by this is differential backups were taken and applied to backup systems as a means of DR\HA solution for a specific set of applications.  So rather than having to worry about applying logs, differential backups\restores were automated and the customer was willing to lose data in between failovers as long as the failover was very fast and required minimal manual intervention.  In the situation I was in, only a RESTORE DATABASE [NAME] WITH RECOVERY command was needed (as opposed to figuring out the correct differential backup file to restore) to bring the database online which was completed as a portion of the bigger application failover process.  SQL Server was just one piece of the process.

This was a unique situation from my perspective for a number of reasons, so please let me know if that explanation makes sense.

Thank you,
The MSSQLTips.com Team

Friday, July 11, 2008 - 9:16:56 AM - bkshilo Back To Top (1408)

I disagree with the RESTORE example.  After restoring the full backup file, you should only have to restore the 2nd differential backup file.














get free sql tips
agree to terms