November 11, 2016 at 7:00 am
I have a SQL instance that had the underlying SAN storage worked on last night. All the databases except one came back online when the server came up. It shows in a restoring state. SQL Server Error Log says, "The database 'myDB' is marked RESTORING and is in a state that does not allow recovery to be run." The mdf and ldf files are present so I know I am not missing a file on the drive. It is a small database of about 84 GB.
If I run RESTORE MYDB WITH RECOVERY I get the following error.
"The database cannot be recovered because the log was not restored."
What is my next step? What things do I need to check?
November 11, 2016 at 7:15 am
This is a data warehouse SQL Server instance and there is a nightly job that runs at 9 pm. Part of what it does is restore several databases, one of which is the one that is hung in a restoring state. I am in my 4th week in this environment and did not know there was a job that did this. The Job Activity Monitor shows the job was cancelled, apparently during the restore of this database, which was the last step of the job. I am just re-running the restore to resolve.
November 11, 2016 at 7:36 am
How long has it been in that state? If you do an sp_who2 can you see any system processes going on? I had one of these last week, and it took hours to come back up, so it might just be a matter of time.
November 11, 2016 at 8:17 am
lmarkum (11/11/2016)
This is a data warehouse SQL Server instance and there is a nightly job that runs at 9 pm. Part of what it does is restore several databases, one of which is the one that is hung in a restoring state. I am in my 4th week in this environment and did not know there was a job that did this. The Job Activity Monitor shows the job was cancelled, apparently during the restore of this database, which was the last step of the job. I am just re-running the restore to resolve.
If a restore job is cancelled and you do not know at which point it was cancelled, a restart of the complete restore process is the fastest way to recover. If you do know at which point the restore was cancelled (query the [msdb]..[restorehistory] and [msdb]..[backupset] tables) you could continue from there.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply