【ADO.NET】Make full backup by ADO.NET and T-SQL cmd

工作上需要用到程式自動備份與還原資料庫，一開始查到的大部分是教要用 SMO 工具可是這樣要額外裝東西，感覺蠻麻煩的，直覺可以直接跑 T-SQL 命令做掉，花了一點時間查，以下是做法


備份

private void BackupDB(string dbName, string saveAt)
{
    using (SqlConnection conn = new SqlConnection(CONN_STR)) {
        conn.Open();
        SqlCommand cmd = new SqlCommand(" BACKUP DATABASE @DBName TO DISK = @PATH WITH STATS = 5", conn);
 
        cmd.Parameters.AddWithValue("@DBName", dbName);
        cmd.Parameters.AddWithValue("@PATH", saveAt);
        cmd.CommandTimeout = 86400000;
 
        cmd.ExecuteNonQuery();
    }
}

還原

public void RestoreDB(string dbName, string loadBakFilePath)
{
    using (SqlConnection conn = new SqlConnection(CONN_STR)) {
        SqlConnectionStringBuilder connStrBuilder = new SqlConnectionStringBuilder(CONN_STR);
        connStrBuilder.InitialCatalog = "master";
        conn.ConnectionString = connStrBuilder.ConnectionString;
        conn.Open();
 
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "ALTER DATABASE " + dbName + " SET Single_User WITH Rollback Immediate; RESTORE DATABASE " +
        dbName + " FROM DISK = @PATH WITH REPLACE; ALTER DATABASE " + dbName + " SET Multi_User;";
        cmd.CommandTimeout = 86400000;
        cmd.Parameters.AddWithValue("@DBName", dbName);
        cmd.Parameters.AddWithValue("@PATH", loadBakFilePath);
        cmd.Connection = conn;
        cmd.ExecuteNonQuery();
    }
}

ref：CaveDweller