Exclusive access could not be obtained because the database is in use with SQL Server authentication

Question

I am facing a weird issue with the way I am using the Server object. It looks like when using SQL Server authentication, doing a restore it's complaining

Exclusive access could not be obtained because the database is in use

Here is the full code to restore when the Restore button is clicked. Please see server.KillAllProcesses(DB_NAME). If I create the Server object like so, Server server=new Server("localhost\PharmSpec") and when I do the restore it's working fine even on the VM. But when I create the Server using the connection string like below, it's failing.

The connection string is correct because I do backup and archive and those operations works fine. This issue is happening only on the VM (please scroll below for event log) and on the local machine it works fine.

Any idea what's going on?

Here is the full code:

private void Restore_OK_But_Click(object sender, System.EventArgs e)
{
        SAForm m_saForm;
        string strSAPswd;
        string strDate = "04/04/2003";

        try
        {
            // Validate file.
            DateTime createddate = Convert.ToDateTime(strDate);

            if (DateTime.Now > createddate)
            {
                string strFile = BackupFile_Txt.Text;
                FileInfo FileIn = new FileInfo(BackupFile_Txt.Text);

                if (false == FileIn.Exists)
                {
                    MessageBox.Show(m_ResMngr.GetString("InvalidBackupFile"), m_ResMngr.GetString("Error"), MessageBoxButtons.OK,
                        MessageBoxIcon.Error);
                    OK_But.Enabled = false;
                }
                else
                {
                    // Creates SQL file.
                    WriteFile();
                    m_saForm = new SAForm();
                    m_saForm.ShowDialog();

                    if (true == m_saForm.m_bContinue)
                    {
                        // Modified for Bug #719 - TimR 9-15-2008
                        this.Cursor = Cursors.WaitCursor;
                        OK_But.Enabled = false;
                        this.Refresh();

                        string dbServerName = PharmSpecRegistryHelper.GetDBServerNameFromRegistry(UIService.UIService.PHARMSPEC_DATABASE_REGISTRY_KEY);

                        SqlConnection connection = new SqlConnection(m_connectionString);
                        ServerConnection serverConnection = new ServerConnection(connection);
                        Server server = new Server(serverConnection);
                        if (server == null)
                        {
                            throw new NullReferenceException("Something went wrong");
                        }

                        try
                        {
                            // Kill all open connections and processes on the PharmSpecDB
                            server.KillAllProcesses(DB_NAME);
                        }
                        catch(Exception ex)
                        {
                            // Failed to kill open connections but will try the restore anyway.
                        }
                        finally
                        {
                            server = null;
                        }

                        strSAPswd = m_saForm.m_strsapswd;
                        string strParams = "-U sa -P " + strSAPswd + " -S " + dbServerName + " -i \"" + Environment.CurrentDirectory + "\\Restore.sql\"";

                        System.Diagnostics.Process sqlProcess = new System.Diagnostics.Process();
                        sqlProcess.StartInfo.FileName = oldOSQL;
                        sqlProcess.StartInfo.Arguments = strParams;
                        sqlProcess.StartInfo.UseShellExecute = false;
                        sqlProcess.StartInfo.RedirectStandardOutput = true;

                        sqlProcess.Start();

                        string szOutput = sqlProcess.StandardOutput.ReadToEnd();
                        szOutput = szOutput.Trim(">1234567890 ".ToCharArray());

                        if (szOutput.StartsWith(m_ResMngr.GetString("Restore_Processed")) == false)
                        {
                            throw new Exception(szOutput);
                        }
                        else
                        {
                            BackupFile_Txt.Text = "";
                            txt_Quick.Text = "";
                            int index = strFile.LastIndexOf("\\");
                            strFile = strFile.Substring(index + 1, strFile.Length - (index + 1));
                            LogFile(strFile + m_ResMngr.GetString("RestoreSuccess"));
                            DateTime dt = DateTime.Today;
                            dt = dt.AddDays(1);
                            string strRegkeyValue = dt.ToString("MM/dd/yyyy");
                            EncryptBase64(ref strRegkeyValue);
                            IniWriteValue("Archive", "ArchivedDate", strRegkeyValue);
                            //now clean up the log file (.ldf file)
                            TruncateLDF(strSAPswd);
                            MessageBox.Show(this, m_ResMngr.GetString("RESTORE_COMPLETE"), m_ResMngr.GetString("COMPLETED"), MessageBoxButtons.OK, MessageBoxIcon.Information);
                        }
                    }
                }
            }
            else
                MessageBox.Show(this, m_ResMngr.GetString("invaliddate"), m_ResMngr.GetString("DATE_INVALID"), MessageBoxButtons.OK, MessageBoxIcon.Stop);
        }
        catch (Exception Exc)
        {
            WriteToFile(Exc.Message, "Wrong");
            // shows Error.
            MessageBox.Show(Exc.Message, m_ResMngr.GetString("Error"), MessageBoxButtons.OK,
                MessageBoxIcon.Error);
        }
        finally
        {
            OK_But.Enabled = true;
            this.Cursor = Cursors.Default;
        }
}

private void TruncateLDF(string strSAPswd)
{
    // read datapath from registry
    string strPath = "";

    try
    {
        strPath = PharmSpecRegistryHelper.GetValueFromRegistry<string>(UIService.UIService.PHARMSPEC_DATABASE_REGISTRY_KEY, "DataPath");
        char[] charsToTrim = { '\\' };
        strPath = strPath.TrimEnd(charsToTrim);

        CreateTestFile(strPath);
        string strParams = "";

        // create detach file
        CreateDetachFile();

        // create attach file
        CreateAttachFile(strPath);

        CreateBatchFile(strPath, strSAPswd);

        strParams = Environment.CurrentDirectory + "\\DelLog.bat";
        System.Diagnostics.Process processDeleteLog = System.Diagnostics.Process.Start(strParams);
        processDeleteLog.WaitForExit();
        processDeleteLog.Dispose();
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

This is what I see in the event log:

This is the full exception message when I am writing to a file,

Message: Msg 3101, Level 16, State 1, Server DESKTOP-H61UJDR\PHARMSPEC, Line 1
Exclusive access could not be obtained because the database is in use.

Msg 3013, Level 16, State 1, Server DESKTOP-H61UJDR\PHARMSPEC, Line 1
RESTORE DATABASE is terminating abnormally.

Answer 1

First, you really should simplify this and run your TSQL directly from your SqlConnection, instead of shelling out to SQLCMD. But to fix your problem, set the database to SINGLE_USER before restoring it.

You should be connected to the database when setting it to SINGLE_USER to ensure that you are the single user connected to the database. Then switch to master before the restore, eg

use foo
alter database foo set single_user with rollback after 5
use master
restore database foo from disk='c:\temp\foo.bak' with replace