sql server – Restoring only differences with SQL differential backups?


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.

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.