【ADO.NET】Get progress rate(percentage) when using T-SQL and ADO.NET to backup and restore DB

接續【ADO.NET】Make full backup by ADO.NET and T-SQL cmd 這篇，在備份或還原時要偵測進度。

一開始查的時候也是很多都建議用套件，花了一點時間找到 SOF

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

簡單說是做個 timer 一段時間就去跑這段查詢，[Percent Complete]可得完成度，算是滿足需求，可是不是很方便...

後來查到(SOF)可以用 STATS = N 這個內建的參數去要求 SQL Server 吐完成度，再用 SqlConnection.InfoMessage 去收，但一開始碰到一個問題是照著官方的設定下去做出來，事件不會每一個進度都回報，而是全部跑完才一次噴，等於根本沒用阿！

後來又花了一點時間才查到(SOF)，重點在 FireInfoMessageEventOnUserErrors 這個屬性要打開才會每個進度點都回報。

結論

private void BackupDB(string dbName, string saveAt)
{
    using (SqlConnection conn = new SqlConnection(CONN_STR)) {
        conn.FireInfoMessageEventOnUserErrors = true;
        conn.InfoMessage += (sender, args) => { Console.WriteLine(args.Message); };
        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();
    }
}

還原一樣是加 STATS = N 在 WITH 後面