CODEWITHSUNDEEP
sql-serverdatabase-restore
Sekhar
Sekhar

Reputation: 5797

SQL Server database restore for quick testing

I have a big database which takes few minutes to restore from a bak file. I have to run few tests using a web application, and then discard all those changes for repeating the tests. This is the workflow I am currently doing, and my question is that is there a better/less time consuming method.

  1. Restore db from a bak file (bak file is ~5gb and takes around 1 minute)
  2. Perform testing using a web application.
  3. Drop the database, and restore it again afresh from the bak file (step 1)

The db restore option takes a lot of time, I was thinking if I can use something like CHECKPOINTS, or some other feature where I don't have to restore the full backup again.

Edit: I would like to know if solutions exist where we can reduce the restore time using a in-built sql-server feature. Imagine if the restore takes 5 minutes, - whether 5 minutes is a long time or a short time is relative to each user and each use-case.

Upvotes: 2

Views: 1388

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28809

First off, "I have an enormous database with all the data that I run a few tests against and revert after every test" is not, in the long run, a very good pattern in my experience -- there's still the matter of getting that enormous database first somewhere (you don't want to use production, do you?), possible issues with people not being allowed to see the data, concurrency issues if the instance is shared, and the most damning, brittle tests that rely on specific data being present. Look away for two weeks and you find your tests no longer work and people can't say why.

The best long-term solution is to create a minimal database project using SSDT, which serves as something you can check into source control to boot. Deploy as a LocalDB, insert only data needed for your test, test, then discard it. No dependencies, no hidden assumptions.

Having said that, SQL Server does offer database snapshots, created with CREATE DATABASE ... AS SNAPSHOT OF and reverted to with RESTORE DATABASE ... FROM SNAPSHOT = .... As this only involves discarding modified data, it can be much faster than a full restore. It still inevitably suffers from concurrency issues if multiple people want to run tests.

Upvotes: 4

Related Questions