How to kill all connections to a SQL Server database using C# and SMO?

Question

I'm trying to restore a database from a .BAK file using C# and SMO. This is my code.

public static void RestoreDatabase()
{
    string dbConnString = Configuration.DatabaseConnectionString;
    ServerConnection connection = new ServerConnection(@"dbserver\sqlexpress", "user", "password");
    Server smoServer = new Server(connection);

    Restore rstDatabase = new Restore();
    rstDatabase.Action = RestoreActionType.Database;
    rstDatabase.Database = "AppDb";

    BackupDeviceItem bkpDevice = new BackupDeviceItem(@"TestData\db-backup.bak", DeviceType.File);
    rstDatabase.Devices.Add(bkpDevice);
    rstDatabase.ReplaceDatabase = true;

    // Kill all processes
    smoServer.KillAllProcesses(rstDatabase.Database);

    // Set single-user mode
    Database db = smoServer.Databases[rstDatabase.Database];
    db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
    db.Alter(TerminationClause.RollbackTransactionsImmediately);

    rstDatabase.SqlRestore(smoServer);
}

However when I try to run this method I get the following (error) message when it attempts to kill all processes:

Cannot use KILL to kill your own process.

I would be very grateful if someone could help solve this issue.

Answer 1

Simply not call KillAllProcesses. RollbackTransactionsImmediately is enough. It kills all sessions that are in that database right now.

KillAllProcesses does not help you anyway because right after it is done killing sessions a new one could appear.

Answer 2

It happens to me all the time when I am trying to restore a database and there is an active connection somewhere and database will not restore until there are no more active connections to the database.

Usually what I do is, I execute the following set of commands to disconnect everyone and then restore the database .

ALTER DATABASE [DatabaseName]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- at this point all the users will be disconnected and the database is in 
-- single-user mode

Use [DatabaseName]  --<-- Grab that single connection 
GO

Use [master]        --<-- Disconnect from database and connect to Master DB for restore
GO

RESTORE DATABASE [DatabaseName]  --<-- Finally restore database tara
 FROM DISK .........  
GO