CODEWITHSUNDEEP
sqlsql-serversql-server-2012
see sharper
see sharper

Reputation: 12025

How to do a quick differential backup and restore in SQL Server

I'm using SpecFlow with Selenium for doing UI testing on my ASP.NET MVC websites. I want to be able to restore the database (SQL Server 2012) to its pre-test condition after I have finished my suite of tests, and I want to do it as quickly as possible. I can do a full backup and restore with replace (or with STOPAT), but that takes well over a minute, when the differential backup itself took only a few seconds. I want to basically set a restore point and then revert to it as quickly as possible, dropping any changes made since the backup. It seems to me that this should be able to be done very quickly, without needing to overwrite the whole database. Is this possible, and if so, how?

Upvotes: 0

Views: 249

Answers (1)

Ben Thul
Ben Thul

Reputation: 32687

Not with a differential backup. What a differential backup is is an image of all of the data pages that have changed since the last full backup. In order to restore a differential backup, you must first restore it's base (i.e. full) backup and then the differential.

What you're asking for is some process that keeps track of the changes since the backup. That's where a database snapshot will shine. It's a copy-on-write technology which is to say that when you make a data modification, it writes the pre-change state of the data page to the snapshot file before writing the change itself. So reverting is quick as it need only pull back those changed pages from the snapshot. Check out the "creating a database snapshot" example in the CREATE DATABASE documentation.

Keep in mind that this isn't really a good guard against failure (which is one of the reasons to take a backup). But for your described use case, it sounds like a good fit.

Upvotes: 2

Related Questions