Creating a Removable Database
In Microsoft® SQL Server™ 2000, you can create a database for read-only purposes that can be distributed by way of removable media, such as CD-ROM. This can be useful for distributing large databases containing history data, such as a database containing detailed sales data for the last year.
To create a removable media database, you create the database using the sp_create_removable system stored procedure rather than using SQL Server Enterprise Manager or the CREATE DATABASE statement.
The sp_create_removable system stored procedure creates three or more files:
- One file containing the system tables
- One file containing the transaction log
- One or more files containing the data tables
Although the database itself is likely to remain on the read-only media, such as CD-ROM, the system tables and transaction log are placed in separate files on writable media so that management tasks can be accomplished, such as adding users to the database, granting permissions, and so on.
A database can use multiple removable media devices. However, all media must be available simultaneously. For example, if a database uses three compact discs, then the system must have three CD-ROM drives and have all discs available when the database is used.
After the database has been created, you can use the sp_certify_removable system stored procedure to ensure that the database is configured properly for distribution on removable media. If the database is configured correctly, the database is placed offline, allowing the files to be copied to the removable media. By placing the database offline, users are prevented from accessing the database, and no modifications to the database can be made until the database is placed online. To make the database available again on the same server, place the database online.
After the files have been distributed on removable media, the database can be made available by attaching the files to a different instance of SQL Server. For more information, see Attaching and Detaching a Database.
To place a database online or offline
Transact-SQL