備份SQL Server數(shù)據(jù)庫時使用進度條

作者: penal
email: mysqlcced@163.com

使用SQL語句在代碼中備份還原SQL Server數(shù)據(jù)庫，如果數(shù)據(jù)庫比較大，界面只能阻塞，等待備份還原完成。這段時間無法顯示一些進度信息，讓界面看起來更加友好。下面介紹一種方法，可以在備份還原的同時，顯示進度條。

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;