backupdbf_unit.pas

每天将SQL SERVER中的记录进行完全备份

unit BackupDBF_Unit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, ExtCtrls, DB, ADODB, Grids, DBGrids,IniFiles,
  StdCtrls, Buttons, ExportDBF;

type
  TMain_Frm = class(TForm)
    Panel1: TPanel;
    StatusBar: TStatusBar;
    Straight_Sqlserver_Connection: TADOConnection;
    DB_TABLE_Name_Query: TADOQuery;
    Arm_Sqlserver_Connection: TADOConnection;
    DB_TABLE_Name_QueryCPUID: TSmallintField;
    DB_TABLE_Name_QueryINFOKIND: TSmallintField;
    DB_TABLE_Name_QueryFSTM: TDateTimeField;
    DB_TABLE_Name_QueryCVALUE: TStringField;
    DB_TABLE_Name_QueryIVALUE: TSmallintField;
    DB_TABLE_Name_QueryREMARK: TStringField;
    User_Pwd_Query: TADOQuery;
    User_Pwd_QueryCVALUE: TStringField;
    DBFExport: TDBFExport;
    Timer: TTimer;
    State_Memo: TMemo;
    Backup_Table: TADOTable;
    procedure FormShow(Sender: TObject);
    procedure TimerTimer(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Main_Frm: TMain_Frm;
  //INI文件的备份路径参数
  bkPath:String;
  //[Aim_Sqlserver]的相关参数
  AProvider,APassword,APersist_Security_Info,AUser_ID,AInitial_Catalog,AData_Source:string;
implementation

{$R *.dfm}

procedure TMain_Frm.FormShow(Sender: TObject);
var
  Configure_Sqlserver: TIniFile;
  //[Straight_Sqlserver]的相关参数
  SProvider,SPassword,SPersist_Security_Info,SUser_ID,SInitial_Catalog,SData_Source:string;
  //备份间隔时间（毫秒）
  Interval:String;
  //各自的连接串
  SConnection_String,AConnection_String:string;
begin
  //读出INI文件中的相关参数
  Configure_Sqlserver := TIniFile.Create(ExtractFilePath(ParamStr(0)) + 'Configure_Sqlserver.ini');
 with Configure_Sqlserver do
  begin
//-------读出直接服务器[Straight_Sqlserver]的相关参数并组成连接串-------------------------

   SProvider:=ReadString('Straight_Sqlserver', 'Provider',SProvider);
   SPassword:=ReadString('Straight_Sqlserver', 'Password',SPassword);
   SPersist_Security_Info:=ReadString('Straight_Sqlserver', 'Persist Security Info',SPersist_Security_Info);
   SUser_ID:=ReadString('Straight_Sqlserver', 'User ID',SUser_ID);
   SInitial_Catalog:=ReadString('Straight_Sqlserver', 'Initial Catalog',SInitial_Catalog);
   SData_Source:=ReadString('Straight_Sqlserver', 'Data Source',SData_Source);

   SConnection_String:='Provider='+SProvider+';Password='+SPassword+';Persist Security Info=';
   SConnection_String:=SConnection_String+SPersist_Security_Info+';User ID='+SUser_ID+';Initial Catalog=';
   SConnection_String:=SConnection_String+SInitial_Catalog+';Data Source='+SData_Source;
   Straight_Sqlserver_Connection.ConnectionString:=SConnection_String;

//-------读出直接服务器[Straight_Sqlserver]的相关参数并组成连接串-------------------------

//-----通过直接服务器中TRAN_SH库的SYSINFO表取出间接服务器的用户名和密码值-----------------

   APassword:='';
   AUser_ID:= '';
   State_Memo.Lines.Clear;

  Try
   Straight_Sqlserver_Connection.Connected:=True;
   State_Memo.Lines.Add(datetostr(date())+' '+timetostr(time())+': 连接直接服务器成功！');

   User_Pwd_Query.Close;
   User_Pwd_Query.SQL.Clear;

   {取用户名称}
   User_Pwd_Query.SQL.Add('select CVALUE from SYSINFO where INFOKIND=6');
   User_Pwd_Query.Open;
   AUser_ID:=Trim(User_Pwd_QueryCVALUE.Value);
   User_Pwd_Query.SQL.Clear;
   User_Pwd_Query.Close;
   State_Memo.Lines.Add(datetostr(date())+' '+timetostr(time())+': 取得间接服务器用户名！');
   {取用户密码}
   User_Pwd_Query.SQL.Add('select CVALUE from SYSINFO where INFOKIND=7');
   User_Pwd_Query.Open;
   APassword:=Trim(User_Pwd_QueryCVALUE.Value);
   User_Pwd_Query.SQL.Clear;
   State_Memo.Lines.Add(datetostr(date())+' '+timetostr(time())+': 取得间接服务器密码！');

  Except
   State_Memo.Lines.Add(datetostr(date())+' '+timetostr(time())+': 连接直接服务器失败！请检查参数设置');
  end;

//-----通过直接服务器中TRAN_SH库的SYSINFO表取出间接服务器的用户名和密码值-----------------


//----------------读出间接服务器[Aim_Sqlserver]的相关参数并组成连接串---------------------
   //APassword:=从上面得到
   //AUser_ID:= 从上面得到
   AProvider:=ReadString('Aim_Sqlserver', 'Provider',AProvider);
   APersist_Security_Info:=ReadString('Aim_Sqlserver', 'Persist Security Info',APersist_Security_Info);
   AInitial_Catalog:=ReadString('Aim_Sqlserver', 'Initial Catalog',AInitial_Catalog);
   AData_Source:=ReadString('Aim_Sqlserver', 'Data Source',AData_Source);


   AConnection_String:='Provider='+AProvider+';Password='+APassword+';Persist Security Info=';
   AConnection_String:=AConnection_String+APersist_Security_Info+';User ID='+AUser_ID+';Initial Catalog=';
   AConnection_String:=AConnection_String+AInitial_Catalog+';Data Source='+AData_Source;
   Arm_Sqlserver_Connection.ConnectionString:=AConnection_String;

  Try
   Arm_Sqlserver_Connection.Connected:=True;
   State_Memo.Lines.Add(datetostr(date())+' '+timetostr(time())+': 试连接间接服务器成功！');
  Except
   State_Memo.Lines.Add(datetostr(date())+' '+timetostr(time())+': 试连接间接服务器失败！请检查参数设置');
  end;
//----------------读出间接服务器[Aim_Sqlserver]的相关参数并组成连接串---------------------


//----------------读出其他参数------------------------------------------------------------

   bkPath:=ReadString('bkPath', 'bkPath',bkPath);
   Interval:=ReadString('Time_Interval', 'Interval',Interval);

//----------------读出其他参数------------------------------------------------------------

  end;
   Configure_Sqlserver.Free;

  Try
   Timer.Interval:= strtoint(Interval);
  Except
   showmessage('时间设置有误！');
   State_Memo.Lines.Add(datetostr(date())+' '+timetostr(time())+': 恢复默认备份时间设置间隔为一天');
   Timer.Interval:= 86400000;
  end;
  StatusBar.Panels[1].Text:='备份路径：'+bkPath;

  Timer.Enabled:=True;
end;

procedure TMain_Frm.TimerTimer(Sender: TObject);
var
  Arm_Sqlserver_ConnectionString:string;
begin
  StatusBar.Panels[0].Text:='最近一次备份时间是：'+datetostr(date())+' '+timetostr(time());
  Try
    Arm_Sqlserver_Connection.Connected:=True;
    DB_TABLE_Name_Query.Open;
    DB_TABLE_Name_Query.First;
    While Not DB_TABLE_Name_Query.Eof do
     begin
      AInitial_Catalog:=trim(DB_TABLE_Name_QueryCVALUE.Value);
      Arm_Sqlserver_ConnectionString:='Provider='+AProvider+';Password='+APassword+';Persist Security Info=';
      Arm_Sqlserver_ConnectionString:=Arm_Sqlserver_ConnectionString+APersist_Security_Info+';User ID='+AUser_ID+';Initial Catalog=';
      Arm_Sqlserver_ConnectionString:=Arm_Sqlserver_ConnectionString+AInitial_Catalog+';Data Source='+AData_Source;
      Arm_Sqlserver_Connection.Connected:=false;;
      Arm_Sqlserver_Connection.ConnectionString:=Arm_Sqlserver_ConnectionString;

      try
       Arm_Sqlserver_Connection.Connected:=True;
       State_Memo.Lines.Add(datetostr(date())+' '+timetostr(time())+': 正在备份数据库 '+AInitial_Catalog+'的表'+trim(DB_TABLE_Name_QueryREMARK.Value)+'...');
       Backup_Table.TableName:=trim(DB_TABLE_Name_QueryREMARK.Value);
       Backup_Table.Open;
       DBFExport.FileName:=bkPath+trim(DB_TABLE_Name_QueryCVALUE.Value)+'_'+trim(DB_TABLE_Name_QueryREMARK.Value)+'.dbf';
       DBFExport.Execute;
       Backup_Table.Close;
       State_Memo.Lines.Add(datetostr(date())+' '+timetostr(time())+': 结束备份数据库 '+AInitial_Catalog+'的表'+trim(DB_TABLE_Name_QueryREMARK.Value));
      except
       State_Memo.Lines.Add(datetostr(date())+' '+timetostr(time())+': 不存在数据库 '+AInitial_Catalog+'或表'+trim(DB_TABLE_Name_QueryREMARK.Value));
      end;

      DB_TABLE_Name_Query.Next;
     end;

  Except
    State_Memo.Lines.Add(datetostr(date())+' '+timetostr(time())+': 备份不成功，请确认网络是否连通！');
  end;
end;

end.