Another factor that affects restore speed is the choice of the database state after a restore, in cases where recovery is not selected.  When you choose not to recover a database, usually because you want to apply further updates, you have a choice of using the NORECOVERY or STANDBY options. NORECOVERY leaves the database in recovery mode, allows you apply further updates, and prevents anyone from accessing it. STANDBY also leaves the database in recovery mode, allows you to apply further updates, but allows users read only access to the database.

When you use the STANDBY option, you provide a name for the undo file name.  This file contains the undo actions resulting from uncommitted transactions.  The more uncommitted transactions you have, the larger the file, and subsequently the longer the restore will take.

In the following example, 4 transaction log backups were made, each approximately 131 MB in size.  All backups contained only committed transactions, except for the third transaction log, which contained 32 MB of uncommitted transactions.  The following results were recorded:

- when restoring the transaction logs using the NORECOVERY option

- when restoring the transaction logs using the STANDBY option

Overall, restoring the transaction logs using the STANDBY option is slower compared to when the NORECOVERY option is used.  In addition, when there are uncommitted transactions, SQL Server needs to spend additional time creating the undo file.

The point then is, if you are restoring multiple transaction logs, and you want the database to be in read only mode, you should restore the transaction logs using the NORECOVERY option.  When all the logs have been applied, you can then switch the database to read-only mode e.g.

RESTORE DATABASE mydb WITH STANDBY = 'g:\data\mydb\mydb_und.dat'

In this way, you only incur the cost of creating the undo log once, instead of every time that a transaction log is restored, and speeds up the restore process.