In addition to the properties of each data file and transaction log file in a user database, you can set a number of database options that determine various characteristics of a user database. In this lesson, you will be introduced to these settings and learn how to view and modify these settings for an existing database, including the model database.
Database option settings for a database determine various default behaviors for the database. These settings for a user database are inherited from the model database when the user database is created. Database options fall into five categories:
In most environments, you will rarely modify more than a few of these settings. Setting the recovery model for a database and restricting the level of user access to a database are the settings that you will change periodically. Recovery settings were covered in Chapter 5 and will also be covered in Chapter 8. You restrict access to a user database to perform various administrative tasks (such as restoring a database) or to create a read-only database. Options include setting a database to read-only, allowing only database owners and members of the dbcreator and sysadmin server roles to connect to the database, and setting a database to allow only a single user to connect. Refer to the Setting Database Options topic in SQL Server Books Online for full details on other settings.
You view the current settings for database options using SQL Server Enterprise Manager or using the DATABASEPROPERTYEX system function. SQL Server Enterprise Manager displays the most commonly modified settings. To view them, right-click a database, click Properties, and then click the Options tab. See Figure 6.14.
Viewing the database option settings.
To view the current recovery model for the TSQLDB database using the DATABASEPROPERTYEX system function, use the following statement in SQL Query Analyzer.
SELECT DATABASEPROPERTYEX ('TSQLDB', 'RECOVERY')
You set the most common database options by clicking a check box in SQL Server Enterprise Manager, or you can set any database option using the ALTER DATABASE Transact-SQL statement. For example, to set the TSQLDB database to Bulk-Logged Recovery model using the ALTER DATABASE statement, use the following statement.
ALTER DATABASE TSQLDB SET RECOVERY BULK_LOGGED
Database options control the default behavior of a database. These settings are inherited from the model database. In general, change only the database recovery setting or restrict access to a database to perform administrative tasks or create a read-only user database. To view the current settings of a database, use SQL Server Enterprise Manager or the DATAPROPERTYEX system function. To change these settings for all new databases, change the database option settings for the model database. To change these settings for an existing database, use SQL Server Enterprise Manager or the ALTER DATABASE Transact-SQL statement.