SQL数据库备份与还原 |
|
|
|
ntjrr
高階會員   發表：240 回覆：312 積分：110 註冊：2005-04-24 發送簡訊給我 |
有一段代码,能实现自动备份,但在还原时,如果原来无此数据库,那么还原能成功的.如果原来有数据库,只是想还原到以前的备份时的状态.但就无法覆盖了,虽然提示还原成功,但数据库还是没有变化.
unit UnitBackup; interface uses Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, Db,ADODB, ExtCtrls, ShellApi, Menus, ComCtrls, Registry, ScktComp , StdCtrls , Buttons, Variants,IniFiles ,StrUtils, ImgList ; const WM_BARICON=WM_USER 200; //自定义消息 ID_MAIN=100;//定义图标的ID type TFrmBackup = class(TForm) GroupBox1: TGroupBox; Label1: TLabel; Label2: TLabel; Label3: TLabel; Label4: TLabel; CmbServerName: TComboBox; CmbDatabaseName: TComboBox; EdtUserName: TEdit; EdtPassword: TEdit; GroupBox2: TGroupBox; GroupBox3: TGroupBox; btnSave: TBitBtn; btnCancel: TBitBtn; cboAutoRun: TCheckBox; EdtPath: TEdit; btnPath: TButton; btnSetTime: TBitBtn; connAdo: TADOConnection; connQuery: TADOQuery; btnBackup: TBitBtn; Timer1: TTimer; cboShowMessage: TCheckBox; PopupMenu: TPopupMenu; N1: TMenuItem; N2: TMenuItem; ImageList1: TImageList; cboMin: TCheckBox; cboClose: TCheckBox; cboStart: TCheckBox; BitBtn1: TBitBtn; Label5: TLabel; Label6: TLabel; edit1: TEdit; Button1: TButton; Button2: TButton; OpenDialog1: TOpenDialog; connAdo_res: TADOConnection; procedure btnSetTimeClick(Sender: TObject); procedure btnSaveClick(Sender: TObject); procedure btnBackupClick(Sender: TObject); procedure btnPathClick(Sender: TObject); procedure Timer1Timer(Sender: TObject); procedure FormCreate(Sender: TObject); procedure btnCancelClick(Sender: TObject); procedure N2Click(Sender: TObject); procedure N1Click(Sender: TObject); procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean); procedure BitBtn1Click(Sender: TObject); procedure Button1Click(Sender: TObject); procedure Button2Click(Sender: TObject); private { Private declarations } FilePath:String; BackupIniFile:TIniFile; ServerName,DatabaseName,UserName,Password:String; Run,StartMin,Show,Min,CloseShow:integer; Conn:Boolean; //判断联接是否成功 connString:String; //联接字符串 BackFileName:String; //备份文件的名称 procedure AddIcon(hwnd:HWND); procedure RemoveIcon(hwnd: HWND); //从状态区移去图标 procedure WMSysCommand(var Message: TMessage); message WM_SYSCOMMAND; procedure WMBarIcon(var Message:TMessage);message WM_BARICON; procedure WMMini(var Message:TMessage);message WM_SETFOCUS; procedure BackupBase(FileName:String); //定义备份过程 function RestoreDB(strDBName,strFileName: string): boolean; procedure ReadIni; //读取INI文件中的内容 public { Public declarations } BackupType,EveryTime,EveryDay,EveryMonth,EveryWeek:integer; end; var FrmBackup: TFrmBackup; implementation uses UnitFrmSetupTime, UnitFilePath, UnitDirPath, UnitDlg; {$R *.dfm} procedure TFrmBackup.AddIcon(hwnd:HWND); var lpData:PNotifyIconData; begin lpData := new(PNotifyIconDataA); lpData.cbSize := 88; lpData.Wnd := FrmBackup.Handle; lpData.hIcon := FrmBackup.Icon.Handle; lpData.uCallbackMessage := WM_BARICON; lpData.uID :=0; lpData.szTip := '数据库备份工具'; lpData.uFlags := NIF_ICON or NIF_MESSAGE or NIF_TIP; Shell_NotifyIcon(NIM_ADD,lpData); dispose(lpData); FrmBackup.Visible := False; end; procedure TFrmBackup.RemoveIcon(hwnd: HWND);//从状态区移去图标 var lpData:PNotifyIconData; begin //如果用户点击任务栏图标则将图标删除并回复窗口。 lpData := new(PNotifyIconDataA); lpData.cbSize := 88;//SizeOf(PNotifyIconDataA); lpData.Wnd := FrmBackup.Handle; lpData.hIcon := FrmBackup.Icon.Handle; lpData.uCallbackMessage := WM_BARICON; lpData.uID :=0; lpData.szTip := 'Samples'; lpData.uFlags := NIF_ICON or NIF_MESSAGE or NIF_TIP; Shell_NotifyIcon(NIM_DELETE,lpData); dispose(lpData); end; procedure TFrmBackup.WMSysCommand(var Message:TMessage); begin if Message.WParam = SC_ICON then begin //如果用户最小化窗口则将窗口 //隐藏并在任务栏上添加图标 AddIcon(handle); end else if (Message.WParam= SC_Close) and (Min=1)then begin Application.Minimize; AddIcon(handle); end else if (Message.WParam=WM_CREATE) and (Min=1)then begin Application.Minimize; AddIcon(handle); end else //如果是其它的SystemCommand //消息则调用系统缺省处理函数处理之。 DefWindowProc(FrmBackup.Handle,Message.Msg,Message.WParam,Message.LParam); end; procedure TFrmBackup.WMBarIcon(var Message:TMessage); var lpData:PNotifyIconData; Pt:TPoint; begin if (Message.LParam = WM_LBUTTONDOWN) then begin RemoveIcon(handle); FrmBackup.Visible := True; end else if (Message.LParam=WM_RBUTTONDOWN) then begin SetForeGroundWindow(lpData.Wnd); GetCursorPos(Pt); Popupmenu.Popup(Pt.X,pt.y); end; end; procedure TFrmBackup.WMMini(var Message:TMessage); begin if (Message.Msg=WM_CREATE) then begin Application.Minimize; AddIcon(handle); end end; //定义备份过程 procedure TFrmBackup.BackupBase(FileName:String); begin try screen.Cursor:=crSqlWait; with connQuery do begin close; Sql.Text:='Backup Database ' Trim(CmbDatabaseName.Text) ' to Disk=''' FileName ''' '; ExecSQL; end; screen.Cursor:=crDefault; frmdlg.Close; MessageBox(handle,'备份成功！','提示',MB_IconWarning mb_Ok); except frmdlg.Close; MessageBox(handle,'备份失败！','提示',MB_IconWarning mb_Ok); screen.Cursor:=crDefault; Exit; end; end; //定义还原过程 function TFrmBackup.RestoreDB(strDBName,strFileName: string): boolean; var strsql:TStringList; begin Result := false; strsql:=tstringlist.Create; strsql.Add('use master') ; strsql.Add('declare @spid int, @dbid int, @cmd nvarchar(200)') ; strsql.Add('declare c_processes cursor for') ; strsql.Add(' select spid, dbid from sysprocesses where dbid = db_id(''' CmbDatabaseName.Text ''')'); strsql.Add('open c_processes') ; strsql.Add('fetch next from c_processes into @spid, @dbid') ; strsql.Add('while @@fetch_status=0') ; strsql.Add('begin') ; strsql.Add(' set @cmd = ''kill'' convert(nvarchar, @spid)') ; strsql.Add('exec sp_executesql @cmd') ; strsql.Add('fetch next from c_processes into @spid, @dbid') ; strsql.Add('end') ; strsql.Add('close c_processes') ; strsql.Add('deallocate c_processes') ; strsql.Add('restore database ' strDBName ' from disk=''' edit1.Text '''with replace') ; try connAdo_res.Execute(strSQL.Text); except MessageBox(Application.Handle,'Restore Database Error!','It Is Error',MB_OK MB_ICONERROR); strsql.Free; Exit; end; Result := true; strsql.Free; end; //读取INI文件中的内容 procedure TFrmBackup.ReadIni; begin FilePath:=ExtractFilePath(Application.ExeName) 'System.ini'; BackupIniFile:=TIniFile.Create(FilePath); ServerName:=BackupIniFile.ReadString('Database','ServerName',''); DatabaseName:=BackupIniFile.ReadString('Database','DatabaseName',''); UserName:=BackupIniFile.ReadString('Database','UserName',''); Password:=BackupIniFile.ReadString('Database','Password',''); Run:=BackupIniFile.ReadInteger('AutoRun','Run',0); StartMin:=BackupIniFile.ReadInteger('AutoRun','StartMin',0); Show:=BackupIniFile.ReadInteger('AutoRun','Show',0); Min:=BackupIniFile.ReadInteger('AutoRun','Min',0); CloseShow:=BackupIniFile.ReadInteger('AutoRun','CloseShow',0); BackupType:=BackupIniFile.ReadInteger('Backup','BackupType',0); EveryTime:=BackupIniFile.ReadInteger('Backup','EveryTime',0); EveryDay:=BackupIniFile.ReadInteger('Backup','EveryDay',0); EveryWeek:=BackupIniFile.ReadInteger('Backup','EveryWeek',0); EveryMonth:=BackupIniFile.ReadInteger('Backup','EveryMonth',0); CmbServerName.Text:=ServerName; CmbDatabaseName.Text:=DatabaseName; EdtUserName.Text:=UserName; EdtPassword.Text:=Password; EdtPath.Text:=BackupIniFile.ReadString('FilePath','Path',''); if Run=1 then //初始化<随开机启动> cboAutoRun.Checked:=True else cboAutoRun.Checked:=False; if StartMin=1 then //初始化<启动后最小化> cboStart.Checked:=True else cboStart.Checked:=False; if Show=1 then //初始化<备份时显示提示信息> cboShowMessage.Checked:=True else cboShowMessage.Checked:=False; if Min=1 then //初始化<关闭时最小化> cboMin.Checked:=True else cboMin.Checked:=False; if CloseShow=1 then //初始化<不显示关闭信息> cboClose.Checked:=True else cboClose.Checked:=False; end; procedure TFrmBackup.btnSetTimeClick(Sender: TObject); begin Application.CreateForm(TFrmSetupTime,FrmSetupTime); FrmSetupTime.ShowModal; FrmSetupTime.Free; end; procedure TFrmBackup.btnSaveClick(Sender: TObject); var reg:TRegistry; S_RegTree:String; begin if not conn then //判断数据库联接是否成功，只有成功之后才能保存 begin MessageBox(handle,'数据库联接没有成功，所以不能保存！','错误',mb_IconWarning mb_Ok); CmbServerName.SetFocus; Exit; end; //将数据库联接参数保存到Ini文件中 BackupIniFile.WriteString('Database','ServerName',Trim(CmbServername.Text)); BackupIniFile.WriteString('Database','DatabaseName',Trim(CmbDatabaseName.Text)); BackupIniFile.WriteString('Database','UserName',Trim(EdtUserName.Text)); BackupIniFile.WriteString('Database','Password',Trim(EdtPassword.Text)); BackupIniFile.WriteString('FilePath','Path',Trim(EdtPath.Text)); //判断是否开机时起动系统 reg:=tregistry.Create; Reg.RootKey:=HKEY_LOCAL_MACHINE; S_RegTree:='\Software\Microsoft\Windows\CurrentVersion\Run'; if Reg.OpenKey(S_RegTree,False)=false then Reg.CreateKey(S_RegTree); Reg.OpenKey(S_RegTree,True); FilePath:=ExtractFilePath(Application.ExeName) 'Backup.exe'; if cboAutoRun.Checked then begin Reg.WriteString('BackupDatabase',FilePath); BackupIniFile.WriteInteger('AutoRun','Run',1); end else begin Reg.DeleteValue('BackupDatabase'); BackupIniFile.WriteInteger('AutoRun','Run',0); end; //启动后最小化 if cboStart.Checked then BackupIniFile.WriteInteger('AutoRun','StartMin',1) else BackupIniFile.WriteInteger('AutoRun','StartMin',0); //<备份时显示提示信息> if cboShowMessage.Checked then BackupIniFile.WriteInteger('AutoRun','Show',1) else BackupIniFile.WriteInteger('AutoRun','Show',0); //<关闭时最小化> if cboMin.Checked then BackupIniFile.WriteInteger('AutoRun','Min',1) else BackupIniFile.WriteInteger('AutoRun','Min',0); //<显示关闭信息> if cboClose.Checked then BackupIniFile.WriteInteger('AutoRun','CloseShow',1) else BackupIniFile.WriteInteger('AutoRun','CloseShow',0); Reg.Free; ReadIni(); end; procedure TFrmBackup.btnBackupClick(Sender: TObject); var FilePath:String; begin try if Show=1 then begin application.CreateForm(tfrmDlg,frmDlg); frmDlg.Show; end; //文件名等于<年月日时分秒> BackFileName:=Trim(CmbDatabaseName.Text) FormatDateTime('yyyymmddhhmmss',Now); //取得备份文件名 //取得地址框中的地址 if RightStr(EdtPath.text,1)='\' then FilePath:=EdtPath.Text else FilePath:=EdtPath.Text '\'; FilePath:=FilePath BackFileName; screen.Cursor:=crSqlWait; BackupBase(FilePath); //调用备份过程备份文件 screen.Cursor:=crDefault; if Show=1 then begin frmDlg.Close; frmDlg.Free; end; except MessageBox(handle,'备份失败！','提示',MB_IconWarning mb_Ok); screen.Cursor:=crDefault; Exit; end; end; procedure TFrmBackup.btnPathClick(Sender: TObject); begin Application.CreateForm(TFrmDirPath,FrmDirPath); if FrmDirPath.ShowModal=mrOk then EdtPath.Text:=FrmDirPath.DirectoryListBox1.Directory; end; procedure TFrmBackup.Timer1Timer(Sender: TObject); var Year,Month,Day,Hour,Min,Sec,MSec:Word; begin DecodeDate(Now,Year,Month,Day); DecodeTime(Now, Hour, Min, Sec, MSec); case BackupType of 0:begin//不备份 // end; 1:begin//每小时 if (Min=EveryTime) and (Sec=0) then btnBackupClick(btnBackup); end; 2:begin//每天 if (Hour=EveryDay) and (Min=EveryTime) and (Sec=0) then btnBackupClick(btnBackup); end; 3:begin//每周 if ((DayOfWeek(Date)-1)=EveryWeek) and (Hour=EveryDay) and (Min=EveryTime) and (Sec=0) then btnBackupClick(btnBackup); end; 4:begin//每月 if (Day=EveryMonth) and (Hour=EveryDay) and (Min=EveryTime) and (Sec=0) then btnBackupClick(btnBackup); end; end; end; procedure TFrmBackup.FormCreate(Sender: TObject); begin ReadIni(); connString:='Driver={SQL server};server=' Trim(CmbServerName.Text) ';database=' Trim(CmbDatabaseName.Text) ';uid=' Trim(EdtUserName.Text) ';pwd=' Trim(EdtPassword.Text); try connAdo.ConnectionString:=connString; connAdo.LoginPrompt:=False; connAdo.Connected:=True; conn:=True; except MessageBox(handle,'联接数据库错误','错误',mb_IconWarning mb_Ok); Exit; end; //取得服务器中所有数据库名 with connQuery do begin Close; Sql.Text:='select * from master.dbo.sysdatabases'; open; while not Eof do begin cmbDatabaseName.Items.Add(Fields[0].Value); next; end; end; //判断启动时是否最小化窗体 { if StartMin=1 then begin //Application.Minimize; AddIcon(handle); end; } end; procedure TFrmBackup.btnCancelClick(Sender: TObject); begin //取消所做的修改 ReadIni(); end; procedure TFrmBackup.N2Click(Sender: TObject); begin close; end; procedure TFrmBackup.N1Click(Sender: TObject); begin FrmBackup.Visible := True; end; procedure TFrmBackup.FormCloseQuery(Sender: TObject; var CanClose: Boolean); begin if CloseShow=1 then begin if MessageBox(Handle,'确实要关闭系统吗？','提示',MB_YESNO MB_ICONINFORMATION)=IDNO then begin canClose:=False; end else begin RemoveIcon(handle); Application.Terminate ; end; end else begin RemoveIcon(handle); Application.Terminate ; end; end; procedure TFrmBackup.BitBtn1Click(Sender: TObject); begin if Trim(CmbServerName.Text)='' then begin MessageBox(handle,'数据库服务器名称不能为空！','错误',mb_IconWarning mb_Ok); CmbServerName.SetFocus; Exit; end; if Trim(CmbDatabaseName.Text)='' then begin MessageBox(handle,'数据库名称不能为空！','错误',mb_IconWarning mb_Ok); CmbDatabaseName.SetFocus; Exit; end; if Trim(EdtUserName.Text)='' then begin MessageBox(handle,'用户名不能为空！','错误',mb_IconWarning mb_Ok); EdtUserName.SetFocus; Exit; end; connString:= 'Provider=SQLOLEDB.1;Password=' EdtPassword.Text ';Persist Security Info=true;User ID=''' Trim(edtUserName.Text) ''';Data Source=''' Trim(CmbServerName.Text) ''''; try screen.Cursor:=crSqlwait; with connAdo do begin if Connected then Connected:=False; ConnectionString:=connString; LoginPrompt:=False; Connected:=True; end; MessageBox(handle,'联接成功！','提示',MB_ICONINFORMATION mb_Ok); conn:=True; screen.Cursor:=crdefault; except MessageBox(handle,'联接失败！','提示',mb_IconWarning mb_Ok); screen.Cursor:=crdefault; conn:=False; Exit; end; end; procedure TFrmBackup.Button1Click(Sender: TObject); begin if OpenDialog1.Execute then Edit1.Text:=OpenDialog1.FileName; end; procedure TFrmBackup.Button2Click(Sender: TObject); var FilePath: String; begin if trim(edit1.Text)='' then MessageBox(handle,'还原文件不能为空！','提示',MB_ICONINFORMATION mb_Ok) else begin connAdo_res.Close; connAdo_res.ConnectionString := 'Provider=SQLOLEDB.1;Password=' EdtPassword.Text ';Persist Security Info=true;User ID=''' Trim(edtUserName.Text) ''';Data Source=''' Trim(CmbServerName.Text) ''''; try connAdo.Close; connAdo_res.Open; if connAdo_res.Connected then begin if Show=1 then begin application.CreateForm(tfrmDlg,frmDlg); frmDlg.Show; end; FilePath:=edit1.Text; if RestoreDB(Trim(CmbDatabaseName.Text),Trim(FilePath)) then if Show=1 then begin frmDlg.Close; frmDlg.Free; end; MessageBox(handle,'还原成功!','提示',mb_ok); end; except on ex:Exception do raise Exception.Create('Error:' #13#10 ex.Message); end; connAdo.Open; end; end; end.
------
我的编程起步于ktop，我将永远支持ktop |
|
baby2321
初階會員 發表：52 回覆：165 積分：48 註冊：2005-06-11 發送簡訊給我 |
|
|
fatmoon1
初階會員 發表：3 回覆：29 積分：36 註冊：2004-05-24 發送簡訊給我 |
我提供我的經驗,你試試看好了
我是在 DELPHI5 SQL2005 環境 ADOConnection1.Execute ( 'RESTORE DATABASE dbname FROM ' 'DISK = N''' edtRestoreFrom.Text ''' ' 'WITH RECOVERY' ); 所以 你將下列 strsql.Add('restore database ' strDBName ' from disk=''' edit1.Text '''with replace') ; 改成 strsql.Add('restore database ' strDBName ' from disk=''' edit1.Text '''with recovery'); 測看看
------
才疏學淺 |
|
ntjrr
高階會員 發表：240 回覆：312 積分：110 註冊：2005-04-24 發送簡訊給我 |
我另外参考了一个是能成功的了: sqlstr :=' RESTORE DATABASE ' Edit6.text ;
sqlstr :=sqlstr ' FROM disk = ' QuotedStr(Edit9.text); ADOCommand1.ConnectionString:='Provider=SQLOLEDB.1;Password=' EDIT3.TEXT ';Persist Security Info=True;User ID=' EDIT2.TEXT ';Initial Catalog=master;Data Source=' Edit1.TEXT ''; ADOCommand1.CommandText:=sqlstr; ADOCommand1.Execute;
------
我的编程起步于ktop，我将永远支持ktop |
發表時間：2007-02-01 19:57:46
IP:222.184.xxx.xxx 訂閱
系統時間：2024-05-19 13:41:55
聯絡我們 | Delphi K.Top討論版
| 本站聲明 |
|
1. 本論壇為無營利行為之開放平台，所有文章都是由網友自行張貼，如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權，而損及您的利益，請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨，是藍是綠本站無權干涉，但這裡不是政治性論壇！ |