Specifies whether or not a database device can be used for database storage if the user does not specify a database device or specifies default with the create database or alter database commands.
sp_diskdefault logicalname, {defaulton | defaultoff}
is the logical name of the device as given in master.dbo.sysdevices.name. The device must be a database device rather than a dump device.
defaulton designates the database device as a default database device; defaultoff designates that the specified database device is not a default database device.
Use defaulton after adding a database device to the system with disk init. Use defaultoff to change the default status of the master device (which is designated as a default device when Adaptive Server is first installed).
The master device is no longer used by create database or alter database for default storage of a database:
sp_diskdefault master, defaultoff
A default database device is one that is used for database storage by create database or alter database if the user does not specify a database device name or specifies the keyword default.
You can have multiple default devices. They are used in the order they appear in the master.dbo.sysdevices table (that is, alphabetical order). When the first default device is filled, the second default device is used, and so on.
When you first install Adaptive Server, the master device is the only default database device.
Once you initialize devices to store user databases,
use sp_diskdefault to turn off the master
device’s default status. This prevents users from accidentally
creating databases on the master device and simplifies recovery
of the master database.
To find out which database devices are default database devices, execute sp_helpdevice.
Only a System Administrator can execute sp_diskdefault.
Commands alter database, create database, disk init
System procedures – sp_helpdevice
