SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Restore
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dbalearner
Constraint Violating Yak Guru

262 Posts

Posted - 11/01/2013 :  07:31:49  Show Profile  Reply with Quote
I have database in production and test on the same server.

From Production Copy backed up and trying to Restore onto TEST Database.

Restore is failed because Database is in Use

Restore Failed for Server (SVR01)

Exclusive Access Could Not be obtained because database is in use

I have set the Database in SINGLE USER Mode

USE MASTER;
ALTER DATABASE [DBNAME] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Again tried restore but same Message as above.

Never come across such problem and tried all the WEB provided help but couldn't helped much

Any Masterly solution for this much thankful.

madhivanan
Premature Yak Congratulator

India
22760 Posts

Posted - 11/01/2013 :  08:06:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Some users are accessing the database. Run sp_who to know and kill spids.

Use master
restore script

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 11/01/2013 :  08:07:10  Show Profile  Reply with Quote
quote:
I have set the Database in SINGLE USER Mode

USE MASTER;
ALTER DATABASE [DBNAME] SET MULTI_USER WITH ROLLBACK IMMEDIATE;


shouldn't you set to SINGLE_USER rather than MULTI_USER ?


KH
Time is always against us

Go to Top of Page

dbalearner
Constraint Violating Yak Guru

262 Posts

Posted - 11/01/2013 :  08:21:55  Show Profile  Reply with Quote
I have set to SINGLE USER only and tried RESTORE.

Also checked any user connected as well and killed all before continuing Restore.

Both ways unsuccessful.

Any further help please.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22760 Posts

Posted - 11/01/2013 :  10:07:48  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Have you tried my method?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

dbalearner
Constraint Violating Yak Guru

262 Posts

Posted - 11/01/2013 :  12:44:54  Show Profile  Reply with Quote
yes I have tried but unawares of what is happening?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 11/02/2013 :  05:51:22  Show Profile  Reply with Quote
how do you restore the database ?

where did you execute the restore command ? is it in master as madhivanan suggested ?


KH
Time is always against us

Go to Top of Page

dbalearner
Constraint Violating Yak Guru

262 Posts

Posted - 11/20/2013 :  07:41:48  Show Profile  Reply with Quote
Thanks all but it is the user through application holds the connection then kill the spid but later the restore under Master couldn't get through.

Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
797 Posts

Posted - 11/20/2013 :  14:32:09  Show Profile  Reply with Quote
To prevent access during your restore, you can set the database offline...

ALTER DATABASE ... SET OFFLINE WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE ...

Because you have both databases available on the same server/instance - you need to make sure you are changing the database name and file names. You need to include the WITH MOVE option for each file and the REPLACE option to overwrite the existing database.

Perform with caution, you don't want to accidentally start a restore of the production database.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000