February 25, 2005 at 1:10 am
Hi,
I would like to restore the database from the Enterprise Manager (by clicking the database name, and then take it off line and restoring).
Before restoring i would like to disconnect all existing connections.
I though about using:
alter database <dbname>
set single_user
with rollback immediate
But this command will kill all connections to the <dbname> database except for the connection that I am currently having to the database.
And I will not be able to from the Enterprise Manager.
I also thought about killing all the connection by executing KILL command but the problem is that when there are large number of clients connected some may connect back while killing all the other.
I would like to know a way to kill all connection beside the enterprise manager.
Thanks,
Maya.
February 25, 2005 at 6:31 am
Run this script in QA. Be sure to change the db name for the variable @DBName. There are better ways to do this but this works just fine. You can add your restore script to the end or restore from EM. Just be fast.
use master
go
-- sp_who2
-- Change the @DBName variable to the name of the database
-- wish to work with
declare @DBName varchar(30),@spid int,@SQL varchar(40)
set @DBName = 'db_name'
DECLARE CurKill INSENSITIVE CURSOR
FOR
-- To kill by DB use this script
SELECT spid
FROM sysprocesses
where dbid = (select dbid from sysdatabases where name = @DBName)
OPEN CurKill
FETCH NEXT FROM CurKill
INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'KILL ' + Convert(varchar(3),@spid)
-- print @SQL
EXEC (@SQL)
FETCH NEXT FROM CurKill
INTO @spid
END
close CurKill
deallocate CurKill
February 25, 2005 at 7:42 am
Thanks for replying.
If there are a large number of clients, is it possible that while killing all clients some will connect again to the database?
February 25, 2005 at 1:37 pm
close QA, go to EM run restore. The database will show single user.
Robert
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply