How to speed up restore database from snapshot in SQL Server?

Posted on

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

Answer :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *