備份SQLServer 資料庫檔案到本地機上

{********************************

著作權 jack_0424@126.com   2006、4、16

功能描述：
  備份SQLServer 資料庫檔案到本地機上

實現思路：
  因SQLServer的備份檔案只能儲存於資料庫伺服器所在的機器上，為用戶端資料備份帶來不便。
  為突破這一限制，程式採用了以下思路：
  1，先將資料備份於伺服器上
  2，再將備份檔案載入到以image為列的資料表中
  3，用戶端讀取此表，將image列儲存為本地檔案

使用情景：
  1，以ADO為串連組件
  2，以SQLServer2000為資料庫

調用方法：
  BackupDBFile(ADOConnection1);

********************************}

unit USQLBackFile;

interface
uses DB,ADODB,SysUtils,Forms,Dialogs;

type
  TSQLBack=class
  private
    FDBName,FBakFilename: string;
    FUser,FPass: string;
    FRemoteDBDataPath, FRemoteDBBinnPath,
    FLocalDBDataPath, FLocalDBDataName, FLocalDBDataPathName: string;
    FADOConn:TADOConnection;
    FADOQuery: TADOQuery;
    procedure BackupDBFile;
    function GetData(mSQL:string):string;
    procedure BackDBToRemote;
    procedure ExecSQL(mSQL: string);
    procedure BackFileToImg;
    procedure EnsureImgTable;
    procedure LoadImage;
    procedure InitParams;
    function  GetParams(const param:string):string;
    procedure GetBakFileToLocal;
    procedure DoCleanWork;
  public
    constructor Create(ADOConn:TADOConnection);
    destructor destroy;
   
  end;

procedure BackupDBFile(ADOConn:TADOConnection);

implementation

procedure BackupDBFile(ADOConn:TADOConnection);
var
  SQLBack: TSQLBack;
begin
  SQLBack := TSQLBack.Create(ADOConn) ;

  with SQLBack do
  try
    BackupDBFile;
  finally
    Free ;
  end;

end;

{ TSQLBack }

procedure TSQLBack.BackDBToRemote;
begin
  FRemoteDBDataPath := ExtractFilePath(GetData('select filename from sysfiles'));
  ExecSQL(Format('backup database %s to disk=''%s'' with init'
    ,[FDBName,FRemoteDBDataPath+FBakFilename]));
end;

procedure TSQLBack.BackFileToImg;
begin
  EnsureImgTable; //確保image所在表正確
  LoadImage;      //服務端備份檔案載入表中
end;

procedure TSQLBack.EnsureImgTable;
begin
  ExecSQL(Format('if object_id(''bakfile'') is null begin '
    +#13#10+ ' create table bakfile(img image null) '
    +#13#10+ ' insert into bakfile values('''')'
    +#13#10+ ' end'
    ,[]));
end;

procedure TSQLBack.LoadImage;
begin
  FRemoteDBBinnPath := ExtractFilePath(
    GetData('select filename from master..sysdatabases where name=''master''')
    );
  FRemoteDBBinnPath := StringReplace(FRemoteDBBinnPath,'MSSQL\data','MSSQL\binn',[rfIgnoreCase]);

  //因為TextCopy.exe執行時路徑不能包含空格，故先將其copy到根目錄下
  ExecSQL(Format('execute master..xp_cmdshell ''copy "%s" c:\TextCopy.exe'''
    ,[FRemoteDBBinnPath+'TextCopy.exe']));

  ExecSQL(Format('execute master..xp_cmdshell ''%s /U %s /P %s /D %s /T bakfile /C img'
    + ' /W "where 1=1" /F "%s" /I'',NO_OUTPUT'
    ,['c:\TextCopy.exe'
     ,FUser
     ,FPass
     ,FDBName
     ,FRemoteDBDataPath+FBakFilename
     ]));

  //刪除臨時檔案  
  ExecSQL(Format('execute master..xp_cmdshell ''del %s''',['c:\TextCopy.exe']));
end;

procedure TSQLBack.DoCleanWork;
begin
  ExecSQL('update bakfile set img=''''');
end;

procedure TSQLBack.BackupDBFile;
begin
  BackDBToRemote;  //備份資料庫到服務端機器
  BackFileToImg; //資料庫檔案轉為image列
  GetBakFileToLocal; //備份檔案傳回用戶端
  DoCleanWork; //資料庫清理

  ShowMessage('資料庫已成功備份到：'+FLocalDBDataPathName);
end;

procedure TSQLBack.GetBakFileToLocal;
begin
  with FADOQuery do begin
    close;
    SQL.Text := 'select img from bakfile';
    open;

    TBlobField(Fields[0]).SaveToFile(FLocalDBDataPathName);
    Close;
  end;
end;

constructor TSQLBack.Create(ADOConn: TADOConnection);
begin
  FADOConn := ADOConn ;
  FADOQuery:= TADOQuery.Create(nil) ;
  FADOQuery.Connection := FADOConn;
  FADOQuery.ParamCheck := False;

  InitParams;
end;

procedure TSQLBack.InitParams;
begin
  FUser := GetParams('User ID');
  FPass := GetParams('Password');
  FDBName := GetParams('Initial Catalog');
  FBakFilename := FDBName +'.bak';

  FLocalDBDataPath := ExtractFilePath(Application.ExeName)+'DataBackup\';
  FLocalDBDataName := 'DB' + FormatDateTime('YYYY-MM-DD_HH-NN-SS',now) + '.bak';
  FLocalDBDataPathName := FLocalDBDataPath + FLocalDBDataName ;

  ForceDirectories(FLocalDBDataPath);
end;

function  TSQLBack.GetParams(const param:string):string;
begin
  {ConnectionString='Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;
   User ID=sa;Initial Catalog=pubs;Data Source=192.168.1.30;
   Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;
   Workstation ID=JACKSOFT;Use Encryption for Data=False;
   Tag with column collation when possible=False'}
  Result := FADOConn.ConnectionString ;
  Result := copy(Result,Pos(';'+param+'=',Result)+1,100);
  Result := copy(Result,1,Pos(';',Result)-1);
  Result := copy(Result,Length(param)+2,100);
end;

destructor TSQLBack.destroy;
begin
  FADOQuery.Free ;
end;

procedure TSQLBack.ExecSQL(mSQL: string);
begin
  with FADOQuery do begin
    close;
    SQL.Text := mSQL;
    execSQL;
  end;
end;

function TSQLBack.GetData(mSQL: string): string;
begin
  with FADOQuery do begin
    close;
    SQL.Text := mSQL;
    open;

    Result := Fields[0].AsString ;
  end;
end;

end.