有朋友提到一個奇怪的問題，用SMO備份數據庫時不顯示進度條，也就是進度條事件PercentComplete不觸發。

今天試了一下，果然有點奇怪。

代碼如下：
using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Common; private void btnBackup_Click(object sender, EventArgs e) { btnBackup.Enabled = false; Thread tr = new Thread(new ThreadStart(doBackup)); tr.Priority = ThreadPriority.AboveNormal; tr.Start(); //Thread.Sleep(3000); } /// <summary> /// 備份數據庫 /// </summary> public void doBackup() { pbDemo.Value = 0; pbDemo.Maximum = 100; pbDemo.Style = ProgressBarStyle.Blocks; //pbDemo.Step = 10; Server srv = new Server(@"(local)"); Backup backup = new Backup(); backup.Action = BackupActionType.Database; backup.Database = "btnet"; backup.Incremental = false; backup.Devices.Add(new BackupDeviceItem(@"C:\agronet09.bak", DeviceType.File)); backup.Initialize = true; backup.PercentCompleteNotification = 10; backup.PercentComplete += new PercentCompleteEventHandler(backup_PercentComplete); //backup.Checksum = true; backup.SqlBackup(srv); } public void backup_PercentComplete(object sender, Microsoft.SqlServer.Management.Smo.PercentCompleteEventArgs e) { this.Invoke(new displayProgress_delegate(displayProgress), e.Percent); //Application.DoEvents(); } public delegate void displayProgress_delegate(int progress); public void displayProgress(int progress) { this.lbProgress.Text = "已完成[" + progress.ToString() + " %]"; pbDemo.Value = progress; btnBackup.Enabled = (progress == 100); }
癥狀如下：結果正確執行，但進度條不顯示。

剛開始以為是線程問題，后來發現不是這個原因。

又試了另外一段代碼

using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Common; using System.Diagnostics; static void Main(string[] args) { BackupDatabase("ap4\\agronet09", "agronet2008", "c:\\Northind_3.bak"); Console.WriteLine(Environment.NewLine + "Press any key to continue."); Console.ReadKey(); } public static void BackupDatabase(string serverName, string databaseName, string fileName) { Console.WriteLine("*** Backing up ***"); Server server = new Server(serverName); Backup backup = new Backup(); backup.Action = BackupActionType.Database; backup.Database = databaseName; backup.Incremental = false; backup.Initialize = true; backup.LogTruncation = BackupTruncateLogType.Truncate; BackupDeviceItem backupItemDevice = new BackupDeviceItem(fileName, DeviceType.File); backup.Devices.Add(backupItemDevice); backup.PercentCompleteNotification = 10; backup.PercentComplete += backup_PercentComplete; backup.Complete +=backup_Complete; backup.SqlBackup(server); } protected static void backup_PercentComplete(object sender, PercentCompleteEventArgs e) { Console.WriteLine(e.Percent + "% processed."); //Application.(); System.Threading.Thread.Sleep(1000); } protected static void backup_Complete(object sender, ServerMessageEventArgs e) { Console.WriteLine(Environment.NewLine + e.ToString()); }
結果還是不顯示。

后來突然想到會不會是文件太小，試了一個200M的數據文件，果然成功顯示：

后來經反復實驗，發現SQL server 2000約在數據文件加日志文件大于6M左右時顯示進度條。

而Sql server 2008 r2大約在3M時顯示進度條。可能跟機器也有關系。

注意：SQL server 2008 r2版規定主數據文件必須大于3M，微軟真牛！

參考文章：

http://msdn.microsoft.com/zh-cn/magazine/cc163409.aspx

SQL Server中SMO備份數據庫進度條不顯示？