Lesson 2: Setting Database Options

3 4

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.

After this lesson, you will be able to

  • Describe the database options that you can set
  • View the database settings
  • Modify database settings

Estimated lesson time: 15 minutes

Introducing Database Options

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:

  • Control of certain automatic behaviors (such as automatically creating statistics, automatically updating statistics, and automatically shrinking database files)
  • Cursor behavior and scope (such as local or global)
  • Recovery options (Full, Bulk-Logged, or Simple)
  • ANSI compliance options (such as ANSI nulls and quoted identifiers)
  • State options (such as read-only and dbo access only)

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.

Viewing Database Option 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.

 figure 6.14 - viewing the database option settings.

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.


Modifying Database Options

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.



The sp_dboption system stored procedure can also be used to display or change certain database option settings. However, this system stored procedure is only supported for backward compatibility with previous versions of SQL Server. It does not allow you to view (or set) options that are new to SQL Server 2000.

Lesson Summary

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.

MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
Year: 2001
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net