Separate differential backup from one restored baseline

Question

I work on a team that customises software, part of this customisation is stored as records in a database, part is customisations to the schema itself and there are often some specific records in the database too.

I'd like to create a baseline version of the database and back it up normally. Then for each customer I restore the baseline database under the customer's name and customise it as usual. I'd then like to do a differential backup of the database from the baseline point to the current point and shelve the differential backup for that customer, I expect about 0.1-0.2% changed data. When I need to do more work for that customer I restore the baseline backup and their differential backup, do the work then backup again, replacing the shelved baseline backup.

This is similar to Differential databases (not backups) but using backups.

Can I have different differential backups that can be restored against a single baseline?

Can I do a new differential backup from the same starting point or would I need to do incremental backups.

Any problems or experiences with this sort of approach?

Answer 1

The question is a little ambiguous, but either of the two ways I see to interpret it would work:

Starting point: A differential backup captures all changes from the last full backup (the 'differential base').

Both scenarios below start by creating the baseline database and taking a full (not copy only) backup of it.

Scenario 1 - Continuing differentials

• Restore the baseline
• Restore the latest differential for customer X (if any)
• Make changes for customer X
• Perform a new differential backup for customer X

You need to keep the full baseline and the latest differential for each customer in this scenario.

Scenario 2 - Separate full backups

• Restore the latest full backup for customer X (or the baseline if a new customer)
• Make changes for customer X
• Perform a new full backup for customer X

You need to keep the baseline (for new customers) and the latest full backup for each customer in this scenario.

---

In scenario 1, the size of the differentials will grow larger over time, but never larger than the size of the current database for each customer.

Restoring for each customer requires two steps: baseline, then customer-specific single latest differential. Previous differentials can be discarded if necessary.

Scenario 2 will require more storage space than scenario 1, as each customer gets a full copy of their current database.

Restoring for each customer is a simple one-step process. Previous per-customer full backups may be discarded.

---

As usr mentioned in a comment, one drawback to either set up is it makes it more difficult to apply a change to all customers.

As always, a known good current database validated by DBCC CHECKDB with the appropriate options is needed, and the only valid backup is one you have tested to restore successfully (and pass the checkdb tests). It may be a good idea to keep the historical backups, at least for a time, in a case a customer ever wants to revert.