Delphi数据库备份

此处代码只是测试代码，仅仅是测试

//环境：D7+SQL Server 2008

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ADODB, DB, FolderDialog;

type
  TForm1 = class(TForm)
    Button1: TButton;
    OpenDialog1: TOpenDialog;
    SaveDialog1: TSaveDialog;
    Button2: TButton;
    Edit1: TEdit;
    ADOQuery1: TADOQuery;
    ADOConnection1: TADOConnection;
    ADOStoredProc1: TADOStoredProc;
    edtDBPlacePosition: TEdit;
    btnDBRestore: TButton;
    FolderDialog1: TFolderDialog;
    labDBPlacePosition: TLabel;
    labDBBackSource: TLabel;
    btnDBBackSource: TButton;
    edtDBBackSource: TEdit;
    btnDBPlacePosition: TButton;
    Edit2: TEdit;
    Memo1: TMemo;
    Button3: TButton;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure btnDBRestoreClick(Sender: TObject);
    procedure btnDBBackSourceClick(Sender: TObject);
    procedure btnDBPlacePositionClick(Sender: TObject);
    procedure Button3Click(Sender: TObject);
  private
    procedure DropDB(vDB:string);
    procedure CreateDB(vDB:string);
    procedure RestoreFailDeal(vDB:string);
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

const
  uConStr:string='Provider=SQLOLEDB.1;Password=sa123456;Persist Security Info=True;User ID=sa;Initial Catalog=MyDB;Data Source=192.168.1.50\SQLExpress';
  uConStrMaster:string='Provider=SQLOLEDB.1;Password=sa123456;Persist Security Info=True;User ID=sa;Initial Catalog=master;Data Source=192.168.1.50\SQLExpress';


   //CntSQLStr = 'Provider=SQLOLEDB.1;Data Source=%s;User ID=%s;Password=%s;Persist Security Info=True;Initial Catalog=%s';
  //CntSQLStrWin='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=%s;Initial Catalog=%s';
{$R *.dfm}
procedure TForm1.DropDB(vDB: string);
var
  SQLStr:string;
