2

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?

0

2 Answers 2

1

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.
0

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

Your Answer

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

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