Working in a diverse environment where backup and restores are handled by a different department sometimes small problem can rise into huge monsters if we overlook the details. A recent problem I ran across consisted of restore job where the databases appeared as stuck in loading stage,but not fully functional. After a couple of attempts to restore the database what started as one failed restore turned into three databases all in a loading state. The normal steps of action included dropping the table, but every time I tried the SQL server responded with a message that the database was loading and could not be deleted.
After much research and hitting a couple of brick walls. A next tried to think about this particular problem from a perspective of an isolated database instance without any mitigating factors. I tried a couple of options, which in this case were not successful, but might work under different circumstances.
First, I tried to restore an existing database onto the loading database. This failed again with the error indicating I could perform the action because of loading database.
Next, I spoke to other system administrators and reasoned that in fact the database had been restored with the ‘norecovery’ option. This is by design to put the database into this loading state. It assumes that you will be restoring later transactional file backups on top of the full restore just performed. In fact since we didn’t have those transaction backups this left me rather confused. I again consulted my co-workers and they indicated that I should again try a restore but the the ‘recovery’ option set.
I opened the SQL query manager and ran the following code:
restore database <DATABASE> with recovery
Once I executed this code the database came online and then in turn I could delete the unneeded databases.