恢复代码是:
ADOCommand1.CommandText:='use master';
ADOCommand1.Execute;
ADOCommand1.CommandText:='RESTORE DATABASE test from disk=''d:\text.bak''';
ADOCommand1.Execute;
ADOCommand1.CommandText:='use test';
ADOCommand1.Execute;
showmessage('ok');错误:“因为数据库正在使用,所以未能获得对数据库的排它访问权”必须把每个窗体的ADOConnection的连接给断了才能正常恢复,请问有什么好的方案没?
ADOCommand1.CommandText:='use master';
ADOCommand1.Execute;
ADOCommand1.CommandText:='RESTORE DATABASE test from disk=''d:\text.bak''';
ADOCommand1.Execute;
ADOCommand1.CommandText:='use test';
ADOCommand1.Execute;
showmessage('ok');错误:“因为数据库正在使用,所以未能获得对数据库的排它访问权”必须把每个窗体的ADOConnection的连接给断了才能正常恢复,请问有什么好的方案没?
TADOConnection连接到Master数据库上
1. ADOConnection1 连接你的业务数据库。
2. ADOConnection2 连接 MASTER 数据库。
3. ADOCommand1.Connection := ADOConnection2;
假设你的数据库名为: DataBaseName
Button.OnClick:
begin
ADOConnection1.Connected := False;
ADOCommand1.CommandText := 'BACKUP DATABASE DataBaseName TO DISK = ' + #39 + 'C:\KKK.BAK' + #39;
ADOCommand1.Execute;
ADOCommand1.CommandText := 'ALTER DATABASE DataBaseName SET OFFLINE WITH ROLLBACK IMMEDIATE';
ADOCommand1.Execute;
ADOCommand1.CommandText := 'RESTORE DATABASE DataBaseName FROM DISK = ' + #39 + 'C:\KKK.BAK' + #39;
ADOCommand1.Execute;
ADOCommand1.CommandText := 'ALTER DATABASE DataBaseName SET ONLINE WITH ROLLBACK IMMEDIATE';
ADOCommand1.Execute;
ADOConnection1.Connected := True;
ADOTable1.Active := true;
end;
procedure TForm23.Button2Click(Sender: TObject);
begin
adoquery1.SQL.Clear;
adoquery1.SQL.Add('use Master');
adoquery1.ExecSQL;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('execute sp_helpdevice');
adoquery1.ExecSQL;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('Restore database test From disk=''d:\text.bak'' with replace');
adoquery1.ExecSQL;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('Use test');
adoquery1.ExecSQL;
application.MessageBox('数据库完成恢复','警告',MB_OK);
end;
在查询分析器中运行
use master
goif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_killspid]
GOcreate 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
go--用法
exec p_killspid test--恢复数据库.
RESTORE DATABASE test FROM disk='D:\text.bak'不过这种方法在恢复数据库之后,只有重新启动程序才能使用程序的其它功能。
begin
adoquery1.SQL.Clear;
adoquery1.SQL.Add('use Master');
adoquery1.ExecSQL;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('execute sp_helpdevice');
adoquery1.ExecSQL;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('Restore database test From disk=''d:\text.bak'' with replace');
adoquery1.ExecSQL;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('Use test');
adoquery1.ExecSQL;
application.MessageBox('数据库完成恢复','警告',MB_OK);
end;
这个应当行的。