sp_diskdefault System Stored Procedure

Sets a database device's status to indicate whether the device can be used for database storage when the user does not specify a database device or specifies DEFAULT with the CREATE DATABASE or ALTER DATABASE statements.

Syntax

sp_diskdefault database_device, {defaulton | defaultoff}

where

database_device

Is the logical name of the database device (not a dump device).

defaulton | defaultoff

Indicates whether the specified database device is the default. Use defaulton if the database device can be a default database device. Use defaultoff if the database device cannot be a default database device. The defaulton option will most frequently be used after a database device has been added to the system by the DISK INIT statement. The defaultoff option will most frequently be used to change the default status of the MASTER database device (which is the designated default when SQL Server is initially installed).

Remarks

You can have multiple default devices. They are used in alphabetic order, the order in which they appear in the sysdevices table.

When SQL Server is initially installed, the MASTER database device is the only default database device.

To find out which database devices are default database devices, execute the sp_helpdevice system stored procedure.

Example

In this example, the MASTER database device will no longer be used by the CREATE DATABASE or ALTER DATABASE statements for the default storage of a database.

sp_diskdefault master, defaultoff

Permission

Only the system administrator can use this procedure.

Table Used

master.dbo.sysdevices

See Also