LOAD Statement

LOAD Statement (version 6.5)

Loads a backup copy of one of the following:

User database and its transaction log (LOAD DATABASE)
Transaction log (LOAD TRANSACTION)
Table (LOAD TABLE)
Header information about the dump (LOAD HEADERONLY)

For additional syntax information for the LOAD statement, see the Microsoft SQL Server Transact-SQL Reference.

The LOAD TRANSACTION statement supports the STOPAT option, which stops a roll forward at a specified time.

The LOAD TABLE statement loads a backup copy of a table into a database.

The LOAD HEADERONLY statement returns additional columns.

Syntax

To load a table:

LOAD TABLE [[database.]owner.]table_name
FROM dump_device [, dump_device2 [..., dump_device32]]
[WITH options]

To load a transaction log:

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

To load header information:

LOAD HEADERONLY
FROM dump_device

where

dump_device =: {dump_device_name | @dump_device_name_var}
|{DISK | TAPE | FLOPPY | PIPE} =
{'temp_dump_device' | @temp_dump_device_var}
[VOLUME = {volid | @volid_var}]
options =: [[, ] {UNLOAD | NOUNLOAD}]
[[, ] {SKIP | NOSKIP}]
[[, ] FILE = fileno]
[[, ] STOPAT = {date_time | @date_time_var}]
[[, ] SOURCE = source_name]
[[, ] APPEND]
[[, ] STATS [= percentage]]
TABLE: Specifies that a table is 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 time stamps on the dumped transactions to ensure that the transactions are loaded into the correct database and in the correct sequence.
table_name: Is the name of table to be restored.
dbname | @dbname_var: Is the database from which the transaction log or complete database is being loaded. The database name can be specified as a string, char, or varchar variable.
dump_device_name | @dump_device_name_var: Is the logical name of the dump device as created by the sp_addumpdevice stored procedure. The dump device name can be specified as a string, char or varchar variable.
DISK | TAPE | FLOPPY | PIPE =: 'temp_dump_device' | @temp_dump_device_var
Specifies the backup device media and is the temporary dump device from which to load the backup. These devices must 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 complete path and filename of the device. A device type of PIPE should specify the named pipe that will be used by the client application. If specified as a variable (@temp_dump_device_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 Universal Naming Convention (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 is not used.
VOLUME = volid | @volid_var: Specifies the volume ID of the backup device, 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 SQL001. 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. Explicitly specifying the volume ID is not recommended.
UNLOAD: Specifies that the tape be automatically rewound and unloaded when the LOAD is finished. UNLOAD is the default setting 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 not be automatically unloaded when the LOAD is finished. NOUNLOAD remains set until UNLOAD is specified. This option is used only for tape devices.
SKIP: Specifies that the server not read the ANSI tape device headers.
NOSKIP: Specifies that the server read the ANSI tape device headers. This is the default.
FILE: Specifies that a file from a tape or disk device will provide the database, table or transaction log to load.
FILE = fileno: Is the file number to be loaded from the tape or disk device that contains multiple database backups. Load the first dump on the device (the default, 1).
STOPAT = date_time | @date_time_var: Is the date and time to stop loading a dump onto the database. If a variable is used for STOPAT, the variable should have a varchar or a char datatype. Only log records written before the specified date and time will be applied to the database.
APPEND: Specifies that all new data will be added to a table that already contains data. For LOAD TABLE use only.
SOURCE = source_name: Is the name of the source from which to load the table. This option must be specified if the source and target table names differ. For LOAD TABLE use only.
STATS = percentage: Is the percentage of pages loaded in increments optionally set at the time of the load. If a percentage is not specified, statistics are shown for each 10 percent of the load. This option is available only for database and table loads.

Remarks

The LOAD TABLE statement loads only one table at a time, which provides more control when recovering a database. Loading a table from a database dump is discouraged because if data manipulation transactions were occurring on the table when the database dump was performed, the data in the table may be logically inconsistent. You should load a table from a database dump only in disaster recovery situations, and even then it is important that you understand that the data loaded from the dump may be inconsistent.

When loading a table, use a target table and data source table. The data source table holds the data to be loaded to the target table; it can be a full database backup or an individual table backup.

For example, a company maintains a mailing list that is updated quarterly by an outside agency. The mailing list table has no index. The company wants to replace the existing mailing list with the new, agency-supplied mailing list. You could use a procedure that deletes all of the rows of the old table and inserts new rows, but a more efficient approach is to load the new table from a backup.

When loading a table, an EXCLUSIVE TABLE lock is placed on the table. No one else can read or modify the table while it is being loaded.

Important It is recommended that you load a table only from a table dump file because a table dump file is generated from a locked table. It provides an up-to-date snapshot of the table. If you load a table from a database dump file, you will not get any outstanding transactions because the LOAD TABLE statement does not load transactions from the transaction log.

The schema for the data source table and the target table must match for the load to be successful. The names of the tables can be different if the SOURCE option is specified, but the underlying table structure must be the same in terms of base system types, lengths, and nullability.

Note Once you have performed a load table operation, you must do a full database dump. Until you dump the full database, the transaction log cannot be dumped.

Using LOAD TABLE on a populated table is permitted if you use the APPEND option.

During load operations, triggers, rules, defaults, and declarative referential integrity (FOREIGN KEY, CHECK, PRIMARY KEY) are not enforced.

Important Table loads are not permitted on tables that have text columns, indexes, or are published for replication. Table loads from mixed platforms are not supported. For example, you cannot dump a table on an Alpha-based server and load it into a table on an Intel-based server.

When a transaction log is not completely correct (because of possible user error) and you want to apply transactions up to a certain point in time, use the LOAD TRANSACTION statement with the STOPAT clause. If you are unsure which transaction log contains the stopping time, specify the STOPAT clause for all transaction logs that are being loaded.

For example, suppose a user mistakenly deletes the rows in a table on Sunday, Aug. 4, 1996 at 1:00 p.m. The transaction log's last transaction time was Sunday, Aug. 4, 1996 at 1:30 p.m. The STOPAT option is used to stop the roll forward at 12:55 p.m. on Sunday, Aug. 4, 1996. Transactions are applied up to just prior to the deletion.

The LOAD HEADERONLY statement retrieves all the volume and dump header information for all dumps on a particular dump device. The information is returned in a tabular format. These columns have been added to the LOAD HEADERONLY results set.



New columns	Datatype	Comment
BackupStart	datetime	Date and time of the oldest committed transaction. Cannot be NULL.
BackupFinish	datetime	Date and time of the most recently committed transaction. Cannot be NULL.
ServerSort	int	Server sort order. Cannot be NULL. This column is valid for database dumps only.
ServerCodePage	int	Server code page, which is the character set used by the server. Value is 0 when viewing a version 6.0 or earlier dump.
TableOwner	char(30)	Table owner.
OperatorName	char(30)	Name of the user performing the dump operation. Cannot be NULL unless viewing a version 6.0 or earlier database backup.
ProcessorType	int	CPU architecture number. Cannot be NULL.
SQLVersionMajor	int	Major version number of server that made the dump. Cannot be NULL. Value is 0 when viewing a version 6.0 or earlier dump.
SQLVersionMinor	int	Minor version number of server that made the dump. Cannot be NULL. Value is 0 when viewing a version 6.0 or earlier dump.
SQLVersionBuild	int	Build number of server that made the dump. Cannot be NULL. Value is 0 when viewing a version 6.0 or earlier dump.

Examples

A. Load a Table

This example loads the employee_table from the disk file dump1 into the personnel table.

LOAD TABLE personnel FROM dump1 WITH source='employee_table', APPEND

B. Load a Transaction

This example loads a transaction log for the corporate database and stops it on Nov. 4, 1996 at 2:00 p.m..

LOAD TRANSACTION corporate FROM TAPEDUMP1 
    WITH STOPAT='1996 110414:00:00'

C. Load a Header Only

This example loads the header only from a predefined device.

LOAD HEADERONLY FROM dump_dev1

Permission

INSERT permission is required to load a table. The target table's database must have the select into/bulkcopy database option set before loading a table.