u_frmmain.pas

delphi编写成的模拟ＳＱＬＳＥＶＥＲ数据库还原数据库的程序

unit u_frmMain;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Buttons, DB, ADODB, Grids, DBGrids, ComCtrls, ExtCtrls;

type
  TfrmMain = class(TForm)
    cnn: TADOConnection;
    qry1: TADOQuery;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    TabSheet3: TTabSheet;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    DBGrid2: TDBGrid;
    qry2: TADOQuery;
    DataSource2: TDataSource;
    dstDatabase: TADODataSet;
    dstFilename: TADODataSet;
    edtMDF: TEdit;
    Label1: TLabel;
    OpenDialog: TOpenDialog;
    Button2: TButton;
    edtLDF: TEdit;
    Label2: TLabel;
    edtDb: TEdit;
    Label3: TLabel;
    lstDB2: TListBox;
    Button5: TButton;
    qryAttach: TADOQuery;
    cbPath: TCheckBox;
    edtMasterpath: TEdit;
    Button3: TButton;
    Button4: TButton;
    lstDB1: TListBox;
    Panel1: TPanel;
    SpeedButton1: TSpeedButton;
    Button1: TButton;
    cmbCnn: TComboBox;
    Splitter1: TSplitter;
    Splitter2: TSplitter;
    CheckBox1: TCheckBox;
    edtBakFile: TEdit;
    Label4: TLabel;
    Button6: TButton;
    qry3: TADOQuery;
    ds2: TDataSource;
    ds3: TDataSource;
    GroupBox1: TGroupBox;
    DBGrid3: TDBGrid;
    DBGrid4: TDBGrid;
    Label5: TLabel;
    procedure Button1Click(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button6Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmMain: TfrmMain;

implementation
var
  sMasterpath: string;
  sSingl: string = 'EXEC sp_attach_single_file_db @dbname = ''%s'', @physname = ''%s''';
  sDb: string = 'EXEC sp_attach_db @dbname = ''%s'',@filename1 = ''%s'', @filename2 = ''%s''';
  sname, sTypeName: string;




{$R *.dfm}

procedure TfrmMain.Button1Click(Sender: TObject);
var
  s: string;
begin
  s := PromptDataSource(0, 'Provider=SQLOLEDB.1;Persist Security Info=False');
  cmbCnn.Items.Add(s);

end;

procedure TfrmMain.SpeedButton1Click(Sender: TObject);
var
  b: boolean;
begin
  dstDatabase.Close;
  dstFilename.Close;

  cnn.Close;
  cnn.ConnectionString := cmbCnn.Text;
  cnn.DefaultDatabase := 'master';
  b := true;
  try
    cnn.open;
  except
    on e: Exception do
    begin
      ShowMessage('connect error');
      b := false;
    end;
  end;

  if b then
  begin
    dstDataBase.Open;
    dstFileName.Open;
    dstDataBase.DisableControls;
    dstFilename.DisableControls;
    lstDB1.Clear;
    lstDB2.Clear;
    while not dstDatabase.Eof do
    begin
      lstDB1.Items.Add(dstDatabase.fieldbyname('name').AsString);
      lstDB2.Items.Add(dstDatabase.fieldbyname('name').AsString);
      if UpperCase(dstDatabase.fieldbyname('name').AsString) = 'MASTER' then
      begin
        sMasterpath := ExtractFilePath(dstDatabase.fieldbyname('filename').AsString);
        edtMasterpath.Text := sMasterpath;
      end;
      dstDatabase.next;
    end;
    dstDataBase.EnableControls;
    dstFilename.EnableControls;
  end;
end;

procedure TfrmMain.Button2Click(Sender: TObject);
var
  i, iPos: integer;
  sname: string;
begin
  OpenDialog.FilterIndex := 1;
  if OpenDialog.Execute then
  begin
    for i := 0 to OpenDialog.Files.Count - 1 do
    begin
      if UpperCase(ExtractFileExt(OpenDialog.Files[i])) = '.MDF' then
      begin
        edtMDF.Text := OpenDialog.Files[i];
        sName := ExtractFileName(edtMdf.Text);
        ipos := pos('_', sname);
        if iPos > 1 then
        begin
          edtDB.Text := copy(sName, 1, ipos - 1);
        end;
      end;

      if UpperCase(ExtractFileExt(OpenDialog.Files[i])) = '.LDF' then
        edtLDF.Text := OpenDialog.Files[i];
    end;
  end;
end;

procedure TfrmMain.Button5Click(Sender: TObject);
var
  sp: string;
  sDmdf, sDldf: string;
begin
  sDmdf := '';
  sDldf := '';
  if edtDb.Text = '' then
  begin
    showmessage('没有数据库名');
  end;

  if cbPath.Checked then
  begin
    if not DirectoryExists(edtMasterpath.Text) then
    begin
      showmessage('路径不存在:' + edtMasterpath.Text);
      exit;
    end;
    sp := edtMasterpath.Text;
    if sp[length(sp)] <> '\' then
      sp := sp + '\';
    sDmdf := sp + ExtractFileName(edtMdf.Text);

    if not copyfile(pchar(edtMDF.text), pchar(sDmdf), true) then
    begin
      showmessage('拷贝文件失败:' + edtMDF.Text);
      exit;
    end;
    if edtldf.Text <> '' then
    begin


      sDldf := sp + ExtractFileName(edtLdf.Text);
      if not copyfile(pchar(edtLDF.text), pchar(sDldf), true) then
      begin
        showmessage('拷贝文件失败:' + edtMDF.Text);
        exit;
      end;
    end;
  end
  else
  begin
    sDmdf := edtMdf.Text;
    sDldf := edtLdf.Text;
  end;

  with qryAttach do
  begin
    close;
    with sql do
    begin
      Clear;
      if sDldf = '' then
      begin
        add(format(sSingl, [edtdb.text, sDmdf]));
      end
      else
        add(format(sdb, [edtdb.text, sDmdf, sDldf]));
    end;
    try
      ExecSQL;
      ShowMessage('ok');
    except
      on e: exception do
        showmessage(e.Message);
    end;
  end;
end;

procedure TfrmMain.FormActivate(Sender: TObject);
begin
  OnActivate := nil;
  cmbCnn.Items.LoadFromFile('.\conn.txt');
  if cmbCnn.Items.Count > 0 then
  begin
    cmbCnn.ItemIndex := 0;
  end;
end;

procedure TfrmMain.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  cmbCnn.Items.SaveToFile('.\conn.txt')
end;

procedure TfrmMain.Button3Click(Sender: TObject);
var
  sDb, sBak: string;

begin
  if lstDb2.SelCount = 0 then
  begin
    showmessage('请先选择数据库！');
    exit;
  end;
  sDb := lstDb2.Items[lstDb2.ItemIndex];
  sBak := FormatDateTime('yyyymmddhhmmsszzz', now());
  InputQuery('请输入备份文件', '数据库', sBak);

end;

procedure TfrmMain.Button4Click(Sender: TObject);
var
  ssql,sDb,s1,s2,s3,s4,s5: string;
begin

  if lstDb2.ItemIndex < 0 then
  begin
    showmessage('请先选择数据库！');
    exit;
  end;

  sDb := lstDb2.Items[lstDb2.ItemIndex];
  if not qry3.Active then
    exit;
  if sTypeName='' then
   exit;


  dstDatabase.Locate('name',sDb,[]);

  dstFilename.First;
  qry1.Close;
  //这里偷了个懒 ，应该判断文件属性的
  s1 := dstFilename.FieldByName('filename').AsString;
  dstFilename.next;
  s2 := dstFilename.FieldByName('filename').AsString;

  qry3.First;
  s3:= qry3.FieldByName('logicalname').AsString;
  s5 := qry3.fieldbyname('physicalname').AsString;
  qry3.Next;
  if not qry3.Eof then
    s4:=  qry3.FieldByName('logicalname').AsString;
  //qry2.Close;
  //qry3.Close;
  s1 := trim(s1);
  s2:= trim(s2);
  s3:=trim(s3);
  s4:=trim(s4);

  qry1.Close;
  qry1.SQL.Clear;
  if s1 = s5 then
  begin
    qry1.SQL.Add('RESTORE DATABASE '+sDb);
    qry1.SQL.Add(' FROM ['+sTypeName+']');
  end
  else
  begin
    qry1.SQL.Add(' RESTORE DATABASE '+sDb);
    qry1.SQL.Add('  FROM ['+sTypeName+']');
    qry1.SQL.add(' with    REPLACE , ');
    qry1.SQL.add('MOVE '''+s3+''' TO '''+s1+''',');
    qry1.SQL.Add(' MOVE '''+s4+''' TO '''+s2+'''');
  end;
  try
    qry1.ExecSQL;
  Except
    on e:exception do
            ShowMessage(e.message);

  end;
  showmessage('还原成功');

end;

procedure TfrmMain.Button6Click(Sender: TObject);
var
  b: boolean;
begin
  OpenDialog.FilterIndex := 2;
  if OpenDialog.Execute then
  begin
    edtBakFile.Text := OpenDialog.FileName;

    with qry1 do
    begin
      close;
      sql.clear;
      //备份设备是否存在
      sql.Text := 'select * from sysdevices where phyname = ''' + edtBakFile.Text + '''';
      open;
      if Recordcount > 0 then

      begin
        sname := edtBakFile.Text;

        sTypeName := fieldbyname('name').AsString;
        close;
        //防止备份设备已更新
        sql.text := 'exec sp_dropdevice @logicalname = '''+ sTypeName + '''';

        b := true;

        try
          ExecSQL;
        except
          on e: exception do
          begin
            b := false;
            ShowMessage(e.message);
          end;
        end;
      end;
      if  b then
      begin
        sname := edtBakFile.Text;

        sTypeName := FormatDateTime('yymmddhhmmss', now);
        close;
        sql.text := 'exec sp_addumpdevice ''disk'',''' + sTypeName + ''', ''' + sname + '''';
        b := true;
        try
          ExecSQL;
        except
          on e: exception do
          begin
            b := false;
            ShowMessage(e.message);
          end;
        end;
        if not b then
          exit; 
      end;
    end;


    qry2.Close;
    qry2.SQL.Text := 'RESTORE LABELONLY from [' + sTypeName+']';
    qry2.open;
    qry3.Close;
    qry3.SQL.Text := 'RESTORE FILELISTONLY  from [' + sTypeName+']';
    qry3.Open;
  end;
end;

end.