Speed up restore database from snapshot

Question

To revert a test database to an initial state (after running a test), I would like to restore the database from snapshot. I'm using the following script to achieve that.

However, the script execution now takes around 7-8 seconds, since it first disconnects all users from the database (by setting the DB to SINGLE_USER mode).

Is there any way the restoration process could be made faster so that the script could be called ideally before each automated (E2E) test?

ALTER DATABASE [MyDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [MyDb] FROM DATABASE_SNAPSHOT = @snapshotName;
ALTER DATABASE [MyDb] SET MULTI_USER;

Elapsed time for each command:

1. 3065 ms
2. 2766 ms
3. 2 ms

This technique is meant to be used for end-to-end UI tests, so there is no direct control over transactions during a test.

I'm running the SQL Server in Docker. Could that play a significant role in how long it takes?

Answer 1

I replaced the first ALTER DATABASE (setting the DB to single user mode) command with a new one which simply kills all sessions. It's much faster, I would say it's immediate (with no delay). Meaning, that also the other ALTER DATABASE (setting the DB to multi user mode) command is not needed.

DECLARE @kill varchar(8000) = '';

SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('MyDb');
PRINT "KILL commands to be executed: " + @kill

EXEC(@kill);

I would still be interested in if and how the actual RESTORE could be sped up. - user240502

Snapshots are "copy on write". The time it takes to restore from a snapshot is proportional to the amount of data changed. If you're changing a lot of data, it's going to take time. - user200

There's little you can do about that, other than storing the snapshot on a fast SSD - user220697

Answer 2

I can suggest two things that we've used in the past to improve restore times for a similar scenario of a database being used for automated tests:

1. Shrink the log file before taking the backup to absolutely minimize the size of the backup. In our case this made a surprisingly large difference.
2. Ensure you are taking a compressed backup. I just did a quick test on our DB (Approx 12GB) and a compressed backup restored in 41 seconds vs 58 seconds for an uncompressed backup. Your mileage may vary depending on whether your particular restore is CPU or IO bound.