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 test? Thank you for any opinions.

Note: I'm running the SQL Server in a Docker container. Not sure if this has a significant impact on the rollback and restore performance.

ALTER DATABASE [MyDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [MyDb] FROM DATABASE_SNAPSHOT = @snapshotName;
ALTER DATABASE [MyDb] SET MULTI_USER;
Improve this question
4
  • In the comment on this same question on SO, you mentioned this is a Docker container. Add that detail to your question.
    – Dan Guzman
    Commented Oct 15, 2021 at 17:35
  • @DanGuzman Done.
    – Jakub Janiš
    Commented Oct 15, 2021 at 17:48
  • At first sight, it seems that simply KILLing the sessions instead of setting the DB to SINGLE_USER mode (with rollback) is much faster. When I try to kill a session it seems it's just immediate, without any delay. Maybe, this way at least the first step of the process could be sped up.
    – Jakub Janiš
    Commented Oct 15, 2021 at 18:05
  • KILL statement doesn’t wait for transaction rollback completion to return, while SET SINGLE_USER WITH ROLLBACK IMMEDIATE has to wait until it’s able to get an exclusive lock on the database
    – Eric Prévost
    Commented Oct 15, 2021 at 18:31

0

Reset to default