备份和还原数据库？

Question

如题

Answer 1

SQL SERVER 2000 数据库

Answer 2

SQL Server2000数据库备份的方法很多
1。使用SQL Server的backup database和restore database命令，具体语法看SQL Server的帮助
2。用sp_detach_db和sp_attach_db分离和附加数据库，直接复制数据库文件
3。用alter database set offline/online脱机/连线数据库，直接复制数据库文件

Answer 3

http://topic.csdn.net/t/20050526/11/4036814.html
Procedure   HYUANDatebaseOk(DBname   ,SoureName   :   String);   
  begin   
      with   SqlCommand   do   
      begin   
          CommandText:='use   Master';//   
          Execute;   
          ClearMSSQLUser(DBname);   
          CommandText:='execute   sp_helpdevice';//系统存储过程   
          Execute;   
          CommandText:='Restore   database   '+DBname+'     From   disk='''+SoureName+'''   with   replace';   
          Execute;   
      end;   
  end;//DBname   备份库名称     //SoureName   那个盘   
    
  Procedure   BakUpdatebameOk(BkDBname,MDFileStr   :   String);   
  begin   
      with   SqlCommand   do   
      begin   
          CommandText:='use   Master';   
          Execute;   
          CommandText:='execute   sp_helpdevice';//系统存储过程   
          Execute;   
          CommandText:='backup   database   '+BkDBname+'   to   disk='''+MDFileStr+'''   with   init';   
          Execute;   
          CommandText:='Use   '+BkDBname;   
          Execute;   
      end;   
  end;   
procedure   ClearMSSQLUser(DBname   :   String);   
  var   
      SelectQuery   :   TAdoQuery;   
      ClearProUser   :   TAdoQuery;   
  begin   
      try   
          SelectQuery   :=TAdoQuery.Create(nil);   
          ClearProUser:=TAdoQuery.Create(nil);   
          SelectQuery.Connection   :=SqlConnection;   
          ClearProUser.Connection:=SqlConnection;   
          SelectQuery.SQL.Clear;   
          ClearProUser.SQL.Clear;   
          SelectQuery.SQL.Add('select   *   from   dbo.sysobjects   where   '+   
                                                  'id   =   object_id(N''[dbo].[p_killspid]'')   and   OBJECTPROPERTY(id,   N''IsProcedure'')   =   1');   
          SelectQuery.Open;   
          if   SelectQuery.Eof   then   
          begin   
              SqlCommand.CommandText:='create   proc   p_killspid   '   +   
                                                                '@dbname   sysname                 '   +   
                                                                'as     '+   
                                                                'declare   @s   nvarchar(1000)   '   +   
                                                                'declare   tb   cursor   local   for   '   +   
                                                                'select   s=''kill   ''+cast(spid   as   varchar)   '+   
                                                                'from   master..sysprocesses   '+   
                                                                'where   dbid=db_id(@dbname)   '+   
                                                                'open   tb   '+   
                                                                'fetch   next   from   tb   into   @s     '+   
                                                                'while   @@fetch_status=0       '+   
                                                                'begin     '+   
                                                                '     exec(@s)     '+   
                                                                '     fetch   next   from   tb   into   @s     '+   
                                                                'end   '+   
                                                                'close   tb   '+   
                                                                'deallocate   tb';   
              SqlCommand.Execute;   
          end;   
          SqlCommand.CommandText:='exec   p_killspid     '''+DBname+'''';   
          SqlCommand.Execute;   
      finally   
          freeandnil(SelectQuery);   
          freeandnil(ClearProUser);   
      end;   
  end;

Answer 4

给你个参考的例子：
Access:
//数据备份（创建一个新的备份文件）。
procedure TMainForm.BitBtn1Click(Sender: TObject);
var
   F:TShFileOpStruct;
   DataBackupFileName:string;
begin
   if MessageDlg('备份数据库将创建一个新的Access数据备份文件。'+#13+#10+
                 ''+#13+#10+
                 '你确定要备份数据库吗？',mtConfirmation,
                 [mbyes,mbno], 0) = mrno then Exit;
   F.wnd:=Handle;
   F.wFunc:=Fo_COPY; //操作方式：拷贝数据
   F.pFrom:=pAnsichar(ExtractFileDir(Application.ExeName)+'\Data\FB.mdb'+#0);
   OpenDialog1.Filter:='Access文件(*.mdb)|*.mdb';
   OpenDialog1.Title:='选择备份路径和文件名';
   OpenDialog1.InitialDir := ExtractFileDir(Application.ExeName)+'\Backup';
   if OpenDialog1.Execute then
   F.pTo:=pAnsichar(OpenDialog1.FileName+'[Access备份'+//构造备份文件名
          FormatDateTime('yyyy-mm-dd',Now)+']'+'.mdb');//进行文件备份
   F.fFlags:=FOF_ALLOWUNDO or FOF_RENAMEONCOLLISION;//操作选项
   if ShFileOperation(F)=0 then
      MessageDlg(#13+#10+' Access数据文件备份成功！',mtInformation,[mbYes],0)
   else
      MessageDlg(#13+#10+' Access数据文件备份失败！' ,mtWarning,[mbYes],0);
end;//数据恢复（先删除原数据库文件，再把备份数据库文件写入）。
procedure TMainForm.BitBtn2Click(Sender: TObject);
var
   F:TShFileOpStruct;
begin
   if MessageDlg('数据还原将重写当前Access数据。'+#13+#10+
                 ''+#13+#10+
                 '你确定要还原备份数据吗？',mtConfirmation,
                 [mbyes,mbno], 0) = mrno then Exit;
   F.wnd:=Handle;
   F.wFunc:=Fo_Delete;//操作方式：删除数据
   F.pFrom:=pAnsichar(ExtractFileDir(Application.ExeName)+'\Data\FB.mdb'+#0);
   F.fFlags:=FOF_ALLOWUNDO or FOF_RENAMEONCOLLISION;//设置操作选项
   DM.Dataconn.Connected:=False;//断开连接
   //将文件删除至回收站
   //if ShFileOperation(F)<>0 then
   //  MessageDlg('删除Access原始数据文件出错。数据库恢复失败！',mtError,[mbYes],0)
   //else
   begin
      //设置文件选项
      OpenDialog1.Filter:='Access文件(*.mdb)|*.mdb';
      OpenDialog1.InitialDir:=ExtractFileDir(Application.ExeName)+'\Backup\';
      OpenDialog1.Title:='请选择备份文件';
      if OpenDialog1.Execute then//打开拷贝文件对话框
      //拷贝文件
      if CopyFile(PChar(OpenDialog1.FileName),
         PChar(ExtractFileDir(Application.ExeName)+'\Data\FB.mdb'),False) then
         MessageDlg('Access数据文件恢复完成。数据库恢复成功。',mtInformation,[mbYes],0);
      DM.Dataconn.Connected:=True;//恢复连接
   end;
end;Paradox数据库备份
procedure TDataBackupForm.BitBtn1Click(Sender: TObject);
begin
   if MessageDlg('备份数据库将重写以前的数据备份。'+#13+#10+
                 ''+#13+#10+
                 '你确定要备份数据库吗？',mtConfirmation,
                 [mbyes,mbno], 0) = mrno then Exit;
   if Trim(Edit1.Text)='' then
   begin
      MessageDlg('请选择数据库备份路径！',mtInformation,[mbYes],0);
      Edit1.setfocus;
      Exit;
   end;
   Path:=False;
   ProgressBar1.Visible:=True;
   try
   begin
      dir:=dir+'\data\';//获取源数据库所在目录
      createdir(pchar(Edit1.Text));//创建备份目录
      //备份数据表（cl）
      CopyFile(pchar(dir+'cl.DB'),pchar(Edit1.Text+'\'+'cl.DB'),False);
      CopyFile(pchar(dir+'cl.FAM'),pchar(Edit1.Text+'\'+'cl.FAM'),False);
      CopyFile(pchar(dir+'cl.PX'),pchar(Edit1.Text+'\'+'cl.PX'),False);
      CopyFile(pchar(dir+'cl.TV'),pchar(Edit1.Text+'\'+'cl.TV'),False);
      //备份数据表（clcl）
      CopyFile(pchar(dir+'clcl.DB'),pchar(Edit1.Text+'\'+'\clcl.DB'),False);
      CopyFile(pchar(dir+'clcl.FAM'),pchar(Edit1.Text+'\'+'\clcl.FAM'),False);
      CopyFile(pchar(dir+'clcl.PX'),pchar(Edit1.Text+'\'+'\clcl.PX'),False);
      CopyFile(pchar(dir+'clcl.TV'),pchar(Edit1.Text+'\'+'\clcl.TV'),False);
      //备份数据表（pz）
      CopyFile(pchar(dir+'pz.DB'),pchar(Edit1.Text+'\'+'\pz.DB'),False);
      CopyFile(pchar(dir+'pz.FAM'),pchar(Edit1.Text+'\'+'\pz.FAM'),False);
      CopyFile(pchar(dir+'pz.PX'),pchar(Edit1.Text+'\'+'\pz.PX'),False);
      CopyFile(pchar(dir+'pz.TV'),pchar(Edit1.Text+'\'+'\pz.TV'),False);
      //备份数据表（users）
      CopyFile(pchar(dir+'users.DB'),pchar(Edit1.Text+'\'+'\users.DB'),False);
      CopyFile(pchar(dir+'users.FAM'),pchar(Edit1.Text+'\'+'\users.FAM'),False);
      CopyFile(pchar(dir+'users.PX'),pchar(Edit1.Text+'\'+'\users.PX'),False);
      CopyFile(pchar(dir+'users.TV'),pchar(Edit1.Text+'\'+'\users.TV'),False);
      //备份数据表（ylfb）
      CopyFile(pchar(dir+'ylfb.DB'),pchar(Edit1.Text+'\'+'\ylfb.DB'),False);
      CopyFile(pchar(dir+'ylfb.FAM'),pchar(Edit1.Text+'\'+'\ylfb.FAM'),False);
      CopyFile(pchar(dir+'ylfb.PX'),pchar(Edit1.Text+'\'+'\ylfb.PX'),False);
      CopyFile(pchar(dir+'ylfb.TV'),pchar(Edit1.Text+'\'+'\ylfb.TV'),False);
      Thread.execute;//执行线程
      //设置界面
      ShellTreeView1.Visible:=False;
      DataBackupForm.Height:=312;
      MessageDlg(#13+#10+' 数据库备份成功！',mtInformation,[mbYes],0);
      ProgressBar1.Visible:=False;
      ProgressBar1.Position:=0;
   end;
   except
      ShellTreeView1.Visible:=False;
      DataBackupForm.Height:=312;
      MessageDlg(#13+#10+'数据库备份失败！' ,mtWarning,[mbYes],0);
   end;
   Path:=True;
   ShellTreeView1.Visible:=False;
   DataBackupForm.Height:=312;
   BitBtn3.Caption:='选择目录';
end;
SQL方式
//获取数据备份目的路径
procedure TDataBackForm.FormShow(Sender: TObject);
var
  SourcePath: string;
begin
   Animate1.Visible:=False;
   SourcePath:=ExtractFileDir(Application.ExeName);
   //获取应用程序路径
   if (StrLen(PChar(SourcePath))<>3) then
   begin
      SourcePath:=SourcePath+'\';
      //Edit1.Text:=SourcePath+'BackupData\Data'+
      //      formatdatetime('yyyymmdd',date)+'.back';
      //设置路径名＋文件名
      Edit1.Text:=SourcePath+'BackupData\'+'BackupData.back';
   end;
end;//改变恢复数据按钮的使能状态（随数据备份目的路径的选择而变化）
procedure TDataBackForm.Edit1Change(Sender: TObject);
begin
   if trim(Edit1.Text)<>'' then
    BackData.Enabled:=True
  else
    RecoverData.Enabled:=False;//当路径框中为空时候， 恢复数据不可用
end;//浏览数据备份目的路径
procedure TDataBackForm.BitBtn4Click(Sender: TObject);
begin
   //OpenDialog1.Filter:='备份文件(*.back)|*.back|所有文件(*.*)|*.*';//设置备份文件后缀
   OpenDialog1.Filter:='所有文件(*.*)|*.*|备份文件(*.back)|*.back';//设置备份文件后缀
   OpenDialog1.InitialDir:=ExtractFileDir(Application.ExeName)+'\backupdata';
   //设置恢复路径
   if OpenDialog1.Execute then
   Edit1.Text:=OpenDialog1.FileName;
   //if SaveDialog1.Execute then
   //Edit1.Text:=OpenDialog1.FileName;
end;//备份数据
procedure TDataBackForm.BackDataClick(Sender: TObject);
var
   DataPath:string;
begin
   if Trim(Edit1.Text)='' then
   begin
      MessageDlg(#13+#10+'请选择数据库备份路径！',mtInformation,[mbYes],0);
      Edit1.setfocus;
      Exit;
   end;
   Caption:='正在备份数据....';
   Animate1.Visible:=True;
   Animate1.Active:=True;//备份数据库命令语句
   if ExtractFileExt(Edit1.Text)='.back' then
      DataPath:=''
   else
      DataPath:='.back';
   Edit1.Text:=Edit1.Text+DataPath;
   DM.backcomm.CommandText:='backup database Data1 to disk='''+Edit1.Text+'''';
   try
      DM.backcomm.Execute;//执行备份
      Animate1.Active:=False;
      Animate1.Visible:=False;
      MessageDlg(#13+#10+'数据库备份成功！',mtInformation,[mbYes],0);
   except
      Animate1.Active:=False;
      Animate1.Visible:=False;
      MessageDlg(#13+#10+'数据库备份失败！',mtWarning,[mbYes],0);
   end;
   Caption:='数据备份和恢复';
end;呵呵，例子看看

Answer 5

//备份数据
Procedure AutoBackData();
var
   AdConnDB: TADOConnection;
   qryTemp:TADOQuery;
   strBackUpName,StrPicPath:string;
begin
   if Comm2.blnAutoBackData = false then exit;
   if (comm2.strBackDataPath ='') or (DirectoryExists(comm2.strBackDataPath)=false) then
      StrPicPath :=uDataModule.AppPath +'DataBackUp\'
   else
      StrPicPath := Comm2.strBackDataPath + '\';   //strBackUpName := FormatDateTime('yyyymmdd',date)+ '_'+ FormatDateTime('hhmmss',now)+ '.bak';
   strBackUpName := FormatDateTime('yyyymmdd',date) + '.bak';
   strBackUpName := StrPicPath + '\' + strBackUpName;
   AdConnDB:=TADOConnection.Create(nil);
   if condb(AdConnDB)=false then
   begin
      Application.MessageBox('服务器连接失败，请重新配置！','提示！',0+64);
      exit;
   end;
   uWaite.ShowWaite('正在备套数据，请稍候......');
   Application.ProcessMessages ;
   qryTemp := TADOQuery.Create(nil);
   qryTemp.Connection:=AdConnDB;
   with qryTemp do
   begin
      Close ;
      SQL.Clear ;
      SQL.Add('Use Master BackUp DataBase '+comm.ztname+' to disk='''+ strBackUpName+'''');
      ExecSQL ;
   end;
   qryTemp.Free;
   uWaite.UnloadWaite;
end;//连接Master数据库
function ConDB(AdConnDB: TADOConnection):boolean;
var
  strConn,strZtSys:string ;  //数据库连接串（帐套、ztsystem）
  File_name:TIniFile ;       //配置文件
  servername,userid,password:string;
begin
   try
      File_name :=TIniFile.Create(ExtractFilePath(Application.ExeName ) +'\ServerSet.ini');
      servername :=File_name.ReadString('DataBase','DataServer',servername);
      userid :=File_name.ReadString('DataBase','UserId',userid);
      password :=File_name.ReadString('DataBase','Password',password);
      AdConnDB.Close ;
      AdConnDB.ConnectionString :='Provider=SQLOLEDB.1;Password='+ password +
         ';Persist Security Info=True;User ID='+userid+
         ';Initial Catalog=master;Data Source='+servername;
      AdConnDB.LoginPrompt :=False ;
      AdConnDB.Open ;
      ConDB:=true;
   except
      Application.MessageBox('服务器连接失败，请重新配置！','提示！',0+64);
      ConDB:=false;
  end;
end;