怎样在Delphi中备份并恢复SQL Server数据库? 本人在Delphi程序中备份了SQL Server数据库但同时在程序中怎么也不能恢复命令,请高手指点。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 备份:var Frm_BeiFen: TFrm_BeiFen; //Filename:string;implementationuses dmunit;{$R *.dfm}procedure TFrm_BeiFen.FormCreate(Sender: TObject);beginedit2.Text:=extractfilepath(application.ExeName)+'backup\student_data';end;procedure TFrm_BeiFen.BitBtn2Click(Sender: TObject);beginclose;end;procedure TFrm_BeiFen.SpeedButton1Click(Sender: TObject);beginsavedialog1.InitialDir:=extractfilepath(application.ExeName)+'backup\';savedialog1.Title:='保存文件';savedialog1.FileName:='student_data';if savedialog1.Execute thenedit2.Text:=savedialog1.FileName;end;procedure TFrm_BeiFen.BitBtn1Click(Sender: TObject);beginscreen.Cursor:=crhourglass;label3.Visible:=true;label3.Update;//path_filename:=trim(edit2.Text);with adocommand1 dobegin CommandText := ''; CommandText := 'backup database student to DISK = :path with init'; Parameters.ParamByName('path').value := trim(edit2.text); Execute;end;label3.Visible:=false;label3.Update;showmessage('备份完毕!');screen.Cursor:=crdefault;end;end. 恢复:unit restoreandconnect;interfaceuses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, ComCtrls, StdCtrls, Buttons, ExtCtrls, IdBaseComponent, IdComponent, IdUDPBase, IdUDPClient, winsock,FInIfile,DB,idstack, ADODB,Registry;type TFrm_RestoreAndConnect = class(TForm) PageControl1: TPageControl; TabSheet1: TTabSheet; TabSheet2: TTabSheet; Label1: TLabel; Label2: TLabel; ComboBox1: TComboBox; Edit1: TEdit; BitBtn1: TBitBtn; Label3: TLabel; BitBtn2: TBitBtn; RadioGroup1: TRadioGroup; Label4: TLabel; Edit2: TEdit; BitBtn3: TBitBtn; ADOConnection1: TADOConnection; ADOC_Create: TADOCommand; ADOQ_pro: TADOQuery; ADOQ_check: TADOQuery; OpenDialog1: TOpenDialog; IdUDPClient1: TIdUDPClient; ini: TFinifile; procedure FormCreate(Sender: TObject); procedure BitBtn2Click(Sender: TObject); procedure BitBtn1Click(Sender: TObject); procedure RadioGroup1Click(Sender: TObject); procedure BitBtn3Click(Sender: TObject); private procedure createdatabase; { Private declarations } public sys_dir:string; str_filename:string; { Public declarations } end;var Frm_RestoreAndConnect: TFrm_RestoreAndConnect;implementation{$R *.dfm}procedure Tfrm_restoreandconnect.createdatabase;var myreg:Tregistry; sql_path:string; s_sql:string;begin screen.Cursor:=crhourglass; label3.Visible:=true; label3.Update; myreg:=Tregistry.Create; myreg.RootKey:=HKEY_LOCAL_MACHINE; try if myreg.OpenKey('\software\microsoft\mssqlserver\setup',false) then begin sql_path:=myreg.ReadString('sqlpath'); sql_path:=sql_path+'\data'; end; finally myreg.Free; end; s_sql := ''; s_sql := s_sql + 'create database '+trim(combobox1.Text); s_sql := s_sql + ' on (name = student_data, filename = '''+SQL_path+'\student.mdf'+''') '; s_sql := s_sql + 'log on (name = student_log, filename = '''+SQL_path+'\student_log.ldf'+''')'; with adoc_create do begin //adoc_create.ConnectionString:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master'; commandtext:=''; commandtext:=s_sql; execute; end; with adoc_create do begin //adoc_create.ConnectionString:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master'; commandtext := ''; commandtext := 'Restore database :S_Name from disk=:path with replace'; parameters.ParamByName('s_Name').value := trim(combobox1.Text); parameters.ParamByName('path').value := trim(edit1.Text); Execute; end; label3.Visible:=false; label3.Update; screen.Cursor:=crdefault;end;procedure TFrm_RestoreAndConnect.FormCreate(Sender: TObject);var myreg:TRegistry; s:array[0..max_path]of char;begin adoc_create.ConnectionString:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master'; myreg:=Tregistry.Create; myreg.RootKey:=HKEY_LOCAL_MACHINE; Try if myreg.OpenKey('\Software\microsoft\microsoft sql server',false) then TabSheet1.Visible:=true else Tabsheet1.Visible:=false; Finally myreg.Free; end; GetSystemDirectory(s,sizeof(s)); sys_dir:=s;end;procedure TFrm_RestoreAndConnect.BitBtn2Click(Sender: TObject);begin if combobox1.Text='' then begin showmessage('请选择数据库名称!'); combobox1.SetFocus; exit; end; if edit1.Text='' then begin showmessage('请输入数据库备份文件名!'); edit1.SetFocus; exit; end; if not fileexists(trim(edit1.Text)) then begin showmessage('没有找到数据库文件,请核实!'); edit1.SetFocus; exit; end else begin if application.MessageBox('确实要恢复数据库吗?','提示',mb_okcancel+mb_iconinformation)<>IDcancel then begin try adoq_check.ConnectionString:='Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master'; with adoq_check do begin close; sql.Clear; sql.Add('select*from sysdatabases where name=:sname'); parameters.ParamByName('sname').Value:=trim(combobox1.Text); open; end; except showmessage('连接数据库失败,'+chr(13)+chr(13)+'请核实是否已正确安装MS SQL Server2000!'); exit; end; if adoq_check.IsEmpty then createdatabase else begin if application.MessageBox('该操作将删除现有数据库,确认恢复吗?','恢复警告',mb_yesno+mb_iconwarning)=idyes then begin with adoc_create do begin commandtext:=''; commandtext:='drop database student'; execute; createdatabase; end; showmessage('数据库已被恢复,请核实!'); end; exit; end; showmessage('数据库恢复完成!'); edit1.Clear; end; end;end;procedure TFrm_RestoreAndConnect.BitBtn1Click(Sender: TObject);var path:string;beginsetLength(path, 144); if getsystemDirectory(pchar(path),144)<>0 then begin setlength(path, strlen(pchar(path))); path := path; end; OpenDialog1.Title :='读取文件'; OpenDialog1.InitialDir:=path; if OpenDialog1.Execute then str_filename:=OpenDialog1.FileName; edit1.Text := str_filename;end;procedure TFrm_RestoreAndConnect.RadioGroup1Click(Sender: TObject);var myreg:Tregistry;begin if radiogroup1.ItemIndex=0 then begin label4.Caption:='服务器名:'; edit2.Enabled:=true; edit2.Text:=''; edit2.SetFocus; end else begin label4.Caption:='计算机名:'; edit2.Enabled:=false; myreg:=Tregistry.Create; myreg.RootKey:=HKEY_LOCAL_MACHINE; try if myreg.OpenKey('\system\currentcontrolset\control\computername\computername',false) then edit2.Text:=myreg.ReadString('computername'); finally myreg.Free; end; end;end;procedure TFrm_RestoreAndConnect.BitBtn3Click(Sender: TObject);var //ini:TFinifile; wversionrequested:word; wsadata:TWSAdata; p:Phostent; p2:pchar; servername:string;begin if Trim(edit2.Text) = '' then begin if radiogroup1.ItemIndex=0 then ShowMessage('服务器名不能为空!') else ShowMessage('计算机名不能为空!'); edit2.SetFocus; Exit; end; Screen.Cursor := crHourGlass; if radiogroup1.ItemIndex=0 then //网络 begin {连接 WinSock} wVersionRequested := MAKEWORD(1, 1); WSAStartup(wVersionRequested, wsaData); {获得计算机名} p := GetHostByName(PChar(Trim(edit2.Text))); if p = nil then begin Screen.Cursor := crDefault; ShowMessage('服务器连接失败!可能是所输入的服务器名有误,请重新输入。'); {断开 WinSock} WSACleanup; Exit; end; {获得本机的ip地址} p2 := iNet_ntoa(PInAddr(p^.h_addr_list^)^); try servername:=p2; adoconnection1.ConnectionString:='Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=student;Data Source='+servername; adoconnection1.Connected:=true; // adoq_pro.Active:=true; except showmessage('服务器连接失败!请检查数据库连接!'); exit; end; INI.Directory:=ExtractFilePath(Application.ExeName); InI.Writestrings('数据库连接', 'Provider', 'SQLOLEDB.1'); ini.writestrings('数据库连接', 'Integrated Security','SSPI'); InI.Writestrings('数据库连接', 'Persist Security Info', 'False'); InI.Writestrings('数据库连接', 'User ID', 'sa'); InI.Writestrings('数据库连接', 'Initial Catalog', 'student'); InI.Writestrings('数据库连接', 'Data Source', p2); //InI.Writestrings('数据库连接', 'Locale Identifier', '2052'); //InI.Writestrings('数据库连接', 'Connect TimeOut', '15'); InI.Writestrings('数据库连接', 'Use Procedure for Prepare', '1'); InI.Writestrings('数据库连接', 'Auto Translate', 'True'); InI.Writestrings('数据库连接', 'Packet Size', '4096'); InI.Writestrings('数据库连接', 'Workstation ID', idstack.Gstack.localaddress); {断开 WinSock} WSACleanup; end else begin //单机系统 INI.Directory:=ExtractFilePath(Application.ExeName); InI.Writestrings('数据库连接', 'Provider', 'SQLOLEDB.1'); ini.writestrings('数据库连接', 'Integrated Security','SSPI'); InI.Writestrings('数据库连接', 'Persist Security Info', 'False'); InI.Writestrings('数据库连接', 'User ID', 'sa'); InI.Writestrings('数据库连接', 'Initial Catalog', 'student'); InI.Writestrings('数据库连接', 'Data Source', ''); //InI.Writestrings('数据库连接', 'Locale Identifier', '2052'); //InI.Writestrings('数据库连接', 'Connect TimeOut', '15'); InI.Writestrings('数据库连接', 'Use Procedure for Prepare', '1'); InI.Writestrings('数据库连接', 'Auto Translate', 'True'); InI.Writestrings('数据库连接', 'Packet Size', '4096'); InI.Writestrings('数据库连接', 'Workstation ID', trim(edit2.Text)); end; Screen.Cursor := crDefault; ShowMessage('数据库连接成功!'); // close; end;end. 请教一个关于showmessage()的用法 因小三阳连续失去3个工作机会了 如何得到所有活动的sqlserver服务器名 关于dbgrideh的问题 我做的安装文件怎么运行时会出现The Instance name specified is invalid. 一个关于数据库更新的问题,请大家帮帮忙吧! sleep(1)和sleep(10)有区别嘛?? 小问题,GZ,UP都有分 这样的情况下是不是能使内存耗尽? 为何DBgrid只可显示小数点后的四位,不知如何显示更多位? 急!!打印图形问题 在线等,100分,立结TListBox滚动问题!
Frm_BeiFen: TFrm_BeiFen;
//Filename:string;
implementation
uses dmunit;
{$R *.dfm}procedure TFrm_BeiFen.FormCreate(Sender: TObject);
begin
edit2.Text:=extractfilepath(application.ExeName)+'backup\student_data';
end;procedure TFrm_BeiFen.BitBtn2Click(Sender: TObject);
begin
close;
end;procedure TFrm_BeiFen.SpeedButton1Click(Sender: TObject);
begin
savedialog1.InitialDir:=extractfilepath(application.ExeName)+'backup\';
savedialog1.Title:='保存文件';
savedialog1.FileName:='student_data';
if savedialog1.Execute then
edit2.Text:=savedialog1.FileName;
end;procedure TFrm_BeiFen.BitBtn1Click(Sender: TObject);
begin
screen.Cursor:=crhourglass;
label3.Visible:=true;
label3.Update;
//path_filename:=trim(edit2.Text);
with adocommand1 do
begin
CommandText := '';
CommandText := 'backup database student to DISK = :path with init';
Parameters.ParamByName('path').value := trim(edit2.text);
Execute;
end;
label3.Visible:=false;
label3.Update;
showmessage('备份完毕!');
screen.Cursor:=crdefault;
end;end.
unit restoreandconnect;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ComCtrls, StdCtrls, Buttons, ExtCtrls, IdBaseComponent,
IdComponent, IdUDPBase, IdUDPClient, winsock,FInIfile,DB,idstack, ADODB,Registry;type
TFrm_RestoreAndConnect = class(TForm)
PageControl1: TPageControl;
TabSheet1: TTabSheet;
TabSheet2: TTabSheet;
Label1: TLabel;
Label2: TLabel;
ComboBox1: TComboBox;
Edit1: TEdit;
BitBtn1: TBitBtn;
Label3: TLabel;
BitBtn2: TBitBtn;
RadioGroup1: TRadioGroup;
Label4: TLabel;
Edit2: TEdit;
BitBtn3: TBitBtn;
ADOConnection1: TADOConnection;
ADOC_Create: TADOCommand;
ADOQ_pro: TADOQuery;
ADOQ_check: TADOQuery;
OpenDialog1: TOpenDialog;
IdUDPClient1: TIdUDPClient;
ini: TFinifile;
procedure FormCreate(Sender: TObject);
procedure BitBtn2Click(Sender: TObject);
procedure BitBtn1Click(Sender: TObject);
procedure RadioGroup1Click(Sender: TObject);
procedure BitBtn3Click(Sender: TObject);
private
procedure createdatabase;
{ Private declarations }
public
sys_dir:string;
str_filename:string;
{ Public declarations }
end;var
Frm_RestoreAndConnect: TFrm_RestoreAndConnect;implementation{$R *.dfm}
procedure Tfrm_restoreandconnect.createdatabase;
var
myreg:Tregistry;
sql_path:string;
s_sql:string;
begin
screen.Cursor:=crhourglass;
label3.Visible:=true;
label3.Update;
myreg:=Tregistry.Create;
myreg.RootKey:=HKEY_LOCAL_MACHINE;
try
if myreg.OpenKey('\software\microsoft\mssqlserver\setup',false) then
begin
sql_path:=myreg.ReadString('sqlpath');
sql_path:=sql_path+'\data';
end;
finally
myreg.Free;
end;
s_sql := '';
s_sql := s_sql + 'create database '+trim(combobox1.Text);
s_sql := s_sql + ' on (name = student_data, filename = '''+SQL_path+'\student.mdf'+''') ';
s_sql := s_sql + 'log on (name = student_log, filename = '''+SQL_path+'\student_log.ldf'+''')';
with adoc_create do
begin
//adoc_create.ConnectionString:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master';
commandtext:='';
commandtext:=s_sql;
execute;
end;
with adoc_create do
begin
//adoc_create.ConnectionString:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master';
commandtext := '';
commandtext := 'Restore database :S_Name from disk=:path with replace';
parameters.ParamByName('s_Name').value := trim(combobox1.Text);
parameters.ParamByName('path').value := trim(edit1.Text);
Execute;
end;
label3.Visible:=false;
label3.Update;
screen.Cursor:=crdefault;
end;
procedure TFrm_RestoreAndConnect.FormCreate(Sender: TObject);
var
myreg:TRegistry;
s:array[0..max_path]of char;
begin
adoc_create.ConnectionString:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master';
myreg:=Tregistry.Create;
myreg.RootKey:=HKEY_LOCAL_MACHINE;
Try
if myreg.OpenKey('\Software\microsoft\microsoft sql server',false) then
TabSheet1.Visible:=true
else
Tabsheet1.Visible:=false;
Finally
myreg.Free;
end;
GetSystemDirectory(s,sizeof(s));
sys_dir:=s;
end;procedure TFrm_RestoreAndConnect.BitBtn2Click(Sender: TObject);
begin
if combobox1.Text='' then
begin
showmessage('请选择数据库名称!');
combobox1.SetFocus;
exit;
end;
if edit1.Text='' then
begin
showmessage('请输入数据库备份文件名!');
edit1.SetFocus;
exit;
end;
if not fileexists(trim(edit1.Text)) then
begin
showmessage('没有找到数据库文件,请核实!');
edit1.SetFocus;
exit;
end else
begin
if application.MessageBox('确实要恢复数据库吗?','提示',mb_okcancel+mb_iconinformation)<>IDcancel then
begin
try
adoq_check.ConnectionString:='Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master';
with adoq_check do
begin
close;
sql.Clear;
sql.Add('select*from sysdatabases where name=:sname');
parameters.ParamByName('sname').Value:=trim(combobox1.Text);
open;
end;
except
showmessage('连接数据库失败,'+chr(13)+chr(13)+'请核实是否已正确安装MS SQL Server2000!');
exit;
end;
if adoq_check.IsEmpty then createdatabase
else
begin
if application.MessageBox('该操作将删除现有数据库,确认恢复吗?','恢复警告',mb_yesno+mb_iconwarning)=idyes then
with adoc_create do
begin
commandtext:='';
commandtext:='drop database student';
execute;
createdatabase;
end;
showmessage('数据库已被恢复,请核实!');
end;
exit;
end;
showmessage('数据库恢复完成!');
edit1.Clear;
end;
end;
end;procedure TFrm_RestoreAndConnect.BitBtn1Click(Sender: TObject);
var
path:string;
begin
setLength(path, 144);
if getsystemDirectory(pchar(path),144)<>0 then
begin
setlength(path, strlen(pchar(path)));
path := path;
end; OpenDialog1.Title :='读取文件';
OpenDialog1.InitialDir:=path; if OpenDialog1.Execute then str_filename:=OpenDialog1.FileName;
edit1.Text := str_filename;
end;procedure TFrm_RestoreAndConnect.RadioGroup1Click(Sender: TObject);
var
myreg:Tregistry;
begin
if radiogroup1.ItemIndex=0 then
begin
label4.Caption:='服务器名:';
edit2.Enabled:=true;
edit2.Text:='';
edit2.SetFocus;
end else
begin
label4.Caption:='计算机名:';
edit2.Enabled:=false;
myreg:=Tregistry.Create;
myreg.RootKey:=HKEY_LOCAL_MACHINE;
try
if myreg.OpenKey('\system\currentcontrolset\control\computername\computername',false) then
edit2.Text:=myreg.ReadString('computername');
finally
myreg.Free;
end;
end;
end;procedure TFrm_RestoreAndConnect.BitBtn3Click(Sender: TObject);
var
//ini:TFinifile;
wversionrequested:word;
wsadata:TWSAdata;
p:Phostent;
p2:pchar;
servername:string;
begin
if Trim(edit2.Text) = '' then
begin
if radiogroup1.ItemIndex=0 then ShowMessage('服务器名不能为空!')
else ShowMessage('计算机名不能为空!');
edit2.SetFocus;
Exit;
end; Screen.Cursor := crHourGlass;
if radiogroup1.ItemIndex=0 then //网络
begin
{连接 WinSock}
wVersionRequested := MAKEWORD(1, 1);
WSAStartup(wVersionRequested, wsaData);
{获得计算机名}
p := GetHostByName(PChar(Trim(edit2.Text)));
if p = nil then
begin
Screen.Cursor := crDefault;
ShowMessage('服务器连接失败!可能是所输入的服务器名有误,请重新输入。');
{断开 WinSock}
WSACleanup;
Exit;
end; {获得本机的ip地址}
p2 := iNet_ntoa(PInAddr(p^.h_addr_list^)^);
try servername:=p2;
adoconnection1.ConnectionString:='Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=student;Data Source='+servername;
adoconnection1.Connected:=true;
// adoq_pro.Active:=true;
except
showmessage('服务器连接失败!请检查数据库连接!');
exit;
end;
INI.Directory:=ExtractFilePath(Application.ExeName);
InI.Writestrings('数据库连接', 'Provider', 'SQLOLEDB.1');
ini.writestrings('数据库连接', 'Integrated Security','SSPI');
InI.Writestrings('数据库连接', 'Persist Security Info', 'False');
InI.Writestrings('数据库连接', 'User ID', 'sa');
InI.Writestrings('数据库连接', 'Initial Catalog', 'student');
InI.Writestrings('数据库连接', 'Data Source', p2);
//InI.Writestrings('数据库连接', 'Locale Identifier', '2052');
//InI.Writestrings('数据库连接', 'Connect TimeOut', '15');
InI.Writestrings('数据库连接', 'Use Procedure for Prepare', '1');
InI.Writestrings('数据库连接', 'Auto Translate', 'True');
InI.Writestrings('数据库连接', 'Packet Size', '4096');
InI.Writestrings('数据库连接', 'Workstation ID', idstack.Gstack.localaddress);
{断开 WinSock}
WSACleanup;
end else
begin //单机系统
INI.Directory:=ExtractFilePath(Application.ExeName);
InI.Writestrings('数据库连接', 'Provider', 'SQLOLEDB.1');
ini.writestrings('数据库连接', 'Integrated Security','SSPI');
InI.Writestrings('数据库连接', 'Persist Security Info', 'False');
InI.Writestrings('数据库连接', 'User ID', 'sa');
InI.Writestrings('数据库连接', 'Initial Catalog', 'student');
InI.Writestrings('数据库连接', 'Data Source', '');
//InI.Writestrings('数据库连接', 'Locale Identifier', '2052');
//InI.Writestrings('数据库连接', 'Connect TimeOut', '15');
InI.Writestrings('数据库连接', 'Use Procedure for Prepare', '1');
InI.Writestrings('数据库连接', 'Auto Translate', 'True');
InI.Writestrings('数据库连接', 'Packet Size', '4096');
InI.Writestrings('数据库连接', 'Workstation ID', trim(edit2.Text));
end;
Screen.Cursor := crDefault;
ShowMessage('数据库连接成功!');
// close;
end;
end.