备份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.