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


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




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.


With granular permissions enabled, you must be a user with manage server privilege


With granular permissions disabled, you must be a user with sa_role. .


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



Audit option


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