Setting Up Default Database Devices

SQL Server maintains a pool of one or more devices that are set as default devices. If you don't specify a database device when you create a database, that database is placed on a default database device selected by SQL Server. Default database devices are used in alphabetical order, and one is filled before another one is used. Only database devices can be default devices. (Dump devices cannot be set as default devices.)

When SQL Server is installed, the MASTER device is the only default database device. You should remove MASTER as a default database device and set up a number of default database devices for users to create databases on.

When creating a device, you have the option of making the new device a default device. When editing an existing device, you have the option of changing its default status.

For example, to use SQL Enterprise Manager to remove the MASTER device from the pool of default devices, you would perform the following procedure.

    To remove the MASTER device from the pool of default database devices

1. In the Server Manager window, select a server, open the server's Database Devices folder, and then double-click the MASTER device.

   The Edit Database Devices window appears.

   

2. Clear the Default Device checkbox.
3. Choose the OK button.

    To add a device to the pool of default database devices

1. Open the Edit Database Devices window for the database device.
2. Select the Default Device checkbox.
3. Choose OK.

Note You should dump the master database after setting or clearing default status for any device.

You can also add a database device to or remove it from the pool of default devices by using the sp_diskdefault system procedure. For more information, see the Microsoft SQL Server Transact-SQL Reference.

Setting a default database device marks the device in the sysdevices table as a default database device.