Creating a Removable Database
In SQL Server 2005, you can create a database for read-only purposes that can be distributed through removable media such as CD-ROM. This can be useful for distributing large databases that contain history data such as a database that consists of detailed sales data for a previous year.
To create a removable media database, you create the database using the sp_create_removable system stored procedure instead of using SQL Server Management Studio or the CREATE DATABASE statement.
The sp_create_removable system stored procedure creates three or more files, including the following:
- One file that contains the system tables
- One file that contains the transaction log
- One or more files that contain 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 put in separate files on writable media. This is done so management tasks, such as adding users to the database and granting permissions, can be accomplished.
A database can use multiple removable media devices. However, all media must be available at the same time. For example, if a database uses three compact discs, 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 make sure the database is configured correctly for distribution on removable media. If the database is configured correctly, the database is placed offline. This allows 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 Detaching and Attaching Databases.
To place a database online or offline
See Also
Other Resources
sp_certify_removable (Transact-SQL)
sp_create_removable (Transact-SQL)