unit1.~pas

delphi对sqlserver数据库进行备份操作

unit Unit1;

interface

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

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    AdoQueryBackup: TADOQuery;
    SaveDialog1: TSaveDialog;
    Button2: TButton;
    OpenDialog1: TOpenDialog;
    GroupBox1: TGroupBox;
    Button3: TButton;
    Label1: TLabel;
    GroupBox2: TGroupBox;
    Button1: TButton;
    Label2: TLabel;
    CB_backup: TComboBox;
    Label3: TLabel;
    Ed_str: TEdit;
    Button4: TButton;
    CB_restore: TComboBox;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var       //1200000,20分钟
    operation_msg:string;
    return_message:integer;  //messagedlg返回的信息, 3--mryes,4--mrno,0--点击了窗口的关闭按钮
    sql,run_time:string; 
    v_path:string;
    filename:string;
    FindResult,count:integer;
begin
    if not ADOConnection1.Connected then
    begin
        MessageDlg('请连接数据库！     ',mtinformation,[mbOK],0);
        Exit;
    end;

    if CB_backup.Items.IndexOf(CB_backup.Text)=-1 then
    begin
        MessageDlg('请选择要备份的数据库！     ',mtinformation,[mbOK],0);
        Exit;
    end;

    try
        run_time:=FormatDateTime('yyyymmdd',now());
 
        AdoQueryBackup.Close ;
        AdoQueryBackup.SQL.Clear;
        AdoQueryBackup.SQL.Text:='use master';//执得备份SQL语句
        AdoQueryBackup.ExecSQL;
        SaveDialog1.InitialDir := GetCurrentDir();
        SaveDialog1.FileName := FormatDateTime('yyyymmdd hhmmss',now())+'.bak';
        if SaveDialog1.Execute then
        begin
            v_path:=SaveDialog1.FileName;

            AdoQueryBackup.SQL.Clear;
            AdoQueryBackup.SQL.Text:='  backup database '+CB_backup.Text +' to disk='+''''+  SaveDialog1.FileName+'''' ;//执得备份SQL语句

            AdoQueryBackup.ExecSQL;
            MessageDlg('备份成功',mtinformation,[mbOK],0);
        end;

    except
        MessageDlg('备份失败',mtinformation,[mbOK],0);
    end;

end;

procedure TForm1.Button2Click(Sender: TObject);
begin
    self.Close ;
end;

procedure TForm1.Button3Click(Sender: TObject);
var
    v_path:string;
    dbname:string;
begin
    if not ADOConnection1.Connected then
        Exit;
    if Trim(CB_restore.Text) ='' then
    begin
        MessageDlg('请选择要还原的数据库！     ',mtinformation,[mbOK],0);
        Exit;
    end;

    v_path:='';
    if OpenDialog1.Execute then
        v_path:= OpenDialog1.FileName ;
    if v_path='' then
    begin
       // MessageDlg('请选择路径！     ',mtinformation,[mbOK],0);
        Exit;
    end;
    AdoQueryBackup.Close ;
    AdoQueryBackup.SQL.Clear;
    AdoQueryBackup.SQL.Text:='use master';
    AdoQueryBackup.ExecSQL;
    
    AdoQueryBackup.Close ;
    AdoQueryBackup.SQL.Clear;
    AdoQueryBackup.SQL.Text:=' RESTORE DATABASE ' +dbname+' FROM DISK = '''+v_path +'''';
    AdoQueryBackup.ExecSQL;

    AdoQueryBackup.Close ;
    AdoQueryBackup.SQL.Clear;
    AdoQueryBackup.SQL.Text:='use '+dbname;
    AdoQueryBackup.ExecSQL;

   { BACKUP DATABASE Northwind
   TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY
   FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
   FROM DISK = 'c:\Northwind.bak'
   WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
   MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'

       }
end;

procedure TForm1.Button4Click(Sender: TObject);
begin
    try
    ADOConnection1.ConnectionString :='Provider=SQLOLEDB.1;Password=sqlserver;Persist Security Info=True;User ID=sa;Initial Catalog=master;Data Source= ' +Trim(Ed_str.Text);
    ADOConnection1.Connected :=true;
        MessageDlg('连接成功！    ',mtinformation,[mbOK],0);
    except
        MessageDlg('连接失败！    ',mtinformation,[mbOK],0);
        Exit;
    end;

    
    AdoQueryBackup.Close ;
    AdoQueryBackup.SQL.Clear;
    AdoQueryBackup.SQL.Text:='Select name from sysdatabases order by dbid ';
    AdoQueryBackup.Open ;
    AdoQueryBackup.First ;
    CB_restore.Items.Clear ;
    while (not AdoQueryBackup.Eof) do
    begin
        CB_restore.Items.Add(AdoQueryBackup.FieldByName('name').AsString) ;
        AdoQueryBackup.Next ;
    end;
    CB_backup.Items := CB_restore.Items;
    
end;

end.