sp_dbinstall System Stored Procedure

Installs a database and its devices. It must be run once for each device of the database being installed. This system stored procedure copies the system device to the hard disk (placing the system catalog tables and the transaction log on read/write media). You can leave the data device on the distribution media (usually a CD), or you can copy it to the hard disk (but it remains read-only).

Syntax

sp_dbinstall database, logical_dev_name, 'physical_dev_name', size,
'devtype' [,'location']

where

database

Is the name of the removable media database being installed. Any valid database name conforming to the SQL Server rules for identifiers can be used. You are not required to enter the database name that was originally used for this database during development.

logical_dev_name

Is the logical name of the device being installed.

physical_dev_name

Is the physical name, including the fully qualified path, of the device being installed. The path entered here is the path to the device on the distribution media.

size

Is the size, in 1-MB blocks, of the device being installed.

devtype

Indicates the type of device being installed:

SYSTEM

Indicates that the device being installed contains the system catalog tables and the transaction log. The system device must be installed before the data device.

DATA

Indicates that the device being installed contains data tables. The data device must be installed after the system device. Note that even if a data device will be left on the distribution media, it must be installed using this system stored procedure.

If the database contains more than one data device, you must run this system stored procedure once for each device.

location

Indicates the destination location to which the device will be copied from the distribution media. This is usually a destination on the computer's local hard disk. The location is a fully qualified filename, including the drive, directory, and filename of the device. The referenced directories must already exist (sp_dbinstall will not create them).

A location is required for the system device. It is optional for a data device (since a data device can be left on the distribution media).

Remarks

Important When using sp_dbinstall, all of a database's devices must be installed.

The information required by the sp_dbinstall parameters is provided when the database is verified using sp_certify_removable.

When a database is installed using sp_dbinstall, it is set to offline. Before it can be used it must be placed online by using sp_dboption. After the database has been installed using sp_dbinstall and brought online using sp_dboption, it can be configured to suit local requirements. Users can be added, permissions can be granted, and stored procedures and views can be created. However, after the database is installed, do not attempt to perform inserts, updates, or deletes against the read-only data in the data tables.

Before the database can be used (that is, set online by using sp_dboption) all of its devices must be available. This means that if a database¾because of size¾has been distributed on several CDs with a different data device on each CD, and if the computer is equipped with only one CD drive, then as each device is installed sp_dbinstall must be provided a location parameter for all but one of the devices, so that all but one of the data devices are copied off the distribution CDs onto the computer's local hard disk. If this is done, only one CD will need to be in the CD drive when the database is used. To eliminate all need for a CD when using the database, all the data devices can be copied.

As an advanced option, if the data device has been moved to read/write media, it can be made read/write by running sp_addsegment against the device and using sp_devoption to set the readonly setting to false for that device.

You can also use this system stored procedure to install databases and devices from non-removable media.

Example

This example installs the removable media database inventory from the distribution CD, which is located in the computer's CD-ROM drive, drive E. The system device containing the system catalog tables and the transaction log is copied to the computer's hard disk and the data device is left on the distribution media.

sp_dbinstall inventory,invsys,'e:\invsys.dat',2,'SYSTEM',
   'c:\sql60\data\invsys.dat'
EXEC sp_dbinstall inventory,invdata,'e:\invdata.dat',10,'DATA'

Permission

Only the system administrator can use this stored procedure.

Tables Used

sysdatabases, sysdevices, sysusages

See also