The following stored procedures are used to administer removable media. The following information provides summary descriptions. For more information, see the Microsoft SQL Server Transact-SQL Reference.
| Stored procedure | Role in removable media administration |
|---|---|
| sp_certify_removable | Verifies that a database is configured properly for distribution on removable media. If the database passes, it is set to offline, and some information required for installation is returned. |
| sp_create_removable | Creates a removable media database. Creates three or more devices and places the database on those devices. One device holds the system catalog tables, one the transaction log, and one or more the data tables. Use this stored procedure to create the database prior to performing database development. |
| sp_dbinstall | Installs a database and its devices. Must be run once for each device of the database being installed. Copies the system device to the hard disk (which places the system catalog tables and the transaction log on read-write media). Can leave data devices on the distribution media (usually, a CD) or can copy data devices to the hard disk. |
| sp_dboption | Used to place a database online (database is ready to be used) or offline (database is unavailable). Place a database offline before removing a disk containing the data device from a drive. |
| sp_dbremove | Used to remove an installed database (and, optionally, any devices it has exclusive use of). This procedure can be used for all databases, not just those on removable media. |
Note Some of these stored procedures have other uses and other options not documented here. This chapter discusses only the functionality of these stored procedures related to removable media. For more information, see the Microsoft SQL Server Transact-SQL Reference.
Also, although this section focuses on administering databases on removable media, note that you can also set databases and devices to online or offline using sp_dboption, and you can use sp_dbremove to drop databases, whether the databases are on removable media or not.