Certifying a Removable Media Database

When database development is complete, use the sp_certify_removable stored procedure to prepare the database for distribution on removable media. This stored procedure checks that there are no user-created objects in the database, no permissions granted to users, that the SA is the DBO and the owner of all database objects, and that all fragments are contiguous and in sequence. It also truncates the transaction log, moves it to the system device, drops the log device, and sets the database to offline.

The SA must be owner of the database and all database objects because SA is a known user who exists on all SQL Servers and can be counted on to exist when the database is later distributed and installed.

    To certify that a database is configured properly for distribution on removable media

• Type:

  sp_certify_removable dbname[, AUTO]

  where

  dbname

  Is the name of the database to be verified.

  AUTO

  As necessary, gives ownership of the database and all database objects to the SA, and drops any user-created database users and any permissions they may have.

For example:

sp_certify_removable inventory

If the preceding indicates, for example, that user-created users exist in the database, you could then issue the stored procedure by using the AUTO option:

sp_certify_removable inventory,AUTO

If the database is configured properly, this stored procedure sets the database offline and returns information that will be required when the database is later installed using sp_dbinstall. It returns the physical and logical names and the size in megabytes of the system and data devices. Make a note of this information, and provide it¾along with the distribution disk¾to users who will be installing the removable media database.

If you run this stored procedure without the AUTO option and it returns information indicating that the SA is not the DBO, that user-created objects exist, that the SA does not own all objects in the database, or that non-default permissions have been granted, then you have two choices. You can correct those conditions normally, using SQL Server tools and procedures, and then run sp_certify_removable again, or you can simply run sp_certify_removable by using the AUTO option.

Note that this stored procedure only checks for users and user permissions. It is permissible to add groups to the database and to grant permissions to those groups.

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

Once the database has been certified, it can be copied, and then that master copy can be reproduced for distribution. Before reproducing the master copy onto distribution disks, you can compare files to ensure that the master copy is identical to the original. For example, to use the fc utility in Windows NT to compare the device files in two directories, you could type:

fc  c:\sql60\data\remove\*.dat  e:\manu\*.dat

For more information about the fc utility, see your documenatation for Windows NT.

When the database is distributed, be sure to include the information returned by sp_certify_removable (the physical and logical names and the size, in megabytes, of the system and data devices).