December 20, 2013 at 11:47 am
Good afternoon experts,
I had a problem that has been resolved, but I am trying to figure out what happened.
We lost some data in a production table, so I needed to restore the production backup to the development server. The backup was done in CommVault using their SQL client. Both the production DB and the dev version were in “simple” mode. The backup was 775 GB. The DB had 5 data files and a log file. The drives and file locations were set up identically. Production was SQL 2008 Enterprise and dev was SQL 2008 Developer.
We ran the restore 5 times and each time it stuck in restore mode. I tried running RESTORE DATABASE <dbname> WITH RECOVERY, but it would throw an error “The database cannot be recovered because the log was not restored.”
CommVault threw the following errors: “Description: Query Result [Exclusive access could not be obtained because the database is in use.” (4 times) and “The database cannot be recovered because the log was not restored. “(1 time).
Three of these times I completely blew away the database and started from scratch. These failures all occurred after 6-10 hours after all of the files were replaced.
I initiated a call with CommVault and verified that I was in fact setting everything up correctly. On a whim, the engineer suggested we try restoring the database as another name and that worked.
I am trying to figure this out for future reference as they want to go to a regular refresh schedule.
The only thing I can figure is that there are users (services) persistently trying to make connections. Perhaps I should disable these users before I try it again? I am just guessing.
We did not have this issue a couple of weeks ago when we had to restore production. We have the same persistent service accounts over there also, but my restore worked the first time.
Any thoughts?
Mike
“I know that you believe you understand what you think I said, but I'm not sure you realize that what you heard is not what I meant.”...Robert McCloskey
___________________________________________________________________
December 20, 2013 at 4:21 pm
Sorry. Not a comvault person. Do they have a forum? You might post there to see what people say.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 24, 2013 at 12:05 pm
This error: Exclusive access could not be obtained because the database is in use.
Tells you that those services had open connections to the database being restored. When performing a restore of an existing database you need exclusive access to that database. You also need the option REPLACE to overwrite the existing files. Restoring as a different database gets around that issue because there are no open connections.
The easiest way I have found to prevent that issue is to take the database offline before performing the restore. That will disconnect all existing connections and prevent any new connections from being initiated before the restore can begin.
If possible, shut down all services before performing the restore since you don't want those services generating login errors during the restore process.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply