(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;
Backup DataBase sfa to disk ='+opendialog1.FileName+' Restore DataBase sfa from disk ='+opendialog1.FileName+'
楼上的,你能给个例子吗?还有就是怎么得到在sql server身份验证是的系统用户名和系统口令呢
try DM.ADOConnection1.Connected:=False;//关闭sbhy数据库 DM.adocommand1.Connection:=DM.ADOConnection2;//转移连接到数据库master DM.ADOCommand1.CommandText:='ALTER DATABASE sbhy SET OFFLINE WITH ROLLBACK IMMEDIATE';//切断sbhy连接 DM.ADOCommand1.Execute; DM.ADOCommand1.CommandText:='RESTORE DATABASE sbhy FROM DISK = ''' +opendialog1.FileName +'''';//恢复sbhy数据库 DM.ADOCommand1.Execute; showmessage('完成'); finally DM.ADOCommand1.CommandText:='ALTER DATABASE sbhy SET ONLINE WITH ROLLBACK IMMEDIATE';//重新连接sbhy数据库 DM.ADOCommand1.Execute; DM.ADOConnection2.Connected:=False;//关闭master数据库 end; DM.ADOConnection1.Connected:=True;//打开sbhy数据库 DM.adocommand1.Connection:=DM.ADOConnection1;//恢复连接到数据库sbhy //============================================================== 关键在于还原数据库之前 'ALTER DATABASE sbhy SET OFFLINE WITH ROLLBACK IMMEDIATE' 如果是sql7,使用如下: EXEC sp_dboption 'databasename', 'offline', 'TRUE' RESTORE DATABASE databasename FROM DISK = 备份文件名 with REPLACE EXEC sp_dboption 'databasename', 'offline', 'false' 另大家用single user开关也可以 EXEC sp_dboption 'dababasename','single user', 'TRUE' RESTORE DATABASE databasename FROM DISK = 备份文件名 with REPLACE EXEC sp_dboption 'dababasename','single user', 'false' Restore database ... 这条语句在查询分析器中,对数据库自身进行还原也会出现"未取得对数据库的排它访问权限", 我没有取得这个权限,用了另外一个方法解决了这个问题, 1 断开数据库的连接 2 动态的创建一个数据库别名,连接master数据库,在master数据库中执行Restore database ... 3 删除新建的数据库别名
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;
Restore DataBase sfa from disk ='+opendialog1.FileName+'
DM.ADOConnection1.Connected:=False;//关闭sbhy数据库
DM.adocommand1.Connection:=DM.ADOConnection2;//转移连接到数据库master
DM.ADOCommand1.CommandText:='ALTER DATABASE sbhy SET OFFLINE WITH ROLLBACK IMMEDIATE';//切断sbhy连接
DM.ADOCommand1.Execute; DM.ADOCommand1.CommandText:='RESTORE DATABASE sbhy FROM DISK = ''' +opendialog1.FileName +'''';//恢复sbhy数据库
DM.ADOCommand1.Execute;
showmessage('完成');
finally
DM.ADOCommand1.CommandText:='ALTER DATABASE sbhy SET ONLINE WITH ROLLBACK IMMEDIATE';//重新连接sbhy数据库
DM.ADOCommand1.Execute;
DM.ADOConnection2.Connected:=False;//关闭master数据库
end;
DM.ADOConnection1.Connected:=True;//打开sbhy数据库
DM.adocommand1.Connection:=DM.ADOConnection1;//恢复连接到数据库sbhy
//==============================================================
关键在于还原数据库之前
'ALTER DATABASE sbhy SET OFFLINE WITH ROLLBACK IMMEDIATE'
如果是sql7,使用如下:
EXEC sp_dboption 'databasename', 'offline', 'TRUE'
RESTORE DATABASE databasename FROM DISK = 备份文件名 with REPLACE
EXEC sp_dboption 'databasename', 'offline', 'false'
另大家用single user开关也可以
EXEC sp_dboption 'dababasename','single user', 'TRUE'
RESTORE DATABASE databasename FROM DISK = 备份文件名 with REPLACE
EXEC sp_dboption 'dababasename','single user', 'false'
Restore database ... 这条语句在查询分析器中,对数据库自身进行还原也会出现"未取得对数据库的排它访问权限",
我没有取得这个权限,用了另外一个方法解决了这个问题,
1 断开数据库的连接
2 动态的创建一个数据库别名,连接master数据库,在master数据库中执行Restore database ...
3 删除新建的数据库别名