数据库的备份及还原

实例应用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/是服务器的目录，这点要注意