SQL Server – Restoring Only Differences with Differential Backups

restoresql server

I find something similar here but it was asked almost 10 years ago and by that time there was no option. I wonder if there is any now.

I am doing some actions on application and getting back ups before and after but when I want to restore the db to roll back to previous version, it cost a lot of time and distraction. I wonder if there is any quick way to get differential back ups like myDB_v1, myDB_v2, v3 etc.. and restore between them quickly.
Thanks in advance.

Best Answer

The question you asked

No. It is not an option to apply just a differential backup, without the full backup (aka the differential base backup).

You want to essentially undo changes made after the backup, and differentials cannot do this. They are designed to work in the other direction--they "fast forward" changes made since the prior full backup, allowing you to get to the end state quicker, without having to redo the actual changes along the way.

A differential backup contains only the data pages that have changed since the last full backup. Your scenario would require undoing pages that are changed after the differential backup is taken--the differential has no idea what will change in the future, so it likely doesn't have the data to undo those changes. In order to do what you suggest, you need a backup with every data page, so that you can undo any possible changes--the mechanism for this is a Full backup.

The question you should have asked

It sounds like you want to look at database snapshots.

Snapshots are essentially that inverse of differentials that you're looking for. They keep track of changes since the snapshot creation, so that the snapshot shows the original version, regardless of what happens to the real database. Snapshots can also be used to roll back to the point in time when they were created.

From the docs:

In the event of a user error on a source database, you can revert the source database to the state it was in when a given database snapshot was created. Data loss is confined to updates to the database since the snapshot's creation.

For example, before doing major updates, such as a bulk update or a schema change, create a database snapshot on the database protects data. If you make a mistake, you can use the snapshot to recover by reverting the database to the snapshot. Reverting is potentially much faster for this purpose than restoring from a backup; however, you cannot roll forward afterward

It's important to note that snapshots are not backups. They can be used to revert data changes, but they are not helpful if the original/real database becomes corrupt, or goes offline.

You'll want to read up on reverting to a snapshot to ensure that it does meet your needs, and that the various limitations work for your scenario. In particular, in order to revert to a snapshot, you'll need to drop other snapshots first.

You mention "...myDB_v1, myDB_v2, v3 etc.. and restore between them quickly." Snapshots would allow you to actively query all versions simultaneously, but when you want to revert your "real" database to one of those versions, that process would involve dropping all other snapshot versions.

Related Question