数据库备份的奇怪问题 高手进

[lhs203203]

如果这个窗体单独执行没有问题
如果用

 with TFrm_BackDB.Create(nil) do
    ShowModal;

调用后 每次在执行 ExecSQL后都不对 并报错 对象名‘users’无效
代码如下：

unit UFrm_BackDB;

interface

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

type
  TFrm_BackDB = class(TForm)
    edtPath: TEdit;
    Label1: TLabel;
    btnBrowse: TButton;
    btnStart: TButton;
    Button3: TButton;
    dlgSaveDB: TSaveDialog;
    procedure btnBrowseClick(Sender: TObject);
    procedure btnStartClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Frm_BackDB: TFrm_BackDB;

implementation

uses UConfig, UMainDM;
{$R *.dfm}

procedure TFrm_BackDB.btnBrowseClick(Sender: TObject);
var
  fName: string;
begin
  fName := '智能照明控制系统_' + FormatDateTime('yymmddhhmmss', Now);
  dlgSaveDB.FileName := fName;
  dlgSaveDB.Filter := '所有文件(*.*)|*.*|数据库文件(*.bak)|*.bak|数据库文件(*.trn)|*.trn';
  if dlgSaveDB.Execute then
  begin
    if ExtractFileExt(dlgSaveDB.FileName) <> '.bak' then
      dlgSaveDB.FileName := ChangeFileExt(dlgSaveDB.FileName, '.bak');
    edtPath.Text := dlgSaveDB.FileName;
  end;
end;

procedure TFrm_BackDB.btnStartClick(Sender: TObject);
var
  SQLText: string;
begin
  try
    MainDM.conMain.Connected := False; // 断开连接
    // 切换数据库
    MainDM.conMain.ConnectionString :=
      'Provider=SQLOLEDB.1;Persist Security Info=False;' +
      'User ID=sa;Password=1;' + 'Initial Catalog = master' + ';Data Source=' +
      TConfiger.GetKeyValue('ServerConfig', 'ServerName')+
      ';Use Procedure for Prepare=1;Auto Translate=True;' +
      'Packet Size=4096; Workstation ID=' + TConfiger.GetKeyValue
      ('ServerConfig', 'ServerName') +
      ';Use Encryption for Data=False;' +
      'Tag with column collation when possible=False ';

    MainDM.conMain.Connected := True; // 连接数据库
    with MainDM.qryPrivate do { 执行备份 }
    begin
      Close;
      SQLText := Format('Backup DataBase %s to disk = %s',
        [TConfiger.GetKeyValue('ServerConfig', 'DBName'),
        Quotedstr(edtPath.Text)]);
      SQL.Add(SQLText);
       try
         ExecSQL;
         Application.MessageBox('备份数据库成功！', '系统提示', MB_OK + MB_ICONINFORMATION);
       except
         Application.MessageBox('备份数据库失败，请联系管理员！', '系统错误', MB_OK + MB_ICONERROR);
       end;
    end;
  finally
    // 切换回主数据库
    MainDM.conMain.Connected := False;
    MainDM.conMain.ConnectionString :=
      'Provider=SQLOLEDB.1;Persist Security Info=False;' +
      'User ID=sa;Password=1;' + 'Initial Catalog =' + TConfiger.GetKeyValue
      ('ServerConfig', 'DBName') + ';Data Source=' + TConfiger.GetKeyValue
      ('ServerConfig', 'ServerName');
    MainDM.conMain.Connected := True;
  end;
end;

end.

[lhs203203]

问题解决了 但谁能告诉我原因
解决的方法是在备份数据库的窗体上加上给 TADOQuery 然后再将TADOQuery的连接指向MainDM的主连接
就可以备份成功
虽然解决了问题 但我确实不知道是怎么回事 有高手帮我从理论上解释下么？

   qryBackDB.Connection := MainDM.conMain;
    with qryBackDB do { 执行备份 }
    begin
      Close;
      SQLText := Format('Backup DataBase %s to disk = %s',
        [TConfiger.GetKeyValue('ServerConfig', 'DBName'),
        Quotedstr(edtPath.Text)]);
      SQL.Add(SQLText);
       try
         ExecSQL;
         Application.MessageBox('备份数据库成功！', '系统提示', MB_OK + MB_ICONINFORMATION);
       except
         Application.MessageBox('备份数据库失败，请联系管理员！', '系统错误', MB_OK + MB_ICONERROR);
       end;
    end;
  

[火龙岛主]

如果你不连接，qryBackDB不知道要在哪个数据库上操作。

[纯冰糖]

    MainDM.conMain.Connected := True; // 连接数据库
    with MainDM.qryPrivate do { 执行备份 }
    begin
      Close;
      SQL.Clear; //增加这句
      SQLText := Format('Backup DataBase %s to disk = %s',
        [TConfiger.GetKeyValue('ServerConfig', 'DBName'),
        Quotedstr(edtPath.Text)]);
      SQL.Add(SQLText);
       try
         ExecSQL;
         Application.MessageBox('备份数据库成功！', '系统提示', MB_OK + MB_ICONINFORMATION);
       except
         Application.MessageBox('备份数据库失败，请联系管理员！', '系统错误', MB_OK + MB_ICONERROR);
       end;
    end;

你这段代码也可以的啊。
执行SQL语句总得有个执行的组件吧

[masterjames]

连接，主要是选定数据库，操作还是要具体组件

[Oraclers]

是不是SQL語句中還有原來的未清除，先加一個Clear語句試試。

[lhs203203]

经过学习 现将优化的代码贴出来供大家指正 ^_^
备份代码：

