Why does restoring a database needs TempDB?

Or the chicken and egg problem when attempting to restore a corrupted model database as it was presented by my MCM colleague Gail Shaw the other day in our MCM discussion group.

This is the case he presented us with:

Hi all

Something I’ve been puzzling over recently. Restore database appears to need or use TempDB right at the beginning of the restore sequence. Does anyone happen to know why?

Scenario that shows this:

SQL instance with a badly corrupt model (hex edit of the boot page does the job). SQL won’t start normally, but it will with traceflag 3608 (recover only the master database).

Recovering only master database because trace flag 3608 was specified. This is an informational message only. No user action is required.

Starting up database 'master'.

Recovery is complete. This is an informational message only. No user action is required.

If you then connect to that instance and try to restore a backup of model, this is what appears in the error log (I had traceflag 3212 on as well to print out backup stats, and nothing printed, hence why I suspect it was the beginning of the restore that needs TempDB)

Clearing tempdb database.

Starting up database 'model'.

Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files onthe tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

SQL shuts down at this point

Hence you can’t just restore a backup of model if the DB files are missing or badly corrupt. I suspect the best approach would be to replace the files with copies from another SQL instance of the same version or rebuild the system databases and then restore. Seems a little more complex than desirable.

Thanks

Gail

And this is what I found as the reason why it behaves that way:

When the server is started under default settings (no startup trace flags enabled), SQL Server calls one function which starts (recovers & opens) both MODEL (first) and TEMPDB (second). If either of those two databases cannot be recovered, the server cannot start as you’ve noticed and described here.

Now, if trace flag 3608 (Do not recover any databases except master) or 3607 (Do not recover any databases including master) are enabled upon startup of the service, that function is not called. So neither model nor tempdb are eagerly started.

What happens when you try to restore model (or any database for that matter) is that the method which restores all the files in the database and performs recovery to bring the database to a consistent state, prior to allowing any restore activity must ensure the server is fully started. This is implemented by waiting for tempdb to be recovered. But tempdb is NOT recovered when these special trace flags (3607/3608) are used. Even though this method is written so that  if it fails to obtain the tempdb reference, it should continue with the restore, it cannot continue because in this case obtaining the tempdb reference doesn’t return what is understood as a failure but is more drastic than that, it shuts the service down. Why? Let me continue explaining what happens down that path.

In order to get that needed reference to tempdb it must open tempdb, which in turn needs to access model to retrieve model’s size and other attributes to create a fresh new instance of tempdb. Because your model is not accessible it throws an exception which is caught by a handler that decides that the situation is severe enough to bring the service down.

Now, if tempdb is in a recoverable state after the previous service shutdown, you could have also (in addition to one f the previously mentioned flags) enabled trace flag 3609 (Do not recreate tempdb, try to recover it as is) so that you avoid the recreation of tempdb files, therefore avoiding any accesses to model as pre-requisites to open tempdb. And with that, you could have restored model from a proper backupset.