0

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 how the restoration process could be made faster so that the script could be called ideally before each automated (E2E) test? Thank you for any opinions.

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

EDIT [2121-10-16]: 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.

10
  • 1
    I'd imagine the length of time it takes is mostly the RESTORE not the ALTER, and there is very little you can do about that, other than storing the snapshot on a separate fast SSD. Do you have actual timings for each command? Oct 15, 2021 at 16:25
  • Elapsed time for each command: 1) 3065 ms 2) 2766 ms 3) 2 ms . I'm running the SQL Server in docker. Could that play a significant role in how long it takes? Oct 15, 2021 at 17:05
  • @TheImpaler Thank you for the link. I'll give it a try. Oct 15, 2021 at 17:08
  • 1
    Could you maybe just ROLLBACK the transactions in your unit test code instead? Restoring the entire DB between each test seems out of place.
    – HardCode
    Oct 15, 2021 at 18:35
  • 1
    Unit testing frameworks like tSQLt execute their tests in the context of a transaction, which they simply rollback at the end of each test to undo any modifications. Have you considered doing something similar? Oct 16, 2021 at 0:22

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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