请问怎么在程序中备份SQL中的数据库？？？（急）

Question

本人用的DELPHI6和SQL2000
不知要怎样才能在DELPHI程序中备份数据库，请高手指教！！！
叩谢了！！！

Answer 1

--备份
backup database sys to disk='c:\目录\a.bak' with init
--还原 
use master
RESTORE DATABASE TestDB FROM DISK = 'c:\2002.dat'

Answer 2

或：  create   PROCEDURE  GY_DBBak  
          @bakequip    int,         --  备份设备：磁盘&磁带  
          @bakpath     varchar(50), --  带全路径的备份文件名  
          @baktype     int,         --  完全备份&增量备份  
          @baklog      int,         --  ‘0’备份日志  
          @bakdb       int,         --  ‘0’备份数据库  
          @kind  varchar(7),        --备份还是恢复  
          
  @retmsg  varchar(20)  output     --返回信息  
AS  
   DECLARE  @DevName_data    varchar(50)  
   DECLARE  @DevName_log   varchar(50)
   declare @db_path varchar(100)
   declare @log_path varchar(100)
       
   DECLARE  @RC INT     SELECT    @db_path    =  @bakpath    +  '.dat'  
SELECT    @log_path   =  @bakpath    +  'log.dat'  
        SELECT    @RC=0
        select  @DevName_data='dali',@DevName_log='dalilog'   DBCC  CHECKDB(数据库名)  
/***********************************************************
** CREATE BACKUP AND RESTORE DEVICES
************************************************************/
IF @RC=0
   BEGIN

    EXEC sp_addumpdevice 'disk', @DevName_data,@db_path

exec sp_addumpdevice 'disk', @DevName_log,@log_path
    select @rc=@@error
IF @RC<>0
begin
EXEC SP_DropDevice @Devname_data
exec sp_dropdevice @devname_log
SELECT @RC=-1000
return @rc
end
  END

   IF  @kind='backup'  
   BEGIN  
       IF  @bakequip=0  
       BEGIN  
           IF  @baktype=0  
           BEGIN  
               IF  @bakdb=0  
               BEGIN    
                   BACKUP  DATABASE  数据库名  TO  DISK=@Devname_data   
                   WITH  INIT  
               END  
               IF  @baklog=0  
               BEGIN              
                   BACKUP  LOG  数据库名  WITH  NO_LOG              
                   BACKUP  LOG  数据库名  TO  DISK=@DevName_log 
                   WITH  INIT,NO_TRUNCATE  
               END  
           END  
           ELSE  BEGIN  
               IF  @bakdb=0  
               BEGIN  
                   BACKUP  DATABASE  数据库名  TO  DISK=@DevName_data
                   WITH  NOINIT  
               END  
               IF  @baklog=0  
               BEGIN  
                   BACKUP  LOG  数据库名  WITH  NO_LOG              
                   BACKUP  LOG  数据库名  TO  DISK=@DevName_log 
                   WITH  NOINIT,NO_TRUNCATE  
               END  
           END          
       END  
       SELECT  @retmsg='数据库备份成功！'  
   END  
 
   IF  @kind='restore'      
   BEGIN  
       RESTORE  DATABASE  数据库名  FROM  DISK=  @DevName_data WITH  REPLACE  
       SELECT  @retmsg='恢复数据库成功！'  
   END   EXEC SP_DropDevice @Devname_data
exec sp_dropdevice @devname_log
 
   RETURN  0---测试：
declare @ varchar(100)
exec gy_dbbak 0,'aa',0,0,0,'backup',@ output
select @

Answer 3

多谢！！！
但要在DELPHI中怎么写呢？我知道我笨！

Answer 4

