Specifying the Recovery Order

Users can specify the order in which databases are recovered, for all or a subset of user databases.

You can use sp_dbrecovery_order to configure more important databases to be recovered earlier. You must be in the master database and have system administrator privileges to use sp_dbrecovery_order to enter or modify a user-defined recovery order. Any user, in any database, can use sp_dbrecovery_order to the user-defined recovery order of databases. See Reference Manual: Procedures.

sp_dbrecovery_order has an additional parameter indicating the online ordering.

sp_dbrecovery_order [database_name [, rec_order [, force [ relax | strict ]]]]
  • relax – the databases are made as they recover (default).

  • strict – the databases are specified by the recovery order.

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

Recovery order must be consecutive, starting with 1. You cannot assign a recovery sequence of 1, 2, 4, with the intention of assigning a recovery order of 3 to another database at a later time.

To insert a database into a user-defined recovery sequence without putting it at the end, enter rec_order and specify force. For example, if databases A, B, and C have a user-defined recovery order of 1, 2, 3, and you want to insert the pubs2 database as the second user database to recover, enter:
sp_dbrecovery_order pubs2, 2, force

This command assigns a recovery order of 3 to database B and a recovery order of 4 to database C.

SAP ASE 12.5.1 and later uses parallel recovery tasks to determine the next database to recover according to the user-specified order. The remaining databases are recovered in the order of their database IDs. The time to recover a database is dependent on many factors, including the size of the recoverable log. Therefore, although you determined the recovery order with sp_dbrecovery_order, SAP ASE may complete the database recovery in an order other than which it started. For applications that must enforce that databases are brought online in the same order as the recovery order, SAP ASE provides the strict option in sp_dbrecovery_order.