如何在delphi源程序中操作sql数据库

Question

我现有一程序,运用delphi及sql server 2000,如何在源程序中对数据库进行备份与恢复?谢谢!

Answer 1

备份数据
function BackupDB(const sFileName:string):Boolean;
var
  ovAccess:OleVariant;
  SourceConnection,DestConnection:string;
  TmpFile,BackupFile:string;
begin
  //BackFile:='../DataBase/'+DateToStr(Date)+'.mdb';
  //TmpFile:='../DataBase/'+'temp'+DateToStr(Date)+'.mdb';
  BackupFile:='../DataBase/HouseSalesBackup.mdb';
  TmpFile:='../DataBase/TmpHouseSalesBackup.mdb';
  try
    screen.cursor:=crSQLWait;
    ovAccess:=CreateOleObject('JRO.JetEngine');
    try
      if FileExists(BackupFile) then
        begin
          ReNameFile(BackupFile,TmpFile);
        end;
      SourceConnection:='Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source='+sFileName+';Mode=Share Deny None;';
      DestConnection:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+BackupFile+';Mode=Share Deny None;Jet OLEDB:Encrypt Database=True;';
      ovAccess.CompactDatabase(SourceConnection,DestConnection);
      DeleteFile(TmpFile);
      WriteBackupDBTime(DateToStr(Date));
      result:=True;
    except
      RenameFile(TmpFile,BackupFile);
      result:=False;
    end
  finally
    ovAccess:=Unassigned;
    screen.cursor:=crDefault;
  end;
end;恢复数据
function RestoreDB:Boolean;
var
  sFile,TmpFile,BackupFile:string;
  p1,p2:PAnsiChar;
begin
  //BackFile:='../DataBase/'+DateToStr(Date)+'.mdb';
  //TmpFile:='../DataBase/'+'temp'+DateToStr(Date)+'.mdb';
  sFile:='../DataBase/HouseSales.mdb';
  BackupFile:='../DataBase/HouseSalesBackup.mdb';
  TmpFile:='../DataBase/TmpHouseSales.mdb';
  p1:=StrNew(PAnsiChar(BackupFile));
  p2:=StrNew(PAnsiChar(sFile));
  try
  screen.cursor:=crSQLWait;
    try
      if FileExists(BackupFile) then
        begin
          ReNameFile(sFile,TmpFile);
          CopyFile(p1,p2,true);
          //RenameFile(BackupFile,sFile);
        end;
      DeleteFile(TmpFile);
      result:=True;
    except
      RenameFile(TmpFile,sFile);
      result:=False;
    end
  finally
    strDispose(p1);
    strDispose(p2);
    screen.cursor:=crDefault;
  end;
end;

Answer 2

如果恢复是数据库正在用就会报错!如果你没有数据库的操作权限怎么办?
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;