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，微软真牛！
参考文章：