ASP.NET 實作 SQL Server 資料庫還原與排他鎖定問題解決

問題背景

在開發畢業設計的資料庫備份與還原頁面時，發現執行還原操作會遭遇資料庫排他性存取問題。參考網路討論後，對原始碼進行修改以適配 ASP.NET 環境。

核心程式碼實作

void RestoreButton_Click(object sender, System.EventArgs e)
{
    string path = PathTextBox.Text;
    string dbName = DbDropDownList.SelectedValue;

    string restoreSql = "USE master; RESTORE DATABASE @dbname FROM DISK = @path;";
    string offlineOnlineSql = "USE master; ALTER DATABASE Wy SET OFFLINE WITH ROLLBACK IMMEDIATE; ALTER DATABASE Wy SET ONLINE WITH ROLLBACK IMMEDIATE";

    using (SqlConnection conn = new SqlConnection(ConnStr))
    using (SqlCommand offlineCmd = new SqlCommand(offlineOnlineSql, conn))
    using (SqlCommand restoreCmd = new SqlCommand(restoreSql, new SqlConnection(ConnStr)))
    {
        restoreCmd.Parameters.Add("@dbname", SqlDbType.Char).Value = dbName;
        restoreCmd.Parameters.Add("@path", SqlDbType.Char).Value = path;

        try
        {
            conn.Open();
            offlineCmd.ExecuteNonQuery(); // 強制離線斷開連線
            conn.Close();

            restoreCmd.Connection.Open();
            restoreCmd.ExecuteNonQuery();
            InfoLabel.Text = "恢復成功！\n已將備份 " + path + " 還原至資料庫";
        }
        catch (Exception ex)
        {
            InfoLabel.Text = "恢復失敗！\n錯誤訊息：" + ex.ToString();
        }
        finally
        {
            if (restoreCmd.Connection.State == ConnectionState.Open)
                restoreCmd.Connection.Close();
        }
    }
}

關鍵技術點解析

1. ALTER DATABASE ... SET OFFLINE WITH ROLLBACK IMMEDIATE：強制將目標資料庫設為離線，並立即回滾所有未完成交易，釋放所有鎖定，解決「資料庫正在使用中」導致還原失敗的問題。
2. 參數化查詢：使用 SqlParameter 防止 SQL 注入，確保資料庫名稱與備份路徑安全傳遞。
3. 連線管理：採用 using 區塊確保連線正確釋放，避免連線洩漏。
4. 錯誤處理：完整的 try-catch-finally 結構，提供友善的使用者回饋。

注意事項

• 執行還原的帳號需具備 sysadmin 或 dbcreator 角色權限。
• 備份檔案路徑需為 SQL Server 服務帳號可存取的本機或網路路徑。
• 實際部署時，建議將資料庫名稱硬編碼或從下拉選單選取，避免直接使用使用者輸入的資料庫名稱造成安全風險。