sp_create_removable System Stored Procedure

Creates a removable media database. Creates three or more devices (one for the system catalog tables, one the transaction log, and one or more for the data tables) and places the database on those devices. Use this stored procedure to create the database prior to performing database development.

Syntax

sp_create_removable dbname, syslogical, 'sysphysical', syssize,
loglogical, 'logphysical', logsize, datalogical1, 'dataphysical1', datasize1
[... , datalogical16, 'dataphysical16', datasize16]

where

dbname

Is the name of the database that will be created for use on removable media.

syslogical

Is the logical name of the device that will contain the system catalog tables and the transaction log.

sysphysical

Is the physical name, including a fully qualified path, of the device that will hold the system catalog tables and the transaction log.

syssize

Is the size, in megabytes, of the device that will hold the system catalog tables and the transaction log. The minimum syssize is 1.

loglogical

Is the logical name of the device that will contain the transaction log.

logphysical

Is the physical name, including a fully qualified path, of the device that will contain the transaction log.

logsize

Is the size, in megabytes, of the device that will contain the transaction log. The minimum logsize is 1.

datalogical

Is the logical name of a device that will contain the data tables.

There must be from 1 through 16 data devices. In general, you will create more than one data device when the database is expected to be large and will need to be distributed on multiple disks.

dataphysical

Is the physical name, including a fully qualified path, of a device that will contain data tables.

datasize

Is the size, in megabytes, of a device that will contain data tables. The minimum datasize is 1.

Remarks

Only removable media databases should be placed on these devices.

The device created for the transaction log is used only during database development. (When database development is complete you will run the sp_certify_removable system stored procedure. Among other actions, sp_certify_removable will truncate the transaction log, move it to the system device, and drop the log device.)

Example

This example creates the database inventory as a removable database.

sp_create_removable inventory, invsys, 'c:\sql60\data\invsys.dat, 2', 
   invlog,'c:\sql60\data\invlog.dat',4
   invdata,'c:\sql60\data\invdata.dat',10, 

Permission

Only the system administrator can use this stored procedure.

See also