Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
 Posted - 2013-11-01 : 07:31:49
|
| 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 UseRestore Failed for Server (SVR01)Exclusive Access Could Not be obtained because database is in useI have set the Database in SINGLE USER ModeUSE 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 muchAny Masterly solution for this much thankful. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-11-01 : 08:06:30
|
| Some users are accessing the database. Run sp_who to know and kill spids. Use masterrestore scriptMadhivananFailing to plan is Planning to fail |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-11-01 : 08:07:10
|
quote:
I have set the Database in SINGLE USER ModeUSE MASTER;ALTER DATABASE [DBNAME] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
shouldn't you set to SINGLE_USER rather than MULTI_USER ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2013-11-01 : 08:21:55
|
| 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. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-11-01 : 10:07:48
|
| Have you tried my method?MadhivananFailing to plan is Planning to fail |
|
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2013-11-01 : 12:44:54
|
| yes I have tried but unawares of what is happening? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-11-02 : 05:51:22
|
how do you restore the database ?where did you execute the restore command ? is it in master as madhivanan suggested ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2013-11-20 : 07:41:48
|
| 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. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-11-20 : 14:32:09
|
| 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. |
|
|
|
|
|
|
Restore