Installing a Removable Media Database

Use sp_dbinstall to install a removable media database. sp_dbinstall must be run once for each device of the database. It copies the system device to the hard disk (which places the system catalog tables and the transaction log on read-write media). It can leave the data device on the distribution media (usually, a CD), or copy it to the hard disk, but it always remains read-only.

The information required by the sp_dbinstall parameters was provided when the database was verified using sp_certify_removable. This information should have accompanied the distribution disk.

Databases are installed as offline (unavailable). Place the newly installed database online (ready to be used) using sp_dboption.

To install a database and place it online

Place the removable media into the drive.
Run sp_dbinstall to install the system device. Type:
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 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 that is 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. Data devices 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 by using this stored procedure.
If the database contains more than one data device, this stored procedure must be run 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 the data device (since the data device can be left on the distribution media).
Before the database can be used (that is, can be set online using sp_dboption), all 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 disk when using the database, all the data devices can be copied.

For example:
```
sp_dbinstall inventory,invsys,'e:\invsys.dat',2,'SYSTEM',
   'c:\sql60\data\invsys.dat'
```
Run sp_dbinstall again to install the data device.
For example:
```
sp_dbinstall inventory,invdata,'e:\invdata.dat',10,'DATA'
```
Run sp_dboption to place the database online. Type:
sp_dboption dbname,offline,FALSE
where

database

Is the name of the database being placed online.

offline

Is the sp_dboption option being set.

FALSE

Is the setting for offline.
When offline is TRUE, the database is offline. When FALSE, the database is online and can be used.

For example:
```
sp_dboption inventory, OFFLINE, FALSE
```
When a database is placed online, any needed removable disk must be in the disk drive. A disk is needed if it contains a data device that was not moved to the local hard disk during installation.
Now that the database is online, administer the database in the normal manner to add database users, set permissions, and add views and stored procedures.

Note After you install a database, you should back up the master database.

For more information about using sp_dbinstall and sp_dboption, see the sp_dbinstall System Stored Procedure and sp_dboption System Stored Procedure topics in the Microsoft SQL Server Transact-SQL Reference.