Sql-server – Fastest way to restore a Sql Server database

backuprestoresql server

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.

Best Answer

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.
Related Question