procedure TFrm_BackDB.btnStartClick(Sender: TObject);
begin
  if edtPath.Text = '' then
  begin
    Application.MessageBox('请按浏览按钮选择保存的路径！', '系统提示',
      MB_OK + MB_ICONINFORMATION);
    Exit;
  end;
  pbEnd.Position := 0; { 进度条 }
  pbEnd.Visible := True;
  btnStart.Enabled := False;
  pbEnd.StepIt;
  try
    MainDM.conMain.Close; {关闭主连接}
    pbEnd.StepIt;

    cmdBackDB.Connection := MainDM.conMain;
    cmdBackDB.CommandText := 'Use Master';    {连接master数据库}
    cmdBackDB.Execute;
    pbEnd.StepIt;
    {备份数据库}
    cmdBackDB.CommandText := 'Backup DataBase ' + TConfiger.GetKeyValue
      ('ServerConfig', 'DBName') + ' to disk = ' + Quotedstr(edtPath.Text)
      + ' with init';
    cmdBackDB.Execute;
    pbEnd.StepIt;
    {重连主数据库}
    cmdBackDB.CommandText := 'Use ' + TConfiger.GetKeyValue('ServerConfig',
      'DBName');
    cmdBackDB.Execute;
    pbEnd.StepIt;

    Application.MessageBox('备份数据库成功！', '系统提示', MB_OK + MB_ICONINFORMATION);
  except
    Application.MessageBox('备份数据库失败！', '系统提示', MB_OK + MB_ICONINFORMATION);
  end;
  try
    MainDM.conMain.Connected := True;
  except
    Application.MessageBox('无法连接到数据库！', '系统提示', MB_OK + MB_ICONINFORMATION);
    Application.Terminate;
  end;

  pbEnd.Visible := False;
  btnStart.Enabled := True;
end;

恢复数据库的代码为:

procedure TFrm_RestoreDB.btnRestoreDBClick(Sender: TObject);
var
  SQLText: string;
begin
  if edtPath.Text = '' then
  begin
    Application.MessageBox('请按浏览选择要恢复的数据库文件！', '系统提示',
      MB_OK + MB_ICONINFORMATION);
    Exit;
  end;
  if Application.MessageBox(PChar('确定要恢复数据库么？这将覆盖原有数据，建议先备份再执行此操作！'), '系统提示',
    MB_OKCANCEL + MB_ICONINFORMATION) = mrOk then
  begin
    pbEnd.Position := 0; { 进度条 }
    pbEnd.Visible := True;
    btnRestoreDB.Enabled := False;
    pbEnd.StepIt;
    try
      MainDM.conMain.Close; {关闭主连接}
      pbEnd.StepIt;

      cmdRestoreDB.Connection := MainDM.conMain;
      cmdRestoreDB.CommandText := 'Use Master';  {连接到master数据库}
      cmdRestoreDB.Execute;
      pbEnd.StepIt;

      { 获取排他访问权 }
      SQLText := Format(
        'select spid from master.dbo.sysprocesses where dbid = DB_ID(%s)',
        [QuotedStr(TConfiger.GetKeyValue('ServerConfig', 'DBName'))]);

      with MainDM.FindRecords(SQLText) do
        try
          First;
          while not Eof do
          begin
            cmdRestoreDB.CommandText := 'Kill ' + FieldByName('spid').AsString;
            cmdRestoreDB.Execute;
            Next;
          end;
        except
        end;
      pbEnd.StepIt;
      {恢复数据库}
      cmdRestoreDB.CommandText := 'Restore DataBase ' + TConfiger.GetKeyValue
        ('ServerConfig', 'DBName') + ' from disk = ' + QuotedStr(edtPath.Text)
        + ' with replace';
      cmdRestoreDB.Execute;
      pbEnd.StepIt;
      {重连主数据库}
      cmdRestoreDB.CommandText := 'Use ' + TConfiger.GetKeyValue
        ('ServerConfig', 'DBName');
      cmdRestoreDB.Execute;
      Application.MessageBox('恢复数据库成功！', '系统提示', MB_OK + MB_ICONINFORMATION);
    except
      Application.MessageBox('恢复数据库失败，请检查文件是否正确！', '系统提示',
        MB_OK + MB_ICONINFORMATION);
    end;
    try
      MainDM.conMain.Connected := True;
    except
      Application.MessageBox('无法连接到数据库！', '系统提示', MB_OK + MB_ICONINFORMATION);
      Application.Terminate;
    end;

    pbEnd.Visible := False;
    btnRestoreDB.Enabled := True;
  end;
end;

[lyhoo163]

可以把所有的SQL语句放在一起，一次执行。

[lyhoo163]

var SQL,FileName:string;
ADOQ:TADOQuery;
begin
ADOQ:=TADOQuery.Create(nil);
ADOQ.Connection:=DataModule1.ADOCon;
try
if SaveDialog1.Execute then
begin
FileName:=SaveDialog1.FileName;
if (Copy(FileName,Length(FileName)-3,4)<>'.BAK') or (Copy(FileName,Length(FileName)-3,4)<>'.bak') then
FileName:=FileName+'.BAK';
SQL:='Use Master Backup Database '+DatabaseName+' to Disk='''+ FileName+''' Use '+DatabaseName;
ADOQ.SQL.Text:=SQL;
ADOQ.ExecSQL;
MessageDlg('数据备份成功!',mtInformation,[mbOK],0);
end;
except
MessageDlg('数据备份失败!',mtInformation,[mbOK],0);
Exit;
end;

[lhs203203]

这样也可以 其实我分开还是为了仿真进度条的 ^_^

[CSIBM]

不错，一个存储过程就实现了