How to speed up RESTORE DATABASE FROM DATABASE_SNAPSHOT?

Jakub Janiš 96 Reputation points
2021-10-16T12:50:07.17+00:00

For a test environment, to revert all changes made by a test and to get back to an initial state I'm using the following command (below). It automatically restores the database from snapshot. Currently, it takes around 3-4 seconds to execute. Is there any way how the restoration could be made even faster?
Thank you for your advice or any opinion on this. 

RESTORE DATABASE @databaseName FROM DATABASE_SNAPSHOT = @snapshotName;
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,692 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2021-10-16T17:34:20.673+00:00

    I asked about the size, because if the database is small enough, plain restore from a backup may be faster. I did a test on my machine, and was able to restore a 1GB database in 1.3 seconds. I seem to get the same result, no matter the backup is compressed or not.

    Obviously, it depends on the hardware. In my case, I had the database and the backup on two different SSD disks.

    1 person found this answer helpful.
  2. Erland Sommarskog 100.9K Reputation points MVP
    2021-10-16T13:15:20.087+00:00

    3-4 seconds is very fast for a restore. And one reason it's fast is that they are cheating a bit, and which you should be aware of. That is, the transaction log is thrown away and replaced with a new file of a mere 500 KB. So do the operation properly, you should immediately grow the log to a reasonable size. And, yes, since the log file has to be zeroed out that will be a few more seconds.

    How big is the database in question?

  3. YufeiShao-msft 7,056 Reputation points
    2021-10-18T07:27:15.443+00:00

    Hi @Jakub Janiš ,

    3-4 seconds of recovery time is really fast

    Here is an optimized way:https://www.mssqltips.com/sqlservertip/4935/optimize-sql-server-database-restore-performance/

    you can configure trace flags globally. DBCC TRACEON (3213, -1) this outputs information for backups and restores. According to the information you get, you can adjust some settings to increase the Total Buffer Space.

    Using some additional parameters, about Max Transfer Size and Buffer Count, may be can use more memory to make the restore run faster

    https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190954(v=sql.105)?redirectedfrom=MSDN#optimizing-transaction-log-backup-performance

    -------
    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments