6

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rusty Wizard
  • 531
  • 6
  • 20
  • That is because it will kill you own process also. As your application also get connected with SQL Server – Jalpesh Vadgama Apr 06 '15 at 12:57
  • 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. – Rusty Wizard Apr 06 '15 at 13:01
  • 1
    Which database you are using when connecting? I suspect it is AppDB. Change your connection string to connect to `master` instead. – Vishal Gajjar Apr 06 '15 at 13:30
  • @VishalGajjar the way he sets up `connection` looks like it's master because he does not specify a database name. – usr Apr 06 '15 at 15:33
  • @usr could be. But `user`'s default database might be `AppDB`? – Vishal Gajjar Apr 07 '15 at 06:07
  • @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. – Rusty Wizard Apr 07 '15 at 06:08

2 Answers2

7

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.

usr
  • 168,620
  • 35
  • 240
  • 369
  • 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._ – Rusty Wizard Apr 07 '15 at 04:39
  • @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`. – usr Apr 07 '15 at 08:58
  • 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. @usr – Rusty Wizard Apr 07 '15 at 09:17
  • 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. – usr Apr 07 '15 at 09:18
  • 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. – Rusty Wizard Apr 07 '15 at 09:38
  • @VishalGajjar do you want to post an answer? The user database was your idea. – usr Apr 07 '15 at 09:40
3

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
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • So I guess SMO is not the way to go then? – Rusty Wizard Apr 06 '15 at 13:07
  • 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 :) – M.Ali Apr 06 '15 at 13:09
  • 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? – Rusty Wizard Apr 06 '15 at 13:21
  • 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) – M.Ali Apr 06 '15 at 13:25