Creating a Removable Media Database

A database that will later be distributed on removable media must conform to these guidelines:

• The database must be created on new devices.
• The database must be created on devices that are not and will not be used by any other databases. This ensures that the first fragment begins at the start of the device and that any additional fragments that may be created by altering the database will be contiguous and in the proper sequence.
• The database should use three or more devices. Place the system catalog tables on one device, the transaction log on one device, and the data tables on one or more devices.

To make certain that the database you create conforms to these requirements, create the database using the sp_create_removable stored procedure. This stored procedure creates three or more devices and places the database on those devices. One device holds the system catalog tables, a second holds the transaction log, and the third and subsequent devices hold data tables. You must be the SA to use sp_create_removable.

    To create a database that can later be distributed on removable media

• Type

  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.

  sysphysical

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

  syssize

  Is the size, in megabytes, of the device that will hold the system catalog tables. 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 at least one data device. The maximum is 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.

For example:

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

For more information about using sp_create_removable, see the sp_create_removable System Stored Procedure topic in the Microsoft SQL Server Transact-SQL Reference.

After the database has been created, perform database development in the normal manner but following these rules:

• Keep the devices exclusive. Do not add any other databases or logs to the devices used by the database.
• Keep the SA as DBO of the database.
• Do not add users to the database, and do not change user permissions. (However, groups can be added and permissions granted to groups.)
• Do not reference objects outside the database (for example, do not create views, stored procedures, or triggers referencing objects outside the database), since those objects will not be available when the database is distributed.

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

As an alternate development method, you can create and develop a database normally, run sp_create_removable to create the new database that will be distributed on removable media, and then use SQL Transfer Manager to copy the data tables from the original database to the new database. After that, you can certify the database by using sp_certify_removable.

Optionally, you can also create one or more that can be used to add login IDs and database users and to grant permissions after the database is distributed and installed.