LOAD Statement

For SQL Server 6.5 information, see LOAD Statement in What's New for SQL Server 6.5.

Restores a backup copy of a user database and its transaction log (LOAD DATABASE) or only the transaction log (LOAD TRANSACTION) from a dump that was created using the DUMP statement. The LOAD statement can also be used to retrieve header information from a database dump (LOAD HEADERONLY).

Syntax

Loading a database:

    LOAD DATABASE {dbname | @dbname_var}
        FROM dump_device [, dump_device2 [..., dump_device32]]
    [WITH options
        [[,] STATS [ = percentage]]]

Loading a transaction log:

    LOAD TRANSACTION {dbname | @dbname_var}
        FROM dump_device [, dump_device2 [..., dump_device32]]
    [WITH options]

Loading header information:

    LOAD HEADERONLY
        FROM dump_device

where

dump_device =

{ dump_device_name | @dump_device_namevar}
| {DISK | TAPE | FLOPPY | PIPE} =
{'temp_dump_device' | @temp_dump_device_var}}
[VOLUME = {volid | @volid_var}]

options =

[[,] {UNLOAD | NOUNLOAD}]
[[,] {SKIP | NOSKIP}]
[[,] {FILE = fileno}]

DATABASE

Specifies that the complete database and transaction log are to be restored.

TRANSACTION

Specifies that only the transaction log is to be applied to this database. Transaction logs must be applied in sequential order. SQL Server checks the timestamps on the dumped transaction log to make sure that the transactions are being loaded into the correct database and in the correct sequence.

HEADERONLY

Retrieves all the volume and dump header information for all dumps on a particular dump device. For details about the header information returned, see "Volume and Header Information," later in this topic.

dbname | @dbname_var

Specifies the database from which the transaction log or complete database is being dumped. This name can be specified as a string or as a char or varchar variable.

dump_device_name | @dump_device_namevar

Is the logical name of the dump device as created by sp_addumpdevice. The dump device name can be specified as a string or as a char or varchar variable.

fileno

Is the specific file number to be loaded from the tape or disk device that contains multiple database dumps. The default is 1; load the first dump on the device.

{DISK | TAPE | FLOPPY | PIPE} =

'temp_dump_device' | @temp_dump_device_var

Allows backups to be loaded directly from temporary dump devices. These devices must already exist when the load occurs; they do not have entries in sysdevices. The device types of DISK, TAPE, and FLOPPY should be specified with the actual address (for example, complete path and filename) of the device. A device type of PIPE should specify the name of the named pipe that will be used by the client application. If specified as a variable (@temp_dump_dev_var), the device name can be specified as a string or as a char or varchar variable. If you are using either a network server with a UNC name or a redirected drive letter, specify a device type of DISK.

Important When specifying a striped dump, you can use both logical device names (or variables) and temporary device names (or variables). PIPE devices have been added to allow third-party vendors a flexible and powerful way to connect their own software. For typical Transact-SQL use, the PIPE device will not be used.

VOLUME = volid | @volid_var

Specifies the volume ID, volid, a 6-byte character string. If dumping to a new tape, this value is the name of the ANSI VOL1 label. For SQL Server, the default is SQ0001. For subsequent dumps, this value is used to validate the volume name of the dump device. If specified as a variable (@volid_var), the volume name can be specified as a string or as a char or varchar variable. It is not necessary, or recommended, to explicitly specify the volume ID.

UNLOAD

Specifies that the tape is automatically rewound and unloaded when the dump is finished. UNLOAD is set by default when a new user session is started. It remains set until that user specifies NOUNLOAD. This option is used only for tape devices.

NOUNLOAD

Specifies that the tape will not be unloaded automatically from the tape drive after a dump. NOUNLOAD remains set until UNLOAD is specified. This option is used only for tape devices.

SKIP

Instructs the server not to read the ANSI TAPE device headers.

NOSKIP

Specifies that the server will read ANSI TAPE device headers. This is the default.

STATS = percentage

Returns the percentage of pages loaded in increments optionally set at the time of the load. If the percentage is not specified the statistics will be shown for each 10 percent of the load. This option is available only for database loads.

Remarks

During the load, the specified database must not be in use. Any data in the specified database is replaced by the loaded data.

You can use LOAD DATABASE to move a database to another device or another server as long as the same code page and sort order are running on both servers. If the code page or sort order differs, use the SQL Transfer Manager to move the data. If the sort order and code page are the same, the recipient database must be as large as, or larger than, the database to be loaded. If the recipient database is too small, SQL Server displays an error message that gives the required size. The device fragments that the new database uses for data and for the log must appear in the same order and have the same amount of space as the fragments in the original database. For example, if the original database had 10 MB of data (on any number of fragments), followed by 3 MB of log, followed by 6 more MB of space for data, the new database must have the same order by space and usage: that is, 10 MB for data, 3 for log, and 6 (or more) for data. The total size of the destination database must be at least as large and have at least as many fragments (assigned in the same order and have the same uses) as the load.

If the dumped database uses segments to store particular tables or indexes, the new database must include devices of the same size and page order for these segments and their related data in order for LOAD statement to work correctly. Execute the sp_helpdb system stored procedure with the database name to see the size and fragments. Select from the sysusages system table to determine the order of the device fragments. Then re-execute the CREATE DATABASE statement and the ALTER DATABASE statement to re-establish the fragments. For details on using the LOAD statement to restore or move databases, see the Microsoft SQL Server Administrator's Companion.

If a database will be loaded into a newly created database, use CREATE DATABASE FOR LOAD when creating the new database. The FOR LOAD option specifies that the data pages will not be zero-initialized because the only operations allowed against that database are ALTER DATABASE FOR LOAD or LOAD DATABASE. Any unused data pages will be initialized by the database load. For more information, see the CREATE DATABASE statement.

For compatibility, user database dumps from SQL Server 4.2x can be loaded into SQL Server 6.0, as long as the same sort order is running on both SQL Servers. The user database dump (the master database is not allowed to be loaded or upgraded from SQL Server 4.2x dumps) must be a full database dump, not a transaction log dump, and the dump must occur when there is no update activity taking place in the database. For this reason, it is recommended that the dump occur when SQL Server is started in single-user mode. After the database is successfully loaded, the database will be upgraded. For more information about database upgrades and potential compatibility issues, see Microsoft SQL Server Setup.

Important Cross-platform dumping and loading is not supported. For example, a database dumped on a MIPS-based computer cannot be loaded into an Intel-based computer. If you need to move data from one platform to another, use the Microsoft SQL Transfer Manager.

Permission

LOAD DATABASE and LOAD TRANSACTION permission default to the database owner and are not transferable.