Microsoft KB Archive/65757

INF: Dump/Load Database Time Differences ID Number: Q65757

1.10 1.11 4.20 OS/2

Summary:

This article addresses the relative time differences between a database DUMP and LOAD, as well as addressing the question of why a LOAD DATABASE takes approximately the same amount of time as a CREATE DATABASE command.

More Information:

When the “LOAD DATABASE” command is executed, SQL Server performs two operations. First, it reads all the pages from the dump device and writes them to the database. Next, it zeros all remaining unallocated pages in the database. This second step is what accounts for the greater difference between dump and load times. As a general guideline, the LOAD DATABASE “x” operation will take approximately the same amount of time as the CREATE DATABASE “x” operation took.

There is one option you can use in some cases to cut down on the time it takes to load a database. If the database is going to be dumped and reloaded onto the SAME device, some time will be saved by NOT dropping the database. The following scenarios illustrate this situation:

Scenario 1
Task                         Time CREATE DATABASE              1 hour DUMP DATABASE                10 minutes DROP DATABASE                n/a CREATE DATABASE              1 hour LOAD DATABASE                1 hour ==================        =============== >>> Total                     3 hours, 10 minutes <--+ |                                                     | Scenario 2 | ———- | | Task Time | —- —- | CREATE DATABASE 1 hour | DUMP DATABASE 10 minutes | LOAD DATABASE 1 hour | ================== =============== | >>> Total 2 hours, 10 minutes <–+

Because the LOAD DATABASE command zeros out the target database anyway, there is really no gain in dropping and re-creating the database. This, of course, does not apply when loading a database onto a new device.