Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question
1  
See this question on Stack Overflow –  Gaius Sep 21 '11 at 16:14
    
@User - This script deletes database users. The OP wants to disconnect users who are connected to the database. –  Nick Chammas Jul 1 '13 at 5:19
3  
"user" you should immediately remove your answer, you posted a irreversible solution that will literally delete the users from the database. You gotta read what the question is asking before posting a 'nuclear' solution man, this is not helpful at all and very dangerous. –  Ali Razeghi Jul 1 '13 at 18:39
add comment

4 Answers 4

up vote 7 down vote accepted

There are two ways of doing it:

  1. Right click on the database in Object Explorer go to Tasks > Detach. Select the Drop Connections checkbox.

  2. 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
    
share|improve this answer
5  
For option 1 -> not directly detach, just select the drop connections option and then script the action. You'll have a script created by Management Studio that will either kill current sessions or will make db single-user (don't remember exactly which). And after generating this script just Cancel the Detach screen. –  Marian Sep 22 '11 at 8:50
    
Wil, I edited in the script from the page you linked to. Dunno if it's changed since you originally posted this answer, but this script kicks everyone else out and then does a few more things--make the database READ_ONLY and then make it MULTI_USER again. –  Nick Chammas Jul 1 '13 at 5:24
1  
The database names don't need to be in quotes, so you should remove those from your script. –  siride Jul 22 '13 at 16:41
add comment

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.

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;
share|improve this answer
add comment

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'
share|improve this answer
add comment

I use this code:

ALTER DATABASE [Dbname] set offline with rollback immediate
GO
ALTER DATABASE [Dbname] set online
GO

But I can see the SINGLE USER example is less to type.

share|improve this answer
3  
Also setting the database back online means other users can connect again before you start your restore. –  Aaron Bertrand Jun 2 '12 at 16:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.