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
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.
Github Gist as @Brent recommended - here.