求一段SQL SERVER数据库备份与恢复的代码
解决方案 »
- 怎么用application service 来调用DLL啊???
- 如何检查出字符窜中含有汉字?
- dbgrideh的滚动条问题
- execl文件打印同时或打印后,自动另存为,,,
- 菜鸟问题!!!
- 请大家帮忙,学习C/S结构的方法,最好能给我一个简单的例子。
- 我写了个邮件发送程序,老是错,大家帮看看
- 关开 数据库操作的 错误处理---高手请进!
- 用ADOConnect连接SQLServer数据库时,想用udl文件,可以吗?如何生成udl文件
- 如何将改变标题栏的高度?
- 求关于文件断点传输的例子代码和教程!!!
- 客户机连接sql server 2000,只要装sqlserver的客户端就可以,但....
Restore, Backup, 不就行了?
RESTORE 。。 TO
BACKUP 。。 FROM (路径)
var
tmpSql:Tquery;
begin
if savedia.Execute then
begin
tmpsql:=TQuery.Create(self);
tmpsql.DatabaseName :='linxdata';
tmpsql.SQL.Clear ;
mainform.mainfor.mainStatu.Panels[2].Text :='正在备份数据库,请稍等.....';
tmpsql.SQL.Add('BACKUP DATABASE [linxData] TO DISK = N'''+
savedia.FileName+ ''' WITH NOINIT , NOUNLOAD , NAME = N''linxData backup'', SKIP , STATS = 10, NOFORMAT ');
tmpsql.ExecSQL ;
mainform.mainfor.mainStatu.Panels[2].Text :='';
application.MessageBox('数据备份完成!','提示',mb_ok+mb_iconinformation);
tmpsql.Free ;
end;
ADOConnection.Execute('Restore DataBase 数据库名 From disk='+Quotedstr('D:\dbdak.dat'));
所以必须用以下方法;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
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('不行艾');
Exit;
end;
end;
Application.MessageBox(':)','?',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('ok了','?',MB_OK + MB_ICONINFORMATION);
end;
FADOCommand: TADOCommand;
FOpenQuery: TADOQuery;
FConnection: TADOConnection;
初始化
FConnection := TADOConnection.Create(nil);
FConnection.LoginPrompt := False;
FConnection.CommandTimeout := 3600;
FConnection.ConnectionTimeout := 60; FADOCommand := TADOCommand.Create(nil);
FADOCommand.CommandTimeout := 3600;
FADOCommand.CommandType := cmdText;
FADOCommand.Connection := FConnection; FOpenQuery := TADOQuery.Create(nil);
FOpenQuery.Connection := FConnection;
备份
const
BackupStr = 'use master backup database [%s] to DISK=''%s'' with init';
begin
Result := GetConnected;
if not Result then Exit;
with FADOCommand do
begin
try
FADOCommand.CommandText := Format(BackupStr, [DBName(数据库名称), FileName(备份文件名称)]);
FADOCommand.Execute;
Result := True;
except
on E: Exception do
begin
FLastError := '数据库备份错误:' + E.Message;
Result := False;
end;
end;
end;恢复
var
LogicDBName, LogicLogName: string;//, FSQL
const
RestoreStr = 'Use master RESTORE database [%0:s] FROM DISK = ''%1:s'' '
+ ' with Replace, Move ''%2:s'' to ''%3:s%0:s_data.mdf'',move ''%4:s'' to '
+ ' ''%3:s%0:s_log.ldf''';
begin
//判断连接数
Result := CheckOtherConnected and GetLogicInfo(LogicDBName, LogicLogName);
if not Result then
begin
Result := False;
Exit;
end;
with FADOCommand do
begin
FADOCommand.CommandText := Format(RestoreStr, [DBName, FFileName,
LogicDBName, GetDBDataFilePath, LogicLogName]);
try
FADOCommand.Execute;
Result := True;
except
on E: Exception do
begin
FLastError := '数据恢复错误:' + E.Message;
Result := False;
end;
end;
end;
附:
function GetLogicInfo(var LogicDBName,
LogicLogName: string): Boolean;
begin
with FOpenQuery do
begin
try
Close;
SQL.Clear;
SQL.Add(Format('use master RESTORE FILELISTONLY FROM DISK =''%s''', [FFileName]));
Open;
First;
while not Eof do
begin
if FieldByName('Type').AsString = 'D' then
LogicDBName := FieldByName('LogicalName').AsString;
if FieldByName('Type').AsString = 'L' then
LogicLogName := FieldByName('LogicalName').AsString;
Next;
end;
Result := True;
except
on E: EOleException do
begin
FLastError := Format('数据恢复错误:找不到备份文件"%s"。'
+ #10#13'请确认文件是否存在。', [FFileName]);
Result := False;
end;
end;
end;
end;function GetDBDataFilePath: string;
begin
with FOpenQuery do
begin
try
Close;
SQL.Clear;
SQL.Text := 'select filename from master..sysdatabases where name = ''' + FConnectInfo.DB + '''';
Open;
Result := Fields[0].AsString;
if Result <> EmptyStr then
begin
if Length(ExtractFileDir(Result)) <> 3 then
Result := ExtractFileDir(Result) + '\'
else
Result := ExtractFileDir(Result)
end
except
on E: Exception do
FLastError := '得到系统路径错误:' + E.Message;
end;
end;
end;
我学的时间不长,正要捣鼓备份和恢复。请问哪位朋友用的不错,本机恢复备份,符合规范,sql server数据库?上面的代码哪个方便易懂一些?
36不错
procedure TForm1.suiButton12Click(Sender: TObject);
var adobackup:TAdocommand;
begin
if suiEdit5.Text='' then
begin
Application.MessageBox('请选择备份文件的存放路径!','提示',64);
exit;
end;
adobackup:=TAdocommand.Create(application);
adobackup.ConnectionString:=' Provider=SQLOLEDB.1;'+'Persist Security Info=False;User ID='+GetRegInfo(3)+';Password='+GetRegInfo(4)+';'+
'Initial Catalog='+GetRegInfo(2)+';Data Source='+GetRegInfo(1); adobackup.CommandText:='backup database MMDATA to disk='''+suiEdit5.text+'''';
try
adobackup.Execute;
Application.MessageBox('备份成功!','提示',64);
adobackup.Free;
except
Application.MessageBox('备份失败!','提示',64);
adobackup.Free;
exit;
end;end;恢复:
procedure TForm1.suiButton17Click(Sender: TObject);
var adorestore:TAdocommand;
begin
if suiEdit6.Text='' then
begin
Application.MessageBox('请选择恢复文件!','提示',64);
exit;
end;
if application.MessageBox('此操作将使上次备份以来的所有数据丢失,是否继续?','恢复数据',33)=IDCANCEL then exit;
dm.Free;
adorestore:=TAdocommand.Create(application);
adorestore.ConnectionString:=' Provider=SQLOLEDB.1;'+'Persist Security Info=False;User ID='+GetRegInfo(3)+';Password='+GetRegInfo(4)+';'+
'Data Source='+GetRegInfo(1);
adorestore.CommandText:='restore database MMDATA from disk='''+suiEdit6.Text+''' with replace';
try
adorestore.Execute;
adorestore.Free;
if Application.MessageBox( '恢复成功! 现在需要重新登陆软件,请按“确定” ','提示',64)=IDOK then Close;
except
Application.MessageBox('恢复失败!请确认恢复文件的路径和名称是否正确,或是否已关闭其它正在使用此数据库的程序!','提示',64);
adorestore.Free;
exit;
end;//end tryend;记得恢复只是连接到服务器就可以了,还要记得释放数据模块短开连接
问题解决了,给分吧