Hung Database Restore

When doing a deployment today, the build script errored out with a SQL timeout.

C:\Projects\MyProject.proj (2374,3): error: PROJDB: Error # -2 on Line 0: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

The line in the msbuild script was executing a backup/restore script to refresh the database for one of our teams. I decided to change the StatementTimeout for the ExecuteDDL task and try again. This time I received another error.

C:\Projects\MyProject.proj (2374,3): error: PROJDB: Error # 4060 on Line 65536: Cannot open database "PROJDB" requested by the login. The login failed.

C:\Projects\MyProject.proj (2374,3): error: PROJDB: Error # 18456 on Line 65536: Login failed for user 'DOMAIN\BuildUser'.

Why would my database login be messed up? I jumped onto the database server through Management Studio and discovered that the database was stuck in the Restore state. I waited for a while to see if it would go away, but it became clear that it was hung. I googled around to see what needed to be done to fix it. Someone suggested that running the following command would take it out of that state: RESTORE DATABASE <dbname> WITH RECOVERY. It didn't. Instead, I received an error that you can't alter the database while it is being restored.

I finally solved the problem by deleting the database, adding a new database with the same name back, then running the restore script.