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]]]
The name of the database being assigned a recovery order or the database whose user-defined recovery order is to be listed.
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.
allows the user to insert a database into an existing recovery sequence without putting it at the end.
Makes the pubs2 database the first user database to be recovered following a system failure:
sp_dbrecovery_order pubs2, 1
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
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
Lists the current recovery order of all databases with a recovery order assigned through sp_dbrecovery_order:
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:
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.
If database_name 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.
Only a System Administrator can execute sp_dbrecovery_order.
