Specifies database options that you set when you created the database. For example, you must select the Single User option before you perform any database tests. You must clear this option when the tests are completed.

To open this window, on the File menu, choose Database, choose Alter, and then choose the Options tab.

Access Section

Field Description

Single user

Specifies that only one user can access the database at a time. You can use this setting when you are performing administrative functions such as testing or restoring the database. By limiting access to the database to one user, you make sure that the database is not changed when you are testing it.

Important
Clear this check box when you are finished to give other users to access the database.

Settings Section

Field Description

Recovery Model

Determines the kind of information that is written to the transaction log and therefore the kind of recovery model that you want to use in this database.

Note
The Full and Bulk-logged recovery models are similar, and many users of the Full recovery model will use the Bulk-logged recovery model occasionally.

Option Description

Bulk-logged

The transaction log will contain only limited information about certain large-scale or bulk copy operations. The Bulk-logged recovery model provides protection against media failure combined with the best performance and the minimal use of log space for certain large-scale or bulk copy operations.

The backup strategy for bulk-logged recovery consists of:

  • Database backups.
  • Differential backups (optional).

Full

The details of every transaction are stored in the transaction log. This information can be used when you apply transaction log backups. The Full recovery model uses database backups and transaction log backups to provide complete protection against media failure. If one or more data files are damaged, media recovery can restore all the committed transactions. Incomplete transactions are rolled back.

Full recovery lets you recover the database to the point of failure or to a specific point in time. All operations are fully logged to guarantee that the database is recoverable. This includes bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data.

The backup strategy for full recovery consists of:

  • Database backups.
  • Differential backups (optional).
  • Transaction log backups.

Simple

The database can be recovered to the point at which the last backup was made. However, you cannot restore the database to the point of failure or to a specific point in time. To do that, select either the Full or Bulk-logged recovery model.

The backup strategy for simple recovery consists of:

  • Database backups.
  • Differential backups (optional).

ANSI NULL default

Specifies whether the database default NULL settings for column definitions and user-defined data types are to be applied. When you select this option, all user-defined data types or columns that have not been explicitly defined as NOT NULL are set to allow NULL entries. Columns that have been defined by using constraints follow the constraint rules, regardless of this setting.

Recursive triggers

Specifies recursive trigger settings. Triggers can have direct recursion or indirect recursion. Direct recursion occurs when a trigger occurs and performs an action that causes the same trigger to be fired again. Indirect recursion occurs when a trigger occurs and performs an action that causes a trigger on another table to occur. This second trigger updates the original table which causes the first trigger to occur again.

Torn page detection

Enables SQL Server to detect incomplete input/output operations that have been caused by power failures or other system outages.

Auto shrink

Specifies whether SQL Server can periodically shrink data files and transaction log files.

See Also