begin
   {SQLStr:='IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'''+
    vDB+''')'+ #13#10+
          '    DROP DATABASE ['+vDB+']'; }
  SQLStr:=Format('IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N''%s'') '+#13#10
       +' begin '+#13#10
       +' ALTER DATABASE %s SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;'+#13#10
       +' DROP DATABASE %s ;'+#13#10
       //+' ALTER DATABASE %S SET MULTI_USER '+#13#10
       +' end ;',[vDB,'['+vDB+']','['+vDB+']']);
  ADOQuery1.SQL.Text:=SQLStr;
  ADOQuery1.ExecSQL;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  vDataBackStr:String;
  vPath:string;

  SQLStr:String;
begin

  vDataBackStr:='MyDB_'+FormatDateTime('yyyymmddhhmmss',Now)+'.bak';
  vPath:=Edit1.Text+'\'+vDataBackStr;
  SaveDialog1.FileName:=vPath;
  if SaveDialog1.Execute  then
  begin
    //
  end
  else
    Exit;
  ADOConnection1.Connected:=False;
  ADOConnection1.ConnectionString:=uConStr;
  ADOConnection1.Connected:=True;
  vPath:=SaveDialog1.FileName;
  SQLStr:='BACKUP DATABASE "' + 'MyDB' + '" TO DISK = ''' + vPath + '''';
  ADOQuery1.SQL.Text:=SQLStr;
  ADOQuery1.ExecSQL;
  ShowMessage('备份成功！');
end;

procedure TForm1.Button2Click(Sender: TObject);
var
  DBFile,CntStr,SQLStr,DataFile,LogFile,vDBName:string;
begin
  {DBFile:=Edit2.Text;
  ADOConnection1.Connected:=False;
  ADOConnection1.ConnectionString:=uConStrMaster;
  ADOConnection1.Connected:=True;
  //新建一个空数据库
  vDBName:='MyDB';
  DropSourceDB(vDBName);
  SQLStr:='Create database ['+vDBName+']';

  ADOQuery1.SQL.Text:=SQLStr;
  ADOQuery1.ExecSQL;
  sleep(1000);

  //还原数据库到一个新建的数据库中
  SQLStr:='RESTORE DATABASE ['+vDBName+'] FROM  DISK = N'''+DBFile
  +''' WITH  FILE = 1,  NOUNLOAD ,  STATS = 10,  RECOVERY , REPLACE , '+
  ' MOVE N'''+vDBName+'_Data'' TO N'''+edit1.text+'\'+vDBName+'.mdf'+''','+
  ' MOVE N'''+vDBName+'_Log'' TO N'''+edit1.text+'\'+vDBName+'_log.ldf'+'''';
  try
    ADOQuery1.SQL.Text:=SQLStr;
    ADOQuery1.ExecSQL;
  except
    DropSourceDB(vDBName);
  end;  }
end;

procedure TForm1.btnDBRestoreClick(Sender: TObject);
var
  DBFile,CntStr,SQLStr,DataFile,LogFile,vDBName:string;
begin
  DBFile:=edtDBBackSource.Text;
  if Length(edtDBBackSource.Text)<2 then
  begin
    ShowMessage('数据库备份文件的文件错误！');
    exit;
  end
  else if Length(edtDBPlacePosition.Text)<2 then
  begin
    ShowMessage('数据库放置的位置路径错误！');
    exit;
  end;
  if Application.MessageBox(Pchar('确定恢复(还原)数据库吗？'+#13#10'此动作一旦执行就无法撤回'+#13#10'请慎重操作！'),
     Pchar(Application.Title), MB_OKCANCEL + MB_ICONQUESTION) = mrOk then
  begin

  end
  else
    Exit;

  ADOConnection1.Connected:=False;
  ADOConnection1.ConnectionString:=uConStrMaster;
  ADOConnection1.Connected:=True;
  //新建一个空数据库
  vDBName:='MyDB';
  CreateDB(vDBName);
  //SQLStr:='Create database ['+vDBName+']';

  //ADOQuery1.SQL.Text:=SQLStr;
  //ADOQuery1.ExecSQL;
  //sleep(1000);

  //还原数据库到一个新建的数据库中
  SQLStr:='RESTORE DATABASE ['+vDBName+'] FROM  DISK = N'''+DBFile
  +''' WITH  FILE = 1,  NOUNLOAD ,  STATS = 10,  RECOVERY , REPLACE , '+
  ' MOVE N'''+vDBName+''' TO N'''+edtDBPlacePosition.Text+'\'+vDBName+'.mdf'+''','+
  ' MOVE N'''+vDBName+'_Log'' TO N'''+edtDBPlacePosition.Text+'\'+vDBName+'_log.ldf'+'''';
  try
    ADOQuery1.SQL.Text:=SQLStr;
    Memo1.Text:=ADOQuery1.SQL.Text;
    ADOQuery1.ExecSQL;
    ShowMessage('恢复成功！');
  except
    RestoreFailDeal(vDBName);
    ShowMessage('还原失败！');
  end;

end;

procedure TForm1.btnDBBackSourceClick(Sender: TObject);
begin
  if SaveDialog1.Execute  then
  begin
    edtDBBackSource.Text:=SaveDialog1.FileName;
  end
end;

procedure TForm1.btnDBPlacePositionClick(Sender: TObject);
begin
  FolderDialog1.Directory:=ExtractFilePath(Application.ExeName);
  if FolderDialog1.Execute then
  begin
    edtDBPlacePosition.Text:=FolderDialog1.Directory
  end
end;

procedure TForm1.CreateDB(vDB: string);
var
  SQLStr:string;
begin
  SQLStr:=Format('IF  EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N''%s'') '+#13#10
            +' begin '+#13#10
            +'   ALTER DATABASE %s SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;'+#13#10
            +'   DROP DATABASE %s ;'+#13#10
            +' end ;'+#13#10
            +' Create database %s ;'+#13#10
            ,[vDB,'['+vDB+']','['+vDB+']','['+vDB+']']);

  ADOQuery1.SQL.Text:=SQLStr;
  ADOQuery1.ExecSQL;
end;

procedure TForm1.RestoreFailDeal(vDB: string);
var
  SQLStr:string;
begin
  SQLStr:=Format('IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N''%s'') '+#13#10
            +' begin '+#13#10
            +' ALTER DATABASE %s SET MULTI_USER  ;'+#13#10
            +' end ;',[vDB,'['+vDB+']']);
  ADOQuery1.SQL.Text:=SQLStr;
  ADOQuery1.ExecSQL;
end;

procedure TForm1.Button3Click(Sender: TObject);
begin

  ShowMessage(ExtractFileDir(ParamStr(0)));
end;

end.

object Form1: TForm1
  Left = 588
  Top = 390
  Width = 755
  Height = 413
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object labDBPlacePosition: TLabel
    Left = 240
    Top = 115
    Width = 139
    Height = 13
    Alignment = taRightJustify
    Caption = '数据库放置的位置：'
  end
  object labDBBackSource: TLabel
    Left = 251
    Top = 80
    Width = 128
    Height = 13
    Alignment = taRightJustify
    Caption = '数据库备份文件：'
  end
  object Button1: TButton
    Left = 24
    Top = 128
    Width = 75
    Height = 25
    Caption = '备份'
    TabOrder = 0
    OnClick = Button1Click
  end
  object Button2: TButton
    Left = 96
    Top = 8
    Width = 75
    Height = 25
    Caption = '恢复'
    TabOrder = 1
    OnClick = Button2Click
  end
  object Edit1: TEdit
    Left = 8
    Top = 96
    Width = 217
    Height = 21
    ImeName = '中文(简体) - 搜狗拼音输入法'
    TabOrder = 2
    Text = 'D:\program files\TXHR_BACK'
  end
  object edtDBPlacePosition: TEdit
    Left = 384
    Top = 112
    Width = 249
    Height = 21
    ImeName = '中文(简体) - 搜狗拼音输入法'
    TabOrder = 3
  end
  object btnDBRestore: TButton
    Left = 480
    Top = 141
    Width = 81
    Height = 25
    Caption = '还原(恢复)'
    TabOrder = 4
    OnClick = btnDBRestoreClick
  end
  object btnDBBackSource: TButton
    Left = 632
    Top = 77
    Width = 25
    Height = 25
    Caption = '...'
    TabOrder = 5
    OnClick = btnDBBackSourceClick
  end
  object edtDBBackSource: TEdit
    Left = 384
    Top = 80
    Width = 249
    Height = 21
    ImeName = '中文(简体) - 搜狗拼音输入法'
    TabOrder = 6
  end
  object btnDBPlacePosition: TButton
    Left = 632
    Top = 112
    Width = 25
    Height = 25
    Caption = '...'
    TabOrder = 7
    OnClick = btnDBPlacePositionClick
  end
  object Edit2: TEdit
    Left = 8
    Top = 72
    Width = 217
    Height = 21
    ImeName = '中文(简体) - 搜狗拼音输入法'
    TabOrder = 8
    Text = 'Edit2'
  end
  object Memo1: TMemo
    Left = 264
    Top = 176
    Width = 425
    Height = 185
    ImeName = '中文(简体) - 搜狗拼音输入法'
    Lines.Strings = (
      'Memo1')
    ReadOnly = True
    ScrollBars = ssBoth
    TabOrder = 9
  end
  object Button3: TButton
    Left = 80
    Top = 256
    Width = 75
    Height = 25
    Caption = 'Button3'
    TabOrder = 10
    OnClick = Button3Click
  end
  object OpenDialog1: TOpenDialog
    Filter = 'MS SQL Server (*.bak)|*.bak'
    Left = 664
    Top = 64
  end
  object SaveDialog1: TSaveDialog
    Filter = 'MS SQL Server (*.bak)|*.bak'
    Left = 48
    Top = 32
  end
  object ADOQuery1: TADOQuery
    Connection = ADOConnection1
    Parameters = <>
    Left = 128
    Top = 144
  end
  object ADOConnection1: TADOConnection
    ConnectionString =
      'Provider=SQLOLEDB.1;Password=sa123456;Persist Security Info=True' +
      ';User ID=sa;Initial Catalog=TXHR;Data Source=192.168.1.50\SQLExp' +
      'ress'
    Provider = 'SQLOLEDB.1'
    Left = 168
    Top = 144
  end
  object ADOStoredProc1: TADOStoredProc
    Parameters = <>
    Left = 200
    Top = 128
  end
  object FolderDialog1: TFolderDialog
    DialogX = 0
    DialogY = 0
    Version = '1.1.0.1'
    Left = 664
    Top = 112
  end
end