sp_certify_removable (T-SQL)

Verifies that a database is configured properly for distribution on removable media and reports any problems to the user.

Syntax

sp_certify_removable [@dbname =] 'dbname' [, [@autofix =] 'auto']

Arguments

[@dbname =] 'dbname': Specifies the database to be verified. dbname is sysname.
[@autofix =] 'auto': Gives ownership of the database and all database objects to the system administrator, and drops any user-created database users and any nondefault permissions. auto is nvarchar(4), with a default of NULL. auto has the value auto.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

If the database is configured properly, sp_certify_removable sets the database offline so the files can be copied. It updates statistics on all tables and reports any ownership or user problems. It also marks the data filegroups as read-only so these files can be copied to read-only media.

The system administrator must be the owner of the database and all database objects. The system administrator is a known user that exists on all servers running Microsoft® SQL Server™ and can be counted on to exist when the database is later distributed and installed.

If you run sp_certify_removable without the auto value and it returns information indicating that the system administrator is not the database owner, that user-created users exist, that the system administrator does not own all objects in the database, or that nondefault permissions have been granted, you can correct those conditions in two ways:

Use SQL Server tools and procedures, and then run sp_certify_removable again.
Simply run sp_certify_removable with the auto value.

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, see GRANT.

This procedure writes verification information to a text file that has the following file name format:

CertifyR_[dbname].txt

Note The permissions on xp_cmdshell must permit this file write.

Permission

EXECUTE permissions are restricted to members of the sysadmin fixed server role.

Example

This example certifies that the inventory database is ready to be removed.

sp_certify_removable inventory, AUTO

sp_attach_db	sp_dbremove
sp_create_removable	System Stored Procedures
sp_dboption