备份还原 sql server 进度条

时间:2011-5-26来源:yang 作者: peng点击: 44次

备份SQL Server数据库时使用进度条

作者: penal
email: [e-mail]

使用SQL语句在代码中备份还原SQL Server数据库，如果数据库比较大，界面只能阻塞，等待备份还原完成。这段时间无法显示一些进度信息，让界面看起来更加友好。下面介绍一种方法，可以在备份还原的同时，显示进度条。

const
  ConnStr = ‘Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security In‘ +
            ‘fo=False;Initial Catalog=master‘;

function GetProgress: Integer;
var
  Msg: WideString;
  S: string;
  Err: IErrorInfo;
  I: Integer;
begin
  // 取得OLE DB的错误或警告要通过GetErrorInfo, 即使消息不属于错误也必须通过
  // 这种方式来获取.
  if (GetErrorInfo(0, Err) = S_OK) and (Err <> nil) then
  begin
    if Err.GetDescription(Msg) = S_OK then
    begin
      S := ‘‘;
      // 取得百分比的字值
      for I := 1 to 3 do
      begin
        if Char(Msg[I]) in [‘0‘..‘9‘] then
          S := S + Msg[I]
        else
          Break;
      end;
      if TryStrToInt(S, Result) then Exit;
    end;
  end;
  Result := -1;
end;

procedure TForm1.BtnBackupClick(Sender: TObject);
const
  BackupSQL = ‘BACKUP DATABASE [pubs] TO  DISK = N%s WITH NOINIT, NOUNLOAD, ‘ +
            ‘NAME = N%s, NOSKIP, STATS = 10, NOFORMAT‘;
  BakFile = ‘D:\pubs.bak‘;
  BakName = ‘pubs 备份‘;
var
  SQL: string;
  Conn: _Connection;
  Rst: _Recordset;
  RecsAffected: OleVariant;
  percent: Integer;
begin
  PBar.Position := 0;
  Conn := CoConnection.Create;
  Conn.Open(ConnStr, ‘‘, ‘‘, 0);
  SQL := Format(BackupSQL, [QuotedStr(BakFile), QuotedStr(BakName)]);
  Rst := Conn.Execute(SQL, RecsAffected, adCmdText);
  while Rst <> nil do
  begin
    percent := GetProgress;
    if percent <> -1 then
      PBar.Position := percent;
    Application.ProcessMessages;
    Rst := Rst.NextRecordset(RecsAffected);
  end;
end;