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

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

• You must be in the master database to use sp_dbrecovery_order to enter or modify a user-specified recovery order. You can list the user-defined recovery order of databases from any database.

• To change the user-defined recovery position of a database, use sp_dbrecovery_order to delete the database from the recovery sequence, then use sp_dbrecovery_order to insert it into a new position.

• System databases are always recovered before user databases. The system databases and their recovery order are:

  • master

  • model

  • tempdb

  • sybsystemdb

  • sybsecurity

  • sybsystemprocs

• If no database is assigned a recovery order through sp_dbrecovery_order, all user databases are recovered in order, by database ID, after system databases.

• If database_name:

  • Is specified but no rec_order is given – sp_dbrecovery_order shows the user-defined recovery position of the specified database.

  • Is not specified – sp_dbrecovery_order lists the recovery order of all databases with a user-assigned recovery order.

• The order of recovery assigned through sp_dbrecovery_order must be consecutive, starting with 1 and containing no gaps between values. The first database assigned a recovery order must be assigned a rec_order of 1. If three databases have been assigned a recovery order of 1, 2, and 3, you cannot assign the next database a recovery order of 5.

Permissions

The permission checks for sp_dbrecovery_order differ based on your granular permissions settings.

Auditing

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