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.