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.

Hardboiled

That is because it will kill you own process also. As your application also get connected with SQL Server – Sixth

Is there a solution/workaround for this? I tried to simply set the database to single user but that didn't work. It was after this that I included the line to kill all processes. – Hardboiled

Which database you are using when connecting? I suspect it is AppDB. Change your connection string to connect to master instead. – Patriliny

@VishalGajjar the way he sets up connection looks like it's master because he does not specify a database name. – Gauguin

@Gauguin could be. But user's default database might be AppDB? – Patriliny

@VishalGajjar AppDb is mentioned here only to point out which DB is going to be restored. As per my understanding of SMO, usr is correct. – Hardboiled

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.

Gauguin

I did try it without KillAllProcesses as well but then I got the following error message: RESTORE cannot process database 'AppDb' because it is in use by this session. It is recommended that the master database be used when performing this operation. RESTORE DATABASE is terminating abnormally. – Hardboiled

@RustyWizard OK, then you are doing something to have AppDB as the current database as suspected by Vishal. Check what he said (users default database). Also, connect SQL Profiler and sift through all statements made on that connection to see if there is a USE AppDB. – Gauguin

I connected a SQL profiler and I tried executing the scripting (without the KillAllProcesses command). After setting the database to single user the proc sp_reset_connection is executed followed by the Restore operation. There is no USE AppDb between these two steps. Also, it does USE master before setting the db to single user. @Gauguin – Hardboiled

OK, that sounds like a default database for your user. Check that. You are never giving a connection string to SMO, correct? The variable dbConnString is unused. – Gauguin

Cheers @usr! You have been a great help. Turns out AppDb was set as the default db for this particular db user account. After I changed the default DB to master I stopped getting that issue. If you could post your comment as a reply to the question I could mark it as the correct answer. – Hardboiled

@VishalGajjar do you want to post an answer? The user database was your idea. – Gauguin

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

Achorn

So I guess SMO is not the way to go then? – Hardboiled

Maybe there is a more sleek way of doing it with SMO, I have just shared my experience with you. The most painful experience is when an application is making connection with your database, so no matter how many times you kill that connection, the application makes another connection as soon as you kill that active connection, this method also works in that application connection scenario. and has saved me a lot of time and hassle :) – Achorn

I do appreciate your reply; I'm sorry if my comment implied otherwise :) Just a small concern though, would the BAK file have to be on the DB server or could I host in the app server or any other accessible location? – Hardboiled

No its fine mate, I really dont know if there is a better of doing it with SMO :) The .bak file can be located anywhere as long as it is accessible (shared drive and user has permission to access it etc) – Achorn