sp_dbrecovery

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 ]]]]

database_name – The name of the database being assigned a recovery order or the database with a user-defined recovery order that 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.

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 is 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
```

There are additional considerations when using sp_dbrecovery_order:

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:
1. master
2. model
3. tempdb
4. sybsystemdb
5. sybsecurity
6. 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.

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

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage server`privilege
Disabled	With granular permissions disabled, you must be a user with sa_role. .

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

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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