delphi数据库的备份及还原

原作者: [db:作者] 来自: [db:来源] 收藏邀请

实例应用1：

//备份procedure TF_DataBaseBackUp.Btn_bfClick(Sender: TObject);
var i:integer;
begin
  if SaveDialog1.Execute then
  begin
    ADOConnection1.Connected:=False;
    ADOConnection1.ConnectionString:=StringReplace(GetConnectionString,'Taxi','master',[rfReplaceAll]);
    ADOConnection1.Connected:=True;
    ADOQuery1.SQL.Text :='backup database Taxi to disk='+QuotedStr(SaveDialog1.FileName);
    try
      Btn_bf.Caption :=' 正在备份…';
      self.repaint;
      ADOQuery1.execsql;
      for i:=1 to 100 do
      begin
        Btn_bf.Caption :=' 备份中…'+inttostr(i)+'%';
        self.repaint;
        sleep(50);
      end;
      ShowMessage('数据备份操作已经成功完成!');
    except
      ShowMessage('数据备份时出错!请重试。');
    end;
    Btn_bf.caption:='  开始备份';
    self.repaint;
  end;
end;
//恢复
procedure TF_DataBaseBackUp.Btn_hfClick(Sender: TObject);
var i:integer;
begin
  if Application.MessageBox('该操作会把当前程序数据改为备份时的状态，您确定要这么做吗？','提示',mb_okcancel+MB_ICONQUESTION )<>idok then exit;
  if OpenDialog1.Execute then
  begin
    try
      ADOConnection1.Connected:=False;
      ADOConnection1.ConnectionString:=StringReplace(GetConnectionString,'Taxi','master',[rfReplaceAll]);
      ADOConnection1.Connected:=True;
      Btn_hf.Caption :=' 清除数据库连接...';
      self.Repaint;
      ClearDBConnections();
      Btn_hf.Caption :=' 正在恢复...';
      self.repaint;
      ADOQuery1.SQL.Text :='restore database Taxi from disk='+QuotedStr(OpenDialog1.FileName);
      ADOQuery1.execsql;
      for i:=1 to 100 do
      begin                          
        Btn_hf.Caption :=' 恢复中…'+inttostr(i)+'%';
        self.repaint;
        sleep(50);
      end;
      Btn_hf.caption:='  开始恢复';
      self.repaint;
    except
      Application.MessageBox('数据恢复时出错!请重试。','提示',64);
      Btn_hf.caption:='  开始恢复';
      self.repaint;
      exit;
    end;
    application.MessageBox('数据库已成功恢复!请重新启动系统!','提示:',mb_ok+mb_iconinformation);
  end;
end;
//------------------------------------------------------------------------------
//函数名称: ClearDBConnections
//函数功能: 清除数据库连接
//------------------------------------------------------------------------------

procedure TF_DataBaseBackUp.ClearDBConnections;
var vspid:string;
begin
  //覆盖数据库,清除数据库现有连接
  ADOQuery1.Close;
  ADOQuery1.SQL.Text:='select   spid   from   sysprocesses   where   dbid=db_id(''Taxi'')';
  ADOQuery1.Open;
  while not ADOQuery1.Eof do
  begin
    vspid:=ADOQuery1.FieldByName('spid').AsString;
    ADOQuery2.Close;
    ADOQuery2.SQL.Text:='kill '+vspid;
    ADOQuery2.ExecSQL;
    ADOQuery1.Next;
  end;
end;

实例应用2：

procedure TF_BF.BitBtn1Click(Sender: TObject); 
var inif:Tinifile; 
T:string; 
begin 
  inif:=Tinifile.Create(ExtractFilePath(Paramstr(0))+'data/SysSet.ini'); 
  T:=inif.ReadString('Data','DbType',''); 
  IF LENGTH(TRIM(EDIT2.Text))=0 THEN 
  BEGIN 
    APPLICATION.MessageBox('请指定数据备份的文件名！','林康软件',MB_OK+MB_ICONWARNING); 
  END 
  ELSE 
  BEGIN 
    if T='SQLserver' then 
    begin 
      ADOQUERY1.Close; 
      ADOQUERY1.SQL.Clear; 
      ADOQUERY1.SQL.Add('USE MASTER'); 
      ADOQUERY1.SQL.ADD('BACKUP DATABASE GZGL to disk='+#39+SAVEDIALOG1.FileName+#39+' with init');
      TRY 
        ADOQUERY1.ExecSQL; 
        APPLICATION.MessageBox('数据备份操作成功！','林康软件',MB_OK+MB_ICONWARNING); 
      EXCEPT 
        APPLICATION.MessageBox('数据备份操作失败！','林康软件',MB_OK+MB_ICONWARNING); 
      END; 
      close; 
      TRY 
        F_main.ADOConnection1.Close; 
        F_main.ADOConnection1.Open; 
      EXCEPT 
        APPLICATION.MessageBox('该系统需要重新启动, 请退出！','林康软件',MB_OK+MB_ICONWARNING); 
        application.Terminate; 
      END; 
    end; 
    if T='Access' then 
    begin 
      F_MAIN.ADOConnection1.Close; 
      TRY 
        COPYFILE(PCHAR(ExtractFilePath(Paramstr(0))+'DATA/gzgl.mdb'),PCHAR(EDIT2.Text),FALSE); 
        APPLICATION.MessageBox('数据备份操作成功！','林康软件',MB_OK+MB_ICONWARNING); 
      EXCEPT 
        APPLICATION.MessageBox('数据备份操作失败！','林康软件',MB_OK+MB_ICONWARNING); 
      END; 
      F_MAIN.ADOConnection1.Open; 
    end; 
  END; 
end; 


恢复: 
procedure TF_HF.BitBtn1Click(Sender: TObject); 
var 
  t:string; 
  inif:Tinifile; 
begin 
  inif:=Tinifile.Create(ExtractFilePath(Paramstr(0))+'data/SysSet.ini'); 
  T:=inif.ReadString('Data','DbType',''); 
  IF LENGTH(TRIM(EDIT2.Text))=0 THEN 
  BEGIN 
    APPLICATION.MessageBox('请指定数据恢复的文件名！','林康软件',MB_OK+MB_ICONWARNING); 
  END 
  ELSE 
  BEGIN 
    if T='SQLserver' then 
    begin 
      if MESSAGEDLG('数据恢复操作将覆盖现有的数据,需要继续吗？',mtconfirmation,[MBYES,MBNO],1) <>Mryes THEN exit; 
      ADOQUERY1.Close; 
      ADOQUERY1.SQL.Clear; 
      ADOQUERY1.SQL.ADD('use master declare  @spid  int'); 
      ADOQUERY1.SQL.ADD('declare  getspid  cursor  for'); 
      ADOQUERY1.SQL.ADD('select  spid  from  sysprocesses  where  dbid=db_id('+#39+'GZGL'+#39+')'); 
      ADOQUERY1.SQL.ADD('open  getspid'); 
      ADOQUERY1.SQL.ADD('fetch  next  from  getspid  into  @spid'); 
      ADOQUERY1.SQL.ADD('while  @@fetch_status   <  >-1'); 
      ADOQUERY1.SQL.ADD('begin'); 
      ADOQUERY1.SQL.ADD('exec('+#39+'kill '+#39+'+@spid)'); 
      ADOQUERY1.SQL.ADD('fetch  next  from  getspid  into  @spid'); 
      ADOQUERY1.SQL.ADD('end'); 
      ADOQUERY1.SQL.ADD('close  getspid'); 
      ADOQUERY1.SQL.ADD('deallocate  getspid'); 
      ADOQUERY1.SQL.ADD('RESTORE DATABASE GZGL FROM disk='+#39+OPENDIALOG1.FileName+#39+' WITH REPLACE');
      TRY 
        ADOQUERY1.ExecSQL; 
        APPLICATION.MessageBox('数据恢复操作成功！','林康软件',MB_OK+MB_ICONWARNING); 
      EXCEPT 
        APPLICATION.MessageBox('数据恢复操作失败！','林康软件',MB_OK+MB_ICONWARNING); 
      END; 
      close; 
      TRY 
        F_main.ADOConnection1.Close; 
        F_main.ADOConnection1.Open; 
      EXCEPT 
        APPLICATION.MessageBox('该系统需要重新启动, 请退出！','林康软件',MB_OK+MB_ICONWARNING); 
        application.Terminate; 
      END; 
    end; 
    if T='Access' then 
    begin 
      F_MAIN.ADOConnection1.Close; 
      TRY 
        COPYFILE(PCHAR(EDIT2.Text),PCHAR(ExtractFilePath(Paramstr(0))+'DATA/Gzgl.mdb'),FALSE); 
        APPLICATION.MessageBox('数据恢复操作成功！','林康软件',MB_OK+MB_ICONWARNING); 
      EXCEPT 
        APPLICATION.MessageBox('数据恢复操作失败！','林康软件',MB_OK+MB_ICONWARNING); 
      END; 
      F_MAIN.ADOConnection1.Open; 
    end; 
  END; 

end;

实例应用3： 

最简单的sql语句：备份与还原sql   server自带的数据库   
    
  在服务器上备份：   
  use   northwind   
  backup   database   northwind   to   disk=d:/northwind_bak.dat   with   init   
    
  restore   database   northnwind   from   disk   =   d:/northwind_bak.dat   
    
  ------------------------------------------------------------------   
  备份数据库这一操作在客户机上实现     
  客户机：machine   
  共享目录：share     

    
  backup:   
  bakcup   database   dbname   to   disk=//machine/share/data.bak   with   init   
  //machine/share目录要有写权限。   
    
  restore:   
  restore   database   dbname   from   disk=//machine/share/data.bak         
    
  //   
  备注：restore   语句有很多的选项，可以查看企业管理器的在线帮助。如下   
      with   replace,   move   dbname_dat   to   c:/mssql7/data/dbname.mdf,     
      move   dbname_log   to   c:/mssql7/data/dbname.log   
  其中c:/mssql7/data/是服务器的目录，这点要注意