sql serve的备分与还原
用sql语句backup 'RESTORE 等,也可以用sql server中的存储过程
下面的例子是我贴来的,不知道能不能用,不过大概可以起个引导作用。
procedure TBak.btnRestoreClick(Sender: TObject);
var
Qry:TADOQuery;
begin
if FileExists(self.edtReStorePath.Text) then
begin
DM.Conn.Connected:=False; //断开系统数据库连接
Qry:=TADOQuery.Create(self);
Qry.CommandTimeout:=120;
//把连接转移到master库
Qry.ConnectionString:='Provider=SQLOLEDB.1;Password='+Login.GetDBServerInfo('Pwd')+';Persist Security Info=True;User
ID='+Login.GetDBServerInfo('User')+';Initial Catalog=master;Data Source='+Login.GetDBServerInfo('Server');
//-------------------端开rxd_CRM库------------------------------------------
Qry.Close;
Qry.SQL.Clear;
Qry.SQL.Text:='ALTER DATABASE rxd_CRM SET OFFLINE WITH ROLLBACK IMMEDIATE';
Qry.Prepared;
Qry.ExecSQL;
//------------------- 端开rxd_CRM库结束-------------------------------------
Qry.Close;
Qry.SQL.Clear;
Qry.SQL.Text:='RESTORE DATABASE rxd_CRM FROM DISK='''+self.edtReStorePath.Text+''' with replace';
Qry.Prepared;
try
Qry.ExecSQL;
Login.MsgInfo('成功恢复数据!');
finally
//-------------------重新连接rxd_CRM库------------------------------------
Qry.Close;
Qry.SQL.Clear;
Qry.SQL.Text:='ALTER DATABASE rxd_CRM SET ONLINE WITH ROLLBACK IMMEDIATE';
Qry.Prepared;
Qry.ExecSQL;
//-------------------重新连接rxd_CRM库结束--------------------------------
Qry.Free;
DM.Conn.ConnectionString:=Login.GetDBServerInfo('DBStr');
try
DM.Conn.Connected:=True;
except
Login.MsgError('连接数据库失败,请尝试重启系统!');
end;
end
end
else
begin
Login.MsgInfo('数据文件不存在,请重新选择!');
self.edtReStorePath.SetFocus;
end;
end;
至于异地,根据ip地址连接sql server就可以了。
下面这段代码应该可以用
备份:
procedure TBackupFrm.BitBtn2Click(Sender: TObject);
begin
if Edit1.Text= '' then
begin
Showmessage('无选择要保存的文件名');
exit;
end;
try
try
dmData.adoQryTmp.Active:= false;
dmData.adoQryTmp.SQL.Clear;
dmData.adoQryTmp.SQL.Add('BACKUP DATABASE [dzyl] TO DISK = '''+edit1.text+''' WITH INIT');
dmData.adoQryTmp.ExecSQL;
finally
begin
dmData.adoQryTmp.Active:= false;
Showmessage('数据库备份成功!');
end;
end;
except
on e:exception do
begin
ShowMessage('数据库备份失败!');
end;
end;
end;
---------------
恢复
procedure TBackupFrm.BitBtn4Click(Sender: TObject);
begin
if Edit2.Text = '' then
begin
showmessage('未选择要恢复的数据库文件!');
exit;
end;
with dmData do
begin
try
adocmmd.CommandText:='use master';
adocmmd.Execute;
adocmmd.CommandText:=' alter database dzyl set offline with rollback immediate';
adocmmd.Execute;
adocmmd.CommandText:='restore database dzyl from disk= '''+edit2.Text+''' with recovery ';
adocmmd.Execute;
adocmmd.CommandText:=' alter database dzyl set online with rollback immediate';
adocmmd.Execute;
showmessage('数据库恢复成功!');
application.Terminate;
except
on e:exception do
begin
showmessage('数据库恢复失败!'+e.Message);
end;
end;
end;
end;