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 |
loiter99
Starting Member
38 Posts |
Posted - 2004-06-18 : 15:27:48
|
Question: How do you get a DB in single user mode immediately? Say a restore needs done, but you have users popping in and out and you need exclusive access to do a restore. The alter database command keeps failing because said access can't be obtained. What do you do?Thanks in advanceJ |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-18 : 15:29:15
|
Run this before the RESTORE:ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATETara |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-19 : 02:43:53
|
Amazing how much I learn lurking around in here ...Does that get the current task the single user connection, or might someone else grab it?I'm meaning, can I do something like:ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE-- Some error check here?? e.g. loop round until successRESTORE ....GOand be sure that this will run atomically with all other users locked out?Kristen |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-20 : 02:30:36
|
I've never had any users slip in with this command. I think the ROLLBACK IMMEDIATE almost guarantees that you get the single user connection. But I said almost, so there's still a possibility of someone slipping in.Tara |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-20 : 06:53:47
|
Well, now I think about it, the RESTORE will just fail if someone else manages to slip in, so I can check for that too. But at least it gives me the wherewithall to try to get exclusive use before the RESTORE startsThanksKristen |
|
|
|
|
|