Differential Database Backups for SQL Server

By: Jeremy Kadlec | Read Comments (3) | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: 1 | 2 | 3 | 4 | More

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?

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?

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.

Related Tips: 1 | 2 | 3 | 4 | More | Become a paid author

Last Update: 5/2/2007

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

Friday, July 11, 2008 - 9:16:56 AM - bkshilo	Read The Tip
I disagree with the RESTORE example.  After restoring the full backup file, you should only have to restore the 2nd differential backup file.

Friday, July 11, 2008 - 1:49:23 PM - admin

Read The Tip

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

Monday, April 26, 2010 - 1:08:01 PM - sql_noob	Read The Tip
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.

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL diagnostic manager delivers response in minutes, not hours!" It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now. What grade do you think your SQL Servers get? Find out with Edgewood's SQL Server Health Check consulting services. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Valuable SQL Server web casts on Performance Tuning, Development, Administration, SSIS and more...