8

Each of our 10000 integration tests need to restore the database to its original "clean" state before each of them are done, during a cleanup stage. Running all tests takes time, and 80% of the time is spent during the database restore operation.

The restore operation simply takes a backup from disc and restores it. I am now wondering if there is a way to optimize this. One option I am thinking of is to change the location of the backup file from disc to memory. Is that possible?

Another option would be to take a snapshot of the table data only, and save only that as the backup data. I assume then that the backup file becomes smaller, and therefore faster to restore. Is that possible?

Ideally, I would like to avoid restoring, and just reset the affected data (the data that each test produces). Something like: "revert any table rows that were modified since the test started"

What options do i have? I am not an expert in this area but it feels like my problem should have an easy solution.

Improve this question
6
  • 8
    Check out database snapshots, take one before your test, and then just revert when the test is complete, which will put it back to the state it was in when you started. learn.microsoft.com/en-us/sql/relational-databases/databases/…
    – Nic
    Jul 12, 2017 at 20:43
  • 1
    Now available in all editions starting with 2016 SP1!!!
    – John Eisbrener
    Jul 12, 2017 at 20:46
  • snapshots? Thats what i am currently using (Sql Server 2014). Maybe i am mixing snapshot backups with database snapshots?
    – Mr Balanikas
    Jul 12, 2017 at 20:55
  • 3
    Snapshot backups are usually done at the hypervisor layer. Database snapshots are internal to SQL. They create a sparse file (in a location you specify) which grows along with changes to the DB. But you can revert that snapshot at any time, which is a quick process, putting you back to the point where the snapshot was taken.
    – Nic
    Jul 12, 2017 at 21:01
  • VDI/VSS snapshots (e.g. what you're referring to as "snapshot backups") should be quite fast to recover from. This is off-topic in regards to your question, but if you're spending a majority of time restoring these sort of backups, your current backup process may not be configured properly.
    – John Eisbrener
    Jul 12, 2017 at 21:28

1 Answer 1

Reset to default
7

Database snapshots are probably the right answer for your specific situation, per Nic's comment.

But for future users finding this question, there are definitely ways to improve native SQL database backup and restore times:

  • Backup your databases with compression: I/O is typically the bottleneck, this shifts some of the work to CPU.
  • Change your storage configuration: Store backups locally or on higher-performance SAN
  • Backup your databases to multiple files (stripes): This might depends on your specific storage and network configuration, but I've found 4- and 8-stripe backups and restores in our environment complete 2x-10x faster than single-stripe backups. Certainly worth some testing.
  • Make sure instant file initialization is enabled: Helps you carve out the space for restoring files very quickly
  • Reduce the size of the database: Drop old tables, etc. When possible, of course.
  • Shrink away empty space: All the appropriate warnings and caveats about shrinking apply, of course.
  • Enable page or row compression: Makes the database files smaller, so less to write upon restore.
Improve this answer
2
  • It might be worth pointing out that page or row compression are Enterprise Edition features. If compression has been enabled in a database for tables or clustered indexes, you will be unable to restore a database backup from an Enterprise Edition instance to a Standard Edition instance with turning compression off again.
    – John K. N.
    Jul 13, 2017 at 8:13
  • @hot2use These are Enterprise Edition only features for any SQL Version prior to SQL 2016 SP1 or later.
    – John Eisbrener
    Jul 13, 2017 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.