July 28, 2011 at 2:20 pm
Hello,
I am trying to refresh test server, which has old data.
I have a backup of user and sys dbs on prod server. I copied those files on E: of test server. All the dbs names and sys dbs are same. Now when I am trying to restore user db on test server from its E: (where I copied backups from prod server) It is gives me following errors:
1. Exclusive access could not be obtained.
2. Couldn't restore as db is in use.
Please advice. Thanks
July 28, 2011 at 2:46 pm
That means you're trying to overwrite an existing database, while it's in use.
You'll need to take it offline, or set it to single-user and use that credential to run the restore.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 28, 2011 at 3:10 pm
well it is a test server and not in use. So didn't take it offline nor put it in single user mode.
My Q is that the weekly backup used for restoring is of prod server. weekly backup from Sunday. Is it in use? I didn't think so, Am I rt? Please advice. It is the backup and not the actual prod db
July 28, 2011 at 3:38 pm
tracmonali (7/28/2011)
well it is a test server and not in use. So didn't take it offline nor put it in single user mode.My Q is that the weekly backup used for restoring is of prod server. weekly backup from Sunday. Is it in use? I didn't think so, Am I rt? Please advice. It is the backup and not the actual prod db
"in use" meaning there is a connection open to the live database on the test server, and you cannot overwrite a database that it "in use".
Put the database on the test server into single_user mode with rollback immediate to boot everyone out, then run your restore command with replace.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 29, 2011 at 3:30 pm
Use Master
Alter Database [insert db name here]
SET SINGLE_USER With ROLLBACK IMMEDIATE
RESTORE DATABASE [insert db name here] FROM DISK = 'E:\backup\insert db backup name here.bak'
WITH REPLACE
GO
Tweak as needed.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
August 1, 2011 at 4:50 pm
Thank you very much for replies. It helped.
My question is after running the queries, how do I bring back to multi user mode? Does it stay in Single user mode?
August 1, 2011 at 5:01 pm
Nope, you can flip the mode back and forth:
ALTER DATABASE [insert db name here] SET MULTI_USER ;
More info...see example J:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 2, 2011 at 8:48 am
Thanks for all the replies. My issue is resolved. It helped
August 2, 2011 at 9:00 am
You're welcome, HTH 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply