The recovery process for user-created temporary databases differs significantly from that of regular databases.
There is no difference in runtime-undo rollbacks between the system tempdb and user-created temporary databases.
The restart recovery process is similar to that for tempdb. A user-created temporary database is created using the entries in sysusages, and the model database is copied over. All user-created objects that existed in a temporary database before shutting down are lost.
Temporary databases are recovered in the order in which they appear in sysdatabases. Use sp_dbrecovery_order to specify an alternate recovery order.
Sessions that log in before the recovery of the temporary database that they would normally be assigned to are assigned to another temporary database, unless a hard binding exists—in which case the login fails. To minimize this impact, use sp_dbrecovery_order to specify that all or some of the temporary databases can be recovered before the rest of the user databases.
Upon successful recovery, a temporary database is added to the global list of available temporary databases, as well as to the default group, if bound to it.
The creation date and time for the temporary database entry in sysdatabases reflects the time that the database was re-created. This date is updated at the time of recovery every time the temporary database is re-created.
The model database is locked while the system is under recovery so that it can get copied over to the temporary database.
Since model is locked, you cannot
create a new database or use use model until
the recovery process has completed.