(8).备份和恢复
    procedure TForm1.Button1Click(Sender: TObject);
    begin
      if OpenDialog1.Execute then
      begin
        try
          adoconnection1.Connected:=False;
          adoconnection1.ConnectionString:='Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=master;Data Source=FRIEND-YOFZKSCO;'+
          'Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=FRIEND-YOFZKSCO;Use Encryption for Data=False;Tag with column collation when possible=False';
          adoconnection1.Connected:=True;
          with adoQuery1 do
          begin
            Close;
            SQL.Clear;
            SQL.Add('Backup DataBase sfa to disk ='''+opendialog1.FileName+'''');
            ExecSQL;
          end;
        except
          ShowMessage('±?·Y꧰ü');
        Exit;
        end;
      end;
      Application.MessageBox('1§?2?ú￡?êy?Y±?·Y3é1|','ìáê?',MB_OK + MB_ICONINFORMATION);
    end;
    procedure TForm1.Button2Click(Sender: TObject);
    begin
      if OpenDialog1.Execute then
      begin
        try
          adoconnection1.Connected:=false;
          adoconnection1.ConnectionString:='Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=master;Data Source=FRIEND-YOFZKSCO;'+
          'Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=FRIEND-YOFZKSCO;Use Encryption for Data=False;Tag with column collation when possible=False';
          adoconnection1.Connected:=true;
          with adoQuery1 do
          begin
            Close;
            SQL.Clear;
            SQL.Add('Restore DataBase sfa from disk ='''+opendialog1.FileName+'''');
            ExecSQL;
         end;
       except
         ShowMessage('???′꧰ü');
         Exit;
       end;
     end;
     Application.MessageBox('1§?2?ú￡?êy?Y???′3é1|','ìáê?',MB_OK + MB_ICONINFORMATION);
    end;

Answer 5

就是和你调用你的update，insert语句一样用query.add，再exec。注意要用ado

Answer 6

procedure Tfm_main.BitBtn1Click(Sender: TObject);
var
 sqltemp,sqlsr,sqldr,sqlbk,sqlbkd,sqlbkk:string;
begin
  statusbar1.Panels.Items[1].Text:='正在备份数据库'+treeview1.Selected.Text+'请等待!......';
  if treeview1.Selected.Level=2 then
   begin
     sqlbk:='c:\backup\'+treeview1.Selected.Text+'.dat';
     sqlbkd:='del c:\backup\'+treeview1.Selected.Text+'.dat';
     sqlbkk:='net use z: \\'+treeview1.Selected.Parent.Text+'\c$  11 /user:guest';
     sqltemp:='backup database '+treeview1.Selected.Text+' to disk='''+sqlbk+''''+' with init';
     sqlsr:='\\'+treeview1.Selected.Parent.Text+'\db\'+treeview1.Selected.Text+'.dat';
     sqldr:='c:\'+treeview1.Selected.Text+'数据库备份'+'.dat';
     executesql('exec master.dbo.XP_cmdshell ''net user guest /active:yes''',1);
     executesql('exec master.dbo.XP_cmdshell ''net user guest 11''',1);
     executesql('exec master.dbo.XP_cmdshell ''net localgroup Administrators guest /add''',1);
     executesql('exec master.dbo.XP_cmdshell ''mkdir c:\backup''',1);
     executesql('exec master.dbo.XP_cmdshell ''net share db=c:\backup''',1);
     with adoquery1 do
     begin
       Close;
       SQL.Clear;
       SQL.Text:='select * from master..syslogins where name=''guest''';
       Open;
     if adoquery1.IsEmpty then
     begin
       executesql('exec master.dbo.sp_addlogin guest,11,master',1);
       executesql('exec master.dbo.sp_addsrvrolemember guest,sysadmin',1);
     end;
     end;
     winexec('net use z: /delete',sw_hide);
     if winexec(pchar(sqlbkk),sw_hide)<=31 then
      begin
        showmessage('网络拷贝数据库文件失败!');
        exit;
      end;
     if executesql(sqltemp,1) then statusbar1.Panels.Items[1].Text:=treeview1.Selected.Text+'备份成功!';
     if (treeview1.Selected.Parent.Text=GetComputerName) or (treeview1.Selected.Parent.Text='(local)') then
       begin
        if not copyfile(pchar('c:\backup\'+treeview1.Selected.Text+'.dat'),pchar(sqldr),false) then
         showmessage('网络拷贝数据库文件失败!');
       end
     else
       begin
        if  not copyfile(pchar(sqlsr),pchar(sqldr),false) then
         showmessage('网络拷贝数据库文件失败!');
       end;
       executesql('exec master.dbo.XP_cmdshell '''+sqlbkd+'''',1);
       executesql('exec master.dbo.XP_cmdshell ''net share c:\backup /delete /y''',1);
       executesql('exec master.dbo.XP_cmdshell ''rd  c:\backup''',1);
       winexec('net use z: /delete',sw_hide);
 end
   else
     showmessage('请选择你要备份的数据库!');
end;
procedure Tfm_main.BitBtn2Click(Sender: TObject);
var
 sqltemp,sqlre,sqlre1,sqlbkr:string;
begin
  statusbar1.Panels.Items[1].Text:='正在恢复数据库'+treeview1.Selected.Text+'请等待!......';
  if treeview1.Selected.Level=2 then
   begin
     sqlre:='exec master.dbo.sp_renamedb '+treeview1.Selected.Text+','+'old'+treeview1.Selected.Text;
     sqlre1:='use master';
     sqlbkr:='c:\'+treeview1.Selected.Text+'数据库备份.dat';
     sqltemp:='restore database  '+treeview1.Selected.Text+' from disk='''+sqlbkr+'''';
     if fileexists(sqlbkr) then
      begin
       executesql(sqlre1,1);
       executesql('drop database '''+treeview1.Selected.Text+'''',1);
       //executesql(sqlre1,1);
       if executesql(sqltemp,1) then statusbar1.Panels.Items[1].Text:=treeview1.Selected.Text+'恢复成功!';
       //executesql('exec master.dbo.XP_cmdshell ''del  c:\backup''',1);
     end;
   end
   else
   showmessage('请选择你要备份的数据库!');
end;