Error detaching sql express database using SMO

I am using SMO to provide a backup/restore feature. The restore code looks like this:

try
{
// Initialise server object.
Server server = new Server(serverName);
server.ConnectionContext.ConnectionString = connectionString;

// Check if database is current attached to sqlexpress.
foreach (Database db in server.Databases)
{
if (String.Compare(db.Name, destinationPath, true) == 0)
{
Console.WriteLine("Detaching existing database before restore ...");
server.DetachDatabase(db.Name, false);

break;
}
}

// Configure restore.
Restore restore = new Restore();
restore.Database = destinationPath;
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
restore.Devices.Add(new BackupDeviceItem(sourcePath, DeviceType.File));

// Perform restore.
restore.SqlRestore(server);
}
catch (FailedOperationException foe)
{
Console.WriteLine("Exception - SqlRestore of {0} failed with: {1}. Detail: {2}",
sourcePath, foe.Message, foe.InnerException.ToString());
}

This work 9/10 times. However occassional the DetachDatabase() call fails with the following error even though the database is not currently in use.

Exception - SqlRestore of D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.bak failed with: Detach database failed for Server 'NICKDEV\SQLEXPRESS'. . Detail: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot detach the database 'D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.mdf' because it is currently in use.

Clearly it was in use previously and the application using it may have crashed and not closed connections etc; this is why I want to restore the database.

How can I avoid/ workaround this to ensure my restore functionality is full proof.

Thanks,

Nick

Hello

I had exactly the same problem. It took some time to figure out but here is the solution:

You need to insert the restore code somewhere in your program before you open any database associated with your program. I put a restore databases button on the main form before any databases were opened. This works fine. Backup works wherever you put it in the code.

The reason for the problem is that once your database has been opened SQL Server will report an open connection even if you close the database connection. A gift from microsoft!!! There is no way you can disconnect from the database once you have opened it except to completely exit the program! Microsoft seems to provide thousands of options but never the one you need.

I hope this helps you.

|||

Hi,

I have just tackled the same problem, and I found using server.KillAllProcess(dbName) did the trick for me.

Nick

|||

You need to make sure you have the only connection to the database. You can do this by setting the database into single-user mode before detaching it, like this:

server.KillAllProcesses(db.Name);

db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;

db.Alter(TerminationClause.RollbackTransactionsImmediately);

server.DetachDatabase(db.Name, true);

Hope this helps,
Steve

|||Hi,

where exactly did you put your restore-code? On my MainForm, there's also a button, so I think it will be initialized before any connections are opened in MainForm_Load. In the button's click event I call the method that includes the "detach" code. But I receive the same error that the database is in use.

MusiMeli