实例应用1:
//备份procedure TF_DataBaseBackUp.Btn_bfClick(Sender: TObject);
var i:integer;
begin
if SaveDialog1.Execute then
begin
ADOConnection1.Connected:=False;
ADOConnection1.ConnectionString:=StringReplace(GetConnectionString,'Taxi','master',[rfReplaceAll]);
ADOConnection1.Connected:=True;
ADOQuery1.SQL.Text :='backup database Taxi to disk='+QuotedStr(SaveDialog1.FileName);
try
Btn_bf.Caption :=' 正在备份…';
self.repaint;
ADOQuery1.execsql;
for i:=1 to 100 do
begin
Btn_bf.Caption :=' 备份中…'+inttostr(i)+'%';
self.repaint;
sleep(50);
end;
ShowMessage('数据备份操作已经成功完成!');
except
ShowMessage('数据备份时出错!请重试。');
end;
Btn_bf.caption:=' 开始备份';
self.repaint;
end;
end;
//恢复
procedure TF_DataBaseBackUp.Btn_hfClick(Sender: TObject);
var i:integer;
begin
if Application.MessageBox('该操作会把当前程序数据改为备份时的状态,您确定要这么做吗?','提示',mb_okcancel+MB_ICONQUESTION )<>idok then exit;
if OpenDialog1.Execute then
begin
try
ADOConnection1.Connected:=False;
ADOConnection1.ConnectionString:=StringReplace(GetConnectionString,'Taxi','master',[rfReplaceAll]);
ADOConnection1.Connected:=True;
Btn_hf.Caption :=' 清除数据库连接...';
self.Repaint;
ClearDBConnections();
Btn_hf.Caption :=' 正在恢复...';
self.repaint;
ADOQuery1.SQL.Text :='restore database Taxi from disk='+QuotedStr(OpenDialog1.FileName);
ADOQuery1.execsql;
for i:=1 to 100 do
begin
Btn_hf.Caption :=' 恢复中…'+inttostr(i)+'%';
self.repaint;
sleep(50);
end;
Btn_hf.caption:=' 开始恢复';
self.repaint;
except
Application.MessageBox('数据恢复时出错!请重试。','提示',64);
Btn_hf.caption:=' 开始恢复';
self.repaint;
exit;
end;
application.MessageBox('数据库已成功恢复!请重新启动系统!','提示:',mb_ok+mb_iconinformation);
end;
end;
//------------------------------------------------------------------------------
//函数名称: ClearDBConnections
//函数功能: 清除数据库连接
//------------------------------------------------------------------------------
procedure TF_DataBaseBackUp.ClearDBConnections;
var vspid:string;
begin
//覆盖数据库,清除数据库现有连接
ADOQuery1.Close;
ADOQuery1.SQL.Text:='select spid from sysprocesses where dbid=db_id(''Taxi'')';
ADOQuery1.Open;
while not ADOQuery1.Eof do
begin
vspid:=ADOQuery1.FieldByName('spid').AsString;
ADOQuery2.Close;
ADOQuery2.SQL.Text:='kill '+vspid;
ADOQuery2.ExecSQL;
ADOQuery1.Next;
end;
end;
实例应用2:
procedure TF_BF.BitBtn1Click(Sender: TObject);
var inif:Tinifile;
T:string;
begin
inif:=Tinifile.Create(ExtractFilePath(Paramstr(0))+'data/SysSet.ini');
T:=inif.ReadString('Data','DbType','');
IF LENGTH(TRIM(EDIT2.Text))=0 THEN
BEGIN
APPLICATION.MessageBox('请指定数据备份的文件名!','林康软件',MB_OK+MB_ICONWARNING);
END
ELSE
BEGIN
if T='SQLserver' then
begin
ADOQUERY1.Close;
ADOQUERY1.SQL.Clear;
ADOQUERY1.SQL.Add('USE MASTER');
ADOQUERY1.SQL.ADD('BACKUP DATABASE GZGL to disk='+#39+SAVEDIALOG1.FileName+#39+' with init');
TRY
ADOQUERY1.ExecSQL;
APPLICATION.MessageBox('数据备份操作成功!','林康软件',MB_OK+MB_ICONWARNING);
EXCEPT
APPLICATION.MessageBox('数据备份操作失败!','林康软件',MB_OK+MB_ICONWARNING);
END;
close;
TRY
F_main.ADOConnection1.Close;
F_main.ADOConnection1.Open;
EXCEPT
APPLICATION.MessageBox('该系统需要重新启动, 请退出!','林康软件',MB_OK+MB_ICONWARNING);
application.Terminate;
END;
end;
if T='Access' then
begin
F_MAIN.ADOConnection1.Close;
TRY
COPYFILE(PCHAR(ExtractFilePath(Paramstr(0))+'DATA/gzgl.mdb'),PCHAR(EDIT2.Text),FALSE);
APPLICATION.MessageBox('数据备份操作成功!','林康软件',MB_OK+MB_ICONWARNING);
EXCEPT
APPLICATION.MessageBox('数据备份操作失败!','林康软件',MB_OK+MB_ICONWARNING);
END;
F_MAIN.ADOConnection1.Open;
end;
END;
end;
恢复:
procedure TF_HF.BitBtn1Click(Sender: TObject);
var
t:string;
inif:Tinifile;
begin
inif:=Tinifile.Create(ExtractFilePath(Paramstr(0))+'data/SysSet.ini');
T:=inif.ReadString('Data','DbType','');
IF LENGTH(TRIM(EDIT2.Text))=0 THEN
BEGIN
APPLICATION.MessageBox('请指定数据恢复的文件名!','林康软件',MB_OK+MB_ICONWARNING);
END
ELSE
BEGIN
if T='SQLserver' then
begin
if MESSAGEDLG('数据恢复操作将覆盖现有的数据,需要继续吗?',mtconfirmation,[MBYES,MBNO],1) <>Mryes THEN exit;
ADOQUERY1.Close;
ADOQUERY1.SQL.Clear;
ADOQUERY1.SQL.ADD('use master declare @spid int');
ADOQUERY1.SQL.ADD('declare getspid cursor for');
ADOQUERY1.SQL.ADD('select spid from sysprocesses where dbid=db_id('+#39+'GZGL'+#39+')');
ADOQUERY1.SQL.ADD('open getspid');
ADOQUERY1.SQL.ADD('fetch next from getspid into @spid');
ADOQUERY1.SQL.ADD('while @@fetch_status < >-1');
ADOQUERY1.SQL.ADD('begin');
ADOQUERY1.SQL.ADD('exec('+#39+'kill '+#39+'+@spid)');
ADOQUERY1.SQL.ADD('fetch next from getspid into @spid');
ADOQUERY1.SQL.ADD('end');
ADOQUERY1.SQL.ADD('close getspid');
ADOQUERY1.SQL.ADD('deallocate getspid');
ADOQUERY1.SQL.ADD('RESTORE DATABASE GZGL FROM disk='+#39+OPENDIALOG1.FileName+#39+' WITH REPLACE');
TRY
ADOQUERY1.ExecSQL;
APPLICATION.MessageBox('数据恢复操作成功!','林康软件',MB_OK+MB_ICONWARNING);
EXCEPT
APPLICATION.MessageBox('数据恢复操作失败!','林康软件',MB_OK+MB_ICONWARNING);
END;
close;
TRY
F_main.ADOConnection1.Close;
F_main.ADOConnection1.Open;
EXCEPT
APPLICATION.MessageBox('该系统需要重新启动, 请退出!','林康软件',MB_OK+MB_ICONWARNING);
application.Terminate;
END;
end;
if T='Access' then
begin
F_MAIN.ADOConnection1.Close;
TRY
COPYFILE(PCHAR(EDIT2.Text),PCHAR(ExtractFilePath(Paramstr(0))+'DATA/Gzgl.mdb'),FALSE);
APPLICATION.MessageBox('数据恢复操作成功!','林康软件',MB_OK+MB_ICONWARNING);
EXCEPT
APPLICATION.MessageBox('数据恢复操作失败!','林康软件',MB_OK+MB_ICONWARNING);
END;
F_MAIN.ADOConnection1.Open;
end;
END;
end;
实例应用3:
最简单的sql语句:备份与还原sql server自带的数据库
在服务器上备份:
use northwind
backup database northwind to disk=d:/northwind_bak.dat with init
restore database northnwind from disk = d:/northwind_bak.dat
------------------------------------------------------------------
备份数据库这一操作在客户机上实现
客户机:machine
共享目录:share
backup:
bakcup database dbname to disk=//machine/share/data.bak with init
//machine/share目录要有写权限。
restore:
restore database dbname from disk=//machine/share/data.bak
//
备注:restore 语句有很多的选项,可以查看企业管理器的在线帮助。如下
with replace, move dbname_dat to c:/mssql7/data/dbname.mdf,
move dbname_log to c:/mssql7/data/dbname.log
其中c:/mssql7/data/是服务器的目录,这点要注意