可用如下方式关闭use master go create proc killspid (@dbname varchar(20)) as begin declare @sql nvarchar(500),@temp varchar(1000) 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 =0 begin set @temp='kill '+rtrim(@spid) exec(@temp) fetch next from getspid into @spid end close getspid deallocate getspid end --用法 use master exec killspid '数据库名'
if EdtDIR.Text='' then
begin
Application.MessageBox('请指定恢复文件的路径!','提 示',MB_OK+MB_ICONASTERISK);
exit;
end;
if extractfileext(EdtDIR.Text)<>'.bak' then
begin
Application.MessageBox('文件不正确!','提 示',MB_OK+MB_ICONWARNING);
exit;
end;
{
conStr := 'Provider=SQLOLEDB.1;Password=' +pubRecord.PcurServerPWord;
conStr := conStr + ';Persist Security Info=True;' + 'User ID=' +pubRecord.PcurServerUID ;
conStr := conStr + ';Initial Catalog='+'master' ;
conStr := conStr + ';Data Source=' + pubRecord.PcurServer;
DM.ADOCon.ConnectionString:=conStr; //'';
}
panel1.visible:=true;
panel1.Update ; Query_Res:=TADOQuery.Create(Query_Res);
Query_Res.Connection:=DM.ADOCon; strsql := 'RESTORE DATABASE '+ pubRecord.PdefaultDataBase+' from disk='; //pubRecord.PdefaultDataBase 全局变量数据库名
strsql := 'USE master EXEC sp_dboption '+pubRecord.PdefaultDataBase+',''dbo use only'', ''TRUE'' ' +strsql+''''+trim(edtDIR.Text)+'''';
strsql :=strsql+'use '+pubRecord.PdefaultDataBase;
try
Query_Res.Close;
Query_Res.SQL.Clear;
Query_Res.SQL.Add(strsql);
Query_Res.ExecSQL;
panel1.visible:=false;
if Application.MessageBox('数据恢复成功!','提 示',MB_OK+MB_ICONASTERISK)=IDOK then
close;
SBOK.enabled:=true;
{//close;
conStr := 'Provider=SQLOLEDB.1;Password=' +pubRecord.PcurServerPWord;
conStr := conStr + ';Persist Security Info=True;' + 'User ID=' +pubRecord.PcurServerUID ;
conStr := conStr + ';Initial Catalog='+ pubRecord.PdefaultDataBase;
conStr := conStr + ';Data Source=' + pubRecord.PcurServer;
DM.ADOCon.ConnectionString:=conStr;
DM.ADOCon.Connected:=true;}
except
begin
Application.MessageBox('数据恢复过程中发生错误!','警 告',MB_OK+MB_ICONWARNING);
close;
{conStr := 'Provider=SQLOLEDB.1;Password=' +pubRecord.PcurServerPWord;
conStr := conStr + ';Persist Security Info=True;' + 'User ID=' +pubRecord.PcurServerUID ;
conStr := conStr + ';Initial Catalog='+ pubRecord.PdefaultDataBase;
conStr := conStr + ';Data Source=' + pubRecord.PcurServer;
DM.ADOCon.ConnectionString:=conStr;
DM.ADOCon.Connected:=true; }
end;
end;
finally
Query_Res.free;
end;
end;
我的系统不是单用户的,所以我不知道有没有人正在用数据库,也就是别在用数据库时我是不能进行数据库恢复操作的.
go
create proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500),@temp varchar(1000)
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 =0
begin
set @temp='kill '+rtrim(@spid)
exec(@temp)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end --用法
use master
exec killspid '数据库名'
2:执行上面的存储过程即可
3:注意不能在待恢复的数据库下操作,推荐在master数据库下操作。
mySQLserver:Variant;
begin
//启动
try
mySQLserver := CreateOleObject('SQLDMO.sqlserver');
mysqlserver.name:=//远程计算机名
mysqlserver.login:=//登录用户名
mysqlserver.password:=//等路口令
mySQLserver.Start(true);
finally
mySQLserver:=null;
end;
end;
//停止
try
mySQLserver := CreateOleObject('SQLDMO.sqlserver');
mysqlserver.name:=//远程计算机名
mysqlserver.login:=//登录用户名
mysqlserver.password:=//等路口令
mySQLserver.stop;
finally
mySQLserver:=null;
end;