数据备份能成功,但恢复时提示数据库没有被关闭!!
unit Unit2;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ExtCtrls, ComCtrls, StdCtrls, Buttons;type
TBK = class(TForm)
Panel1: TPanel;
Panel2: TPanel;
Label1: TLabel;
Label2: TLabel;
bckedata: TBitBtn;
RecoverData: TBitBtn;
cancelBtn: TBitBtn;
Edit_path: TEdit;
OpenDialog1: TOpenDialog;
a1: TAnimate;
procedure FormCreate(Sender: TObject);
procedure bckedataClick(Sender: TObject);
procedure RecoverDataClick(Sender: TObject);
procedure cancelBtnClick(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
BK: TBK;implementationuses Dataunit;{$R *.dfm}procedure TBK.FormCreate(Sender: TObject);
var
sourcepath:string;
begin
self.Caption:=application.Title +'- 数据备份'; self.Caption:=application.Title +'- 数据备份' ;
sourcepath:=Extractfiledir(application.exename);
if (strlen(pchar(sourcepath))<>3)then
sourcepath:=sourcepath+'\';
edit_path.Text:=sourcepath +'Backdata\wei'+ formatdatetime('yyyymmdd',date)+'.back';end;procedure TBK.bckedataClick(Sender: TObject);
begin
self.Caption:='正在备份数据';
a1.Active:=true;
zh.back.commandtext:='backup database xq to disk='''+ edit_path.text+'''';
try
zh.back.execute;
showmessage('备份成功');
a1.active:=false;
except
showmessage('备份失败');
a1.active:=false;
end;
self.caption:='数据备份和恢复';
end;procedure TBK.RecoverDataClick(Sender: TObject);
var datapath:string;
begin
self.caption:='正在恢复数据......';
zh.zhconn.Close;
zh.ZHConn.Connected:=false;
opendialog1.Filter:='备份文件(*.back)|*.back|所有文件(*.*)|*.*';
opendialog1.InitialDir:=extractfiledir(application.exename)+'\backupdata';
if opendialog1.execute then
datapath:=opendialog1.FileName;
if datapath<>'' then
begin
if application.MessageBox('次操作使现在数据丢失,是否继续?', '恢复数据',MB_okcancel)=idok then
begin
a1.Active:=true;
zh.back.CommandText:='restore database wei from disk='''+datapath+''''+'with replace';
try
zh.back.execute;
showmessage('恢复成功!');
except
showmessage('恢复失败!') ;
end;
end;
end;
end;procedure TBK.cancelBtnClick(Sender: TObject);
begin
application.Terminate;
end;end.
unit Unit2;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ExtCtrls, ComCtrls, StdCtrls, Buttons;type
TBK = class(TForm)
Panel1: TPanel;
Panel2: TPanel;
Label1: TLabel;
Label2: TLabel;
bckedata: TBitBtn;
RecoverData: TBitBtn;
cancelBtn: TBitBtn;
Edit_path: TEdit;
OpenDialog1: TOpenDialog;
a1: TAnimate;
procedure FormCreate(Sender: TObject);
procedure bckedataClick(Sender: TObject);
procedure RecoverDataClick(Sender: TObject);
procedure cancelBtnClick(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
BK: TBK;implementationuses Dataunit;{$R *.dfm}procedure TBK.FormCreate(Sender: TObject);
var
sourcepath:string;
begin
self.Caption:=application.Title +'- 数据备份'; self.Caption:=application.Title +'- 数据备份' ;
sourcepath:=Extractfiledir(application.exename);
if (strlen(pchar(sourcepath))<>3)then
sourcepath:=sourcepath+'\';
edit_path.Text:=sourcepath +'Backdata\wei'+ formatdatetime('yyyymmdd',date)+'.back';end;procedure TBK.bckedataClick(Sender: TObject);
begin
self.Caption:='正在备份数据';
a1.Active:=true;
zh.back.commandtext:='backup database xq to disk='''+ edit_path.text+'''';
try
zh.back.execute;
showmessage('备份成功');
a1.active:=false;
except
showmessage('备份失败');
a1.active:=false;
end;
self.caption:='数据备份和恢复';
end;procedure TBK.RecoverDataClick(Sender: TObject);
var datapath:string;
begin
self.caption:='正在恢复数据......';
zh.zhconn.Close;
zh.ZHConn.Connected:=false;
opendialog1.Filter:='备份文件(*.back)|*.back|所有文件(*.*)|*.*';
opendialog1.InitialDir:=extractfiledir(application.exename)+'\backupdata';
if opendialog1.execute then
datapath:=opendialog1.FileName;
if datapath<>'' then
begin
if application.MessageBox('次操作使现在数据丢失,是否继续?', '恢复数据',MB_okcancel)=idok then
begin
a1.Active:=true;
zh.back.CommandText:='restore database wei from disk='''+datapath+''''+'with replace';
try
zh.back.execute;
showmessage('恢复成功!');
except
showmessage('恢复失败!') ;
end;
end;
end;
end;procedure TBK.cancelBtnClick(Sender: TObject);
begin
application.Terminate;
end;end.
Create Procedure killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500)
declare @spid int
set @sql='declare getspid cursor for
select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status<>-1
begin
exec('kill '+@spid)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
endGO
在恢复数据库之前先执行此存储过程,像这样:Use Master
Go
exec killspid 'mydb'
Go
restore database mydb from disk='c:\mydb.bak'在Delphi中直接用ExecSQL先后执行以上SQL语句即可