May 22, 2003 at 9:46 pm
Hi,
Our dbo deleted some data by mistake. I must restore from backup. I can't set databse in single user option. Our developer created some application, when I start the sql server some application run at once. I tried to run exec sp_dboption 'dbname', single user', 'true' or dbo use only or offline. I got the same result: while other users are using the database, the database state can not be changed, the sp_dboption command failed.
I need help, can someone tell me how to configure the single user, so I can restore
the database.
Thanks in advance.
Robert
May 22, 2003 at 10:42 pm
Have a look at BOL 2000:
scan for startup procs Option.
May 22, 2003 at 11:00 pm
you can execute the below.
declare cur_temp cursor for select spid from sysprocesses where dbid = db_id('database_name')
declare @spid varchar(10)
open cur_temp
fetch next from cur_temp into @spid
while @@fetch_status = 0
begin
declare @sql varchar(50)
set @sql = 'kill ' + @spid
exec(@spid)
fetch next from cur_temp into @spid
end
close cur_temp
deallocate cur_temp
alter database database_name
set single_user
replace database_name with the name of your database. You might have to execute couple of times if you have lot of connections being created. Good luck
May 23, 2003 at 7:47 am
Thank you guys.
My database got about 20 connections/second.
Does it still work?
I start sql with single user mode from cmd.
then restore from backup.
Thanks again.
Robert
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply