Identify root cause for why database is stuck in RESTORING state

Question

I know there are questions which solve the issue of a database being stuck in RESTORING state and have used those solutions to manually bring the database back online, but my scenario is somewhat different.

I have an automated restore using Powershell scripts which restores a copy of production to a DEV instance. The scripts are unchanged for about a year and occasionally the restore process finishes but the restored database is stuck in RESTORING state (sometimes the script works fine, sometimes it fails like this).

Each time if I manually re-run the process it works or if I manually restore the database from SSMS's user interface or through T-SQL it completes without a problem.

I've found answers which recommended to run CHECKDB on the restored DB but nothing came out as a cause for this problem.

Since the restore scripts restore a FULL backup of the database and uses a "WITH RECOVERY" option I am trying to find out what might be stopping the restore process, although I'm actually restoring it using "WITH RECOVERY".

Any suggestions are really appreciated since I'm stuck at trying to understand why this is happening from time to time.

I'd very much like to solve the root cause of the issue rather than treat the symptoms, which is to manually restore the DB once again.

Update:

Github Gist as @Brent recommended - here.

Answer 1

Occam's razor suggests starting with the obvious:

If your script sometimes leaves a database in restoring state, then debug the script.

Start by logging what you're doing in a table or a file. Then, when you end up with the database in restoring state, step back through your logs to see what went wrong. (If you want a second set of eyes from the community, try uploading your script as a Github Gist, but keep in mind that the bigger it is, the harder it might be for folks to spot bugs.)

Update: I see that you posted a Github Gist with an error message. That's not what I asked for: I said, log what YOU'RE doing, not what SQL Server is doing. Log what actions you took in order to get the database to the broken state.

If you don't wanna do that, then try running a Profiler or Extended Events trace to track the restore events, but be warned - it's way harder than it looks. (Read the comments on that post for even more reader ideas that they've tried and failed.)