question

JakubJanis-2177 avatar image
0 Votes"
JakubJanis-2177 asked Yufeishao-msft answered

How to speed up RESTORE DATABASE FROM DATABASE_SNAPSHOT?

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered JakubJanis-2177 commented

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?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your answer. It’s around 100 MB and has tens of tables. I could make it much smaller in size since it’s for e2e testing purpose only and still contains data that is not necessarily needed. Anyway, does the db size play any role in restore performance? I though it is rather amount of changes made to the db since the snapshot was created which could play its role maybe. But in my case it’s small changes only.
Do you know any trick or settings which could speed the restoration up? Yes, 4 seconds is really fast. However, ideally I would like to restore after execution of each test or every few tests, so it would be better if the revert is quicker.

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for testing it. I think I will need to do some more experiments, based on what you just found out. I'm running it on my MacBook which has 1 SSD. Now I get times around 2.2 - 2.4 seconds which is not bad. But I forgot to mention the SQL Server is running in a docker container. Maybe this could have a bit of impact on performance.

0 Votes 0 ·

Certainly better hardware can always help. :-)

I made a test with same database (well, the same data and schema built from scripts) in a container running in an Ubuntu VM running on the same machine as where I did the first test. Actually, in my test the restore completed in 0.6 seconds, which was a little surprising.

1 Vote 1 ·

That’s quite surprising that it’s even faster in your test when it’s run in a container, isn’t it?
0.6 seconds is really great performance. Do you have a super-fast SSD? Do you know what is the declared write speed of your disk so we could possibly compare?
Mine is allegedly around 1200 MB/s. Measured with "Blackmagic disk speed test", at the moment it fluctuates between 600-850 MB/s.
However, I have both database and snapshot on a single disk.
I wonder if anything else can also play a role in performance in addition to the disk write speed.

0 Votes 0 ·
Show more comments
Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered

Hi @JakubJanis-2177,

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://docs.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".

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.