Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Differential backups are taken as the difference between the current state, and the last full backup called the "differential base".

Does restoring a full backup reset the differential base? If so, is there a way to avoid that?

The Microsoft docs only say that a backup resets the differential base. My experiments are a little inconclusive, although it seems that restores also reset this value.

I have a list of full backups taken sequentially over time. I'm switching this to a periodic full backup, with interspersed differential backups, eg. one full backup a month, with hourly differentials. However, the existing backup sets are all full backups.

I'm writing a script I want to run on a spare server which will go through the existing sets and sequentially restore them and generate differentials from the backup at the beginning of the month. This won't work if a restore resets the differential base though.

Any pointers or suggestions would be much appreciated. I actually already had something like this working with a binary diff program, but that mysteriously broke this month, so I'm trying to switch to using the built-in differential backups.

Edit: gbn as adequately answered the question of whether the restore resets the differential base, but I'd still like some suggestions on whether it's possible to extract a differential backup if given two full backups.

share|improve this question
    
Your concern is having older backups that take one restore procedure for point-in-time recovery and newer ones that take a different procedure? –  Ram Jan 11 '12 at 0:31
    
Pretty much. Older backups are used for QA, where software developers often restore the closest backup to replicate problems. We have a long history of binary diffs of full backups, and a recent problem in that process forced me to quickly switch to doing full backups. So I have backups in at least 3 formats and I'd like to consolidate. If there's a way to obtain a differential from two full backups, then I can write a script to consolidate everything and just standardize on SQL Server's differentials against periodic full backups. –  naasking Jan 11 '12 at 1:31
    
I have a script that magically picks the right full backup, diff backups, and log backups, and applies them in the right order in order to enable dev to do point in time recovery necessary to debug some problems. For my script it doesn't matter what the periodicity of the fulls, diffs, and tlogs are as long as it's all there. I don't know if having a script that hid the details would suffice for you but if so I can share a link. –  Ram Jan 11 '12 at 2:06

1 Answer 1

The differential backup is always associated with the last full backup. It is that simple.

So any differential restore is looking for a certain full restore.

So yes, the base is reset. You can't mix and match.

Personally, I wouldn't have monthly full backups and hourly diffs. The frequencies don't match (example: weekly full, daily diff, hourly log). And I wouldn't use any binary file diff rubbish for my backups because I expect to be able to restore them.

share|improve this answer
    
I already wrote backup and restore programs that handle the diffs and patching on this schedule, so it's not a burden at all. The binary diffs worked quite nicely until I hit an xdelta bug. Anyway, any thoughts on how to convert a history of full backups into differentials then? –  naasking Jan 10 '12 at 20:50
    
You don't need to. Don't know why you think you do... –  gbn Jan 10 '12 at 20:55
    
I have backup sets in 3 different formats now and I want to consolidate, that's why I need to. Would you want to explain to another dba that restoring for Q&A between dates X and Y you have to use method Z, and between W and T use method S, and so on? Like I said, I have programs that manage backup and restore functions, and I'd like to consolidate on a single method so I can use these programs no matter what time period. –  naasking Jan 10 '12 at 21:02
    
Ok then, you'll have to keep restoring over and over... –  gbn Jan 10 '12 at 21:12
    
I don't follow. Is this an answer to my question of how to extract a differential between two full backups? –  naasking Jan 10 '12 at 21:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.