INF: How to Create and Attach a Removable SQL 7.0 Database (255937)

MORE INFORMATION

Microsoft SQL Server allows you to create a database for read-only purposes that you can distribute through removable media, such as a CD-ROM. This may be useful for distributing large databases containing history data. Even though the database itself is likely to remain on the read-only media, such as CD-ROM, you can place the system tables and transaction log in separate files on the local hard disk so that you can perform management tasks such as adding users to the database, granting permissions, and so on.

The example in this article assumes that SQL Server 7.0 is installed in the D:\Mssql7 directory with all data and log files located in the default directory D:\Mssql7\Data. Drive E:\ is the removable media (the CD-ROM).

Prerequisites

You must have system administrator (sa) privileges.

The following example creates a removable database named "RemovableDB" with each file 2 MB in size and prepares the file for distribution.

1. Create the removable database using the stored procedure sp_create_removable as follows:

   use master
   go
   sp_create_removable 'RemovableDB', 
      'Removedbsys',    'D:\MSSQL\DATA\Remove.mdf', 2,
      'Removedblog',    'D:\MSSQL\DATA\Remove.ldf', 2,
      'RemovedbData',   'D:\MSSQL\DATA\Remove.ndf', 2
   go

   The database is created on at least three files:
   
   
   • One for the system catalog tables.
   • One for the transaction log.
   • One or more for the data.
   
   
   A separate filegroup "readonlyfilegroup" is automatically created for the secondary data file "RemovedbData" and is made the default filegroup for the database. All database objects are automatically placed in this filegroup.
   
   
2. Create the database objects and enter all data. The objects are automatically created on the default filegroup "readonlyfilegroup".
3. Set the option READONLY for the filegroup "readonlyfilegroup" as follows:

   use master 
   go
   ALTER DATABASE RemovableDB MODIFY FILEGROUP readonlyfilegroup READONLY
   go

4. Use the following command to verify that the database is configured properly for distribution on removable media:

   sp_certify_removable 'RemovableDB'
   go

5. The database is now placed offline. Now, you can copy the three database files to a removable media such as a CD-ROM for distribution.

Attaching a Removable Database

If you do not plan to perform any management tasks, like adding users or granting permissions, all three database files can remain on the removable media. Otherwise, you need to copy the primary data file and the log file to the local hard disk drive. This example assumes that you have copied both files to the SQL Server directory on the local hard disk drive to D:\Mssql\Data and that the secondary data file remains on the removable media (drive E:\).

1. Make sure that the file attribute "read only" is not set for the primary data file and the log file on the local hard disk drive.
2. Attach the removable database to your SQL Server installation as follows:

   sp_attach_db 'RemovableDB', 
       'D:\MSSQL\DATA\remove.mdf', 
       'e:\remove.ndf', 
       'D:\MSSQL\DATA\remove.ldf'
   go

3. Add users and grant permissions using the SQL Enterprise Manager or the Transact-SQL commands SP_ADDUSER and GRANT.
4. Users are now able to access the database for read-only activity.