sp_devoption System Stored Procedure

Displays or sets device status. Although primarily used on removable media devices, this procedure can be used for all devices.

This stored procedure is used internally (by other system stored procedures) and is not typically used by users.

Syntax

sp_devoption [devicename [, optname {, true | false} [, override]]]

where

devicename

Is a device name.

optname

Is the name of the option you want to turn on or off. Can be:

deferred: When set to true, indicates that the device is not to be automatically opened at server startup time. Setting deferred to true can be useful when you create a new device and will not immediately place a database on it.
'read only': When set to true, indicates that the device is read only.

true | false

Specifies how to set the optname option.

override

When supplied, allows you to set a device that contains read/write databases to read only. Can only be used when optname is 'read only'. If this option is not supplied, read only devices can only contain read only databases.

Remarks

If no parameters are specified, a list of the settable options is displayed.

Permission

Execute permission defaults to the system administrator.

Tables Used

master.dbo.sysdatabases, master.dbo.sysusages, master.dbo.spt_values, master.dbo.sysdevices

Examples

A. Set Device to Not Open at Startup

This example sets the device DEV1 so that it will not be opened automatically at server startup.

sp_devoption DEV1,deferred,true

B. Set Device to Be Read Only

This example sets the device DEV1 to be read only.

sp_devoption DEV1, readonly, true