请问怎样备份SQL 2000的数据库和恢复数据库。

解决方案 »

  1.   

    你是想在程序备份,还是在SQL中备份呀1
      

  2.   

    //数据备份我用delphi实现的:
    unit backup;interfaceuses
      Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
      Dialogs, ExtCtrls, StdCtrls, Buttons, ComCtrls, DB, ADODB;type
      TFbackup = class(TForm)
        Panel1: TPanel;
        StatusBar1: TStatusBar;
        Panel3: TPanel;
        btnClose: TBitBtn;
        btnBackup: TBitBtn;
        btnRestore: TBitBtn;
        pgBar: TProgressBar;
        Label1: TLabel;
        SaveDialog1: TSaveDialog;
        ADOQuery1: TADOQuery;
        procedure btnBackupClick(Sender: TObject);
        procedure btnRestoreClick(Sender: TObject);
        procedure btnCloseClick(Sender: TObject);
        procedure FormClose(Sender: TObject; var Action: TCloseAction);
      private
        { Private declarations }
      public
        { Public declarations }
      end;var
      Fbackup: TFbackup;implementation
    uses dldata;
    {$R *.dfm}procedure TFbackup.btnBackupClick(Sender: TObject);
    var
      i:integer;
      device_is:boolean;
    begin
    with pgbar do
    begin
    pgbar.Max:=100;
    pgbar.Min:=0;
    pgbar.Position:=0;
    pgbar.Step:=20;
    end;
    with savedialog1 do
      begin
        filter:='备份文件(*.dat)*.dat';
        defaultext:='dat';
        filename:='';
        options:=[ofhidereadonly,offilemustexist,ofpathmustexist];
        device_is:=false;
        if execute then
          begin
            statusbar1.Panels[1].Text:='正在备份中....';
    try
            with adoquery1 do
              begin
                close;
                sql.Clear;
                sql.Add('execute sp_helpdevice');
                open;
                pgbar.Stepit;
                first;
                while not eof do
                  begin
                    if trim(fieldbyname('device_name').AsString)='dl_1' then
                      begin
                        device_Is:=true;
                        break;
                      end;//if fieldbyname('name').AsString='dl_1' then
                      next;
                  end;//for i:=0 to recordcount-1 do
                  pgbar.Stepit;
                //-----------------------------------------------------------
                if device_Is then
                  begin
                    close;
                    sql.Clear;
                    sql.Add('execute sp_dropdevice ''dl_1''');
                    execsql;
                  end;//if not device_Is then
                  pgbar.Stepit;
                //------------------------------------------------------------
                close;
                sql.Clear;
                sql.Add('execute sp_addumpdevice ''disk'',''dl_1'','''+filename+'''');
                execsql;
                //--------------------------------------------------------
                pgbar.Stepit;
                close;
                sql.Clear;
                sql.Add('backup database dl to dl_1');
                execsql;
                pgbar.Stepit;
              end;//with adoquery1 do
    except
      showmessage('备份失败!');
    end;//try
          end;//if execute then
      end;//with savedialog1
      statusbar1.Panels[1].Text:='备份完成。';
    end;
    procedure TFbackup.btnRestoreClick(Sender: TObject);
    begin
    with pgbar do
    begin
    pgbar.Max:=100;
    pgbar.Min:=0;
    pgbar.Position:=0;
    pgbar.Step:=50;
    end;
    if messagedlg('恢复数据库后必须重新登陆!是否继续?',mtwarning,[mbok,mbcancel],0)=mrok then
    begin
    statusbar1.Panels[1].Text:='正在恢复中....';
    dm.dlconnection.Close;
    dm.Free;
    //try
    with adoquery1  do
      begin
        close;
        sql.Clear;
        sql.Add('restore database dl from dl_1');
        execsql;
      end;//with do
    //except
    //  showmessage('备份文件不存在!ss');
    //end;
    pgbar.StepIt;pgbar.StepIt;
    statusbar1.Panels[1].Text:='恢复完成.';
    application.MainForm.Caption:='正在关闭系统....';
    winexec(pchar(application.exename),sw_show);
    application.Terminate;
    end;//if messagedlg()=mrok
    end;procedure TFbackup.btnCloseClick(Sender: TObject);
    begin
    close;
    end;procedure TFbackup.FormClose(Sender: TObject; var Action: TCloseAction);
    begin
    action:=cafree;
    end;end.备份:
    begin
        pgbar.Max:=100;
        pgbar.Min:=0;
        pgbar.Step:=20;
         adoconnection1.Close;
         if adoconnection1.Connected then
         begin
            application.MessageBox('数据库数据正在使用','系统提示')
         end
         else
              begin
              try
                adocommand1.CommandText:='use master';
                pgbar.StepIt;
                adocommand1.Execute ;
                adocommand1.CommandText:='execute sp_helpdevice';
                pgbar.StepIt;
                adocommand1.Execute ;
                pgbar.stepit;
                adocommand1.CommandText:='backup database docmanage to disk=''c:\back1.bak'' with init';
                pgbar.StepIt;
                adocommand1.Execute ;
                adocommand1.CommandText:='backup log docmanage to back1';
                pgbar.StepIt;
                adocommand1.Execute;
                try
                   aDOConnection1.Connected:=true;
                except
                  ShowMessage('无法连接指定的数据库!');
                  application.Terminate;
                end;
                form1.Hide;
                  if messagedlg('数据库docmanage已成功备份到c:\back1.bak!是否继续?',mtwarning,[mbok],0)=mrok then
                     begin
                       form1.close
                     end;
              except
                 on exception do
                     if ADOConnection1.InTransaction then
                         ADOConnection1.RollbackTrans
    end;
    end;
    end;
    还原:
     begin
                pgbar.Max:=100;
                pgbar.Min:=0;
                pgbar.Step:=20;
                try
                    ADOCommand1.CommandText:='use master ';
                    pgbar.StepIt;
                     pgbar.StepIt;
                    adocommand1.Execute ;
                    adocommand1.CommandText:='RESTORE database docmanage FROM DISK = ''c:\back.bak''  with replace ';
                    pgbar.StepIt;                pgbar.StepIt;
                    pgbar.StepIt;
                    adocommand1.Execute ;
                     try
                         aDOConnection1.Connected:=true;
                     except
                        ShowMessage('无法连接指定的数据库!');
                        application.Terminate;
                     end;
                    form1.Hide;
                      if messagedlg('c:\back.bak已成功还原到数据库docmanage!继续?',mtwarning,[mbok],0)=mrok then
                         begin
                           form2.close
                      end;
                except
                   on exception do
                       if ADOConnection1.InTransaction then
                          ADOConnection1.RollbackTrans;
                end;
    end;
    end;
    你试试看了我这里能用!!!!用Delphi程序实现起来不难,在Form中放上一个ADOConnection和一个ADOCommand,设好ADOConnection的DefaultDatabase和Connected为True,ADOCommand的Connection为ADOConnection1,CommandText写为backup database mydatabase to disk='\\mycomputer\mypath\bk_mydat.dat' with init,你想把数据备份到哪台机器上,就在那台机器上设置一个完全共享的目录即可,想同时备份多个数据库就在CommandText中写多条语句,在Form的onShow事件中加入ADOCommand1.Execute,然后利用Windows自带的“计划任务”程序,让你编译好的程序在某个时间执行即可
      

  3.   

    SqlServer 备份数据库语句
    backup database tour  to disk='d:\back1.bak'SqlServer 还原数据库语句
    RESTORE DATABASE tour from disk='d:\back1.bak'搞个Query执行以上Sql就行了。
      

  4.   

    对了。以上tour是数据库名称,改为你的数据库名就行了。