| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-03-12 : 07:31:24
|
| Aru writes "I am trying to restore the database with a single user mode.but when I try to restore I get a message that the database is already open.My understanding was that when the database is put into a single mode no users will be allowed to enter into the database.but in my case inspite of locking the users still can get in.if any of you have experienced this problem and has a solution please let me know.Thanks " |
|
|
ralphs
Starting Member
8 Posts |
Posted - 2003-03-12 : 08:53:53
|
| Any one can log into sql server that has permission to do so. When put in single user mode the first user logging in take that session.Look if you have any application that logs into sql srv.Stop the SQL Agent service.Surveillance applications that logs in?///Ralph |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-12 : 12:36:43
|
| Yeah, you have to be very quick to be the first one when you put it in single user mode. If you have a database that is heavily used, then restart the service, then immediately go back in.Tara |
|
|
|
franco
Constraint Violating Yak Guru
255 Posts |
Posted - 2003-03-13 : 08:12:03
|
| Or you can set this command:ALTER DATABASE dbnameSET SINGLE_USER or RESTRICTED USERWITH ROLLBACK IMMEDIATE SINGLE_USER only one user at a time can access the database. RESTRICTED_USER only members of the db_owner, dbcreator, or sysadmin roles can use the database.Franco |
|
|
|
aru
Starting Member
1 Post |
Posted - 2003-03-20 : 12:20:55
|
| the database is heavily used and it is 24/7.does killing all the logged in users before locking helps?is there any drawback with that?Thanks for all your advice. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-20 : 12:40:16
|
| Why are you trying to restore a database when it is being used heavily 24/7? The drawback to killing all the users is that their transactions are going to be rolled back, which means that they have lost the changes that they were doing in the application.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-20 : 13:54:25
|
| I would imagine doing what you suggest would probably end up with you looking for new employement.What are you trying to do?Brett8-) |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-03-20 : 14:15:35
|
| In order to RESTORE a database, it must not be in use. The only way to make it not in use is to kill the users using it.If you put the database in single user mode, the it may still be in use (by a single user), therefore you cannot restored it.I disagree with Brett. Production or not, if you are tasked with restoring a database, you have to kill the users using the database. It might be wise to let them know ahead of time so that you minimize losses. But the bottom line is you cannot maintain 24X7 uptime and do a database restore.Jay White{0} |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-20 : 14:29:07
|
| Page47, we agree with you 100%. We're just letting him know what killing the users means, so yes he should definitely notify people about it happening.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-20 : 15:02:29
|
| Well the question I have is what happens to all of the activity since the last log? It all will go bye-bye.I still want to know why s/he's trying to restore production. Has s/he been tasked to do so, or is there some other idea s/he has in mind.Do you have a contingency box?Brett8-) |
|
|
|
|
Database Single user mode