10

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.

  • 1
    You can get the answer from the SQL Server logs and event viewer, did you checked ?. Is there somehow space issue happening because that may cause the restoring state to get stuck. Do you verify backup integrity before restoring ? How is your storage subsystem – Shanky Oct 2 '17 at 14:21
  • @Shanky I'll have a look at those, although I don't have direct access to that information (I need to get acquianted with my SAN admin). Backup integrity is set to be checked both when backing up and when writing to disk. – Radu Gheorghiu Oct 2 '17 at 15:21
  • 1
    What does the SQL Server errorlog say? Try running EXEC sys.xp_readerrorlog 0,1; - look for messages around the time of the restore operation. – Max Vernon Oct 2 '17 at 15:53
  • @MaxVernon The error log from the time the restore happened. I've checked the error message and it seems to recommend checking disk space, which is plenty. I'll dig deeper and see what I can find, but on a quick look this could expand quite a lot and would have me looking at a lot of things. – Radu Gheorghiu Oct 3 '17 at 9:18
8

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.)

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.)

  • Thanks Brent, these are some good guidelines! I'll be back with an update when I have more info, for the moment the DB's fine, I'll see what happens when the process runs again tomorrow morning. – Radu Gheorghiu Oct 2 '17 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.