Speed up restore database from snapshot
3
votes
2
answers
1097
views
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?
Asked by Jakub Janiš
(39 rep)
Oct 15, 2021, 04:04 PM
Last activity: Apr 11, 2023, 07:19 AM
Last activity: Apr 11, 2023, 07:19 AM