SQL Server中SMO备份数据库进度条不显示？

有朋友提到一个奇怪的问题，用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