We need to do a restore, and cannot because other users are connected. We thought we had disconnected every process, but apparently not.
How can we, from Management Studio, kick off everyone else so we can do this backup?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
There are two ways of doing it:
Right click on the database in Object Explorer go to Tasks > Detach. Select the Drop Connections checkbox.
Set the database to single-user mode as outlined here:
-- hit Ctrl+Shift+M in SSMS to fill in the template parameter USE master; GO ALTER DATABASE N'<Database Name, sysname,>' SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO ALTER DATABASE N'<Database Name, sysname,>' SET READ_ONLY; GO ALTER DATABASE N'<Database Name, sysname,>' SET MULTI_USER; GO
I always use the following:
USE master; -- get out of dbname myself GO -- kick all other users out: ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO -- prevent sessions from re-establishing connection: ALTER DATABASE [dbname] SET OFFLINE;
Sometimes this may take a while, and sometimes it is blocked because you're the one running it, and you have an active connection to the database. Check for other query windows that might have the same database context - this can include open dialogs, Object Explorer, IntelliSense, long-running jobs, etc.
When I'm done making my changes to that database's config, I simply:
ALTER DATABASE [dbname] SET ONLINE; ALTER DATABASE [dbname] SET MULTI_USER;
Though, sometimes, the thing I need to do to that database requires the database to be online, so sometimes I have to leave it in single-user mode and do this:
ALTER DATABASE [dbname] SET ONLINE; GO USE [dbname];
Now I can make my changes, and then when I'm ready for other users to connect, simply:
ALTER DATABASE [dbname] SET MULTI_USER;
I normally set database in single_user and then waitfor delay and then set the database back in multiuser as below :
-- to kill all connections for particular db ... otherwise the restore will fail as exclusive lock cannot be obtained for the db being restored. alter database db_name set single_user with rollback immediate waitfor delay '00:00:05' -- wait for 5 secs alter database db_name set multi_user restore database db_name from disk = 'D:\restore\db_name.bak' with replace, stats = 10, recovery -- if you want to recover your database online -- optional if you dont have the same directory/file structure move 'datafile logical name' to 'E:\data\physical_name.mdf', move 'logfile logical name' to 'F:\log\physical_name_log.ldf'
The following actually kills all connections. Quite useful in cases where setting single user mode fails
declare @execSql varchar(1000), @databaseName varchar(100) -- Set the database name for which to kill the connections set @databaseName = 'databasename' set @execSql = '' select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' ' from master.dbo.sysprocesses where db_name(dbid) = @databaseName and DBID <> 0 and spid <> @@spid exec(@execSql)
You can use the script below to either nuke everyone, or modify for a specific DB.
Anything that can be killed off, will be! SQL service SPIDs however will not be affected.
Drop table #who go Create table #who( [spid] int, [ECID] int, [Status] varchar(100), [Loginname] varchar(200), [Hostname] varchar(200), [blk] bit, dbname varchar(200), cmd varchar(1000), requestID int ) go Insert into #who (Spid, ECID, Status, Loginname, hostname,blk, dbname, cmd, requestid) exec sp_who Declare cursKillUsers Cursor for Select 'Kill ' + cast(spid as varchar(100)) + ';' [SQL] from #who where dbname like '%' Declare @sql varchar(200) Open cursKillUsers Fetch next from cursKillUsers into @sql While @@fetch_status = 0 begin print @sql Exec (@sql) Fetch next from cursKillUsers into @sql end close cursKillUsers deallocate cursKillUsers