sp_dbrecovery_order

Description

Specifies the order in which user databases are recovered and lists the user-defined recovery order of a database or all databases.

Syntax

sp_dbrecovery_order [database_name [, rec_order [, force [ relax | strict ]]]]

Parameters

database_name

The name of the database being assigned a recovery order or the database whose user-defined recovery order is to be listed.

rec_order

The order in which the database is to be recovered. A rec_order of -1 deletes a specified database from the user-defined recovery sequence.

force

allows the user to insert a database into an existing recovery sequence without putting it at the end.

relax

specifies that the databases are made as they recover (default).

The default is relax, which means that databases are brought online immediately when recovery has completed.

strict

specifies that the databases are specified by the recovery order.

Examples

Example 1

Makes the pubs2 database the first user database to be recovered following a system failure:

sp_dbrecovery_order pubs2, 1

Example 2

Inserts the pubs3 database into third position in a user-defined recovery sequence. If another database was initially in third position, it is moved to fourth position, and all databases following it are moved accordingly:

sp_dbrecovery_order pubs3, 3, force

Example 3

Removes the pubs2 database from the user-defined recovery sequence. Subsequently, pubs2 will be recovered after all databases with a user-specified recovery order have recovered:

sp_dbrecovery_order pubs2, -1

Example 4

Lists the current recovery order of all databases with a recovery order assigned through sp_dbrecovery_order:

sp_dbrecovery_order

Usage

Permissions

Only a System Administrator can execute sp_dbrecovery_order.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect