Delphi Firedac backup Firebird database to local file

Question

Is it possible to use TFDFBNBackup and TFDFBNRestore for creating and restoring backups from/to a remote server from local files?

I know that this can be done with the local service manager command line tool like gbak also allows this, but I do not want to use these tools in my new Firemonkey application (Windows, OSX, Linux). I want to compile the functionality completely into my application and I only will have access to the server on a Firebird connection basis, no file share.

Answer 1

Thanks to Arioch's suggestion I could solve it and it works well. I used gbak service as it compresses the backup file. Should work with the nbackup flavour as well.

Below please find some example code without any error handling as proof of concept. As Backup only makes sense if it is absolutely reliable a sophisticated error detection and handling is neccessary when implementing this concept for production purposes.

Also, one has to modify firebird.conf on the server to allow external file access in the folder where the database(s) reside. I created backups of some databases in Windows and a binary compare of the files transferred to the local machine.

In the example I feed a label and a progress bar. The backup component should be set to verbose to display the progress although this slows down the backup on the server I prefer being able to give feedback to the user.

procedure TForm1.Button1Click(Sender: TObject);
var
  count: int64;
  fs: TFileStream;
  x: integer;

  procedure dropBackupTable;
  begin
    with FDQuery do
    begin
      sql.text := 'execute block as ' + 'begin ' +
        'if (exists(select 1 from rdb$relations where rdb$relation_name=''BACKUP'')) then ' +
        'execute statement ''drop table backup'';' + 'end';
      execute;
    end;
  end;

begin

  lbl.text := 'Online backup on server...';
  dropBackupTable;
  pb.Value := 2;
  pb.Max := 2000;
  with FDIbBackup do
  begin
    host := '192.168.2.14';
    database := 'r:\databases\office.fdb';
    port := 1216;
    UserName := 'SYSDBA';
    Password := '???????';
    BackupFiles.Clear;
    BackupFiles.add('r:\databases\back.fbk');
    Backup;
  end;

  lbl.text := 'Copying backup file...';

  with FDQuery do
  begin
    sql.text := 'create table backup external ''r:\databases\back.fbk'' (x integer)';
    execute;
    sql.text := 'select count(*) from backup';
    open;
    count := fields[0].AsInteger;
    close;
    pb.Max := count div 1024;
    pb.Value := 0;
    sql.text := 'select * from backup';
    open;
    fs := TFileStream.create('d:\temp\local.fbk', fmCreate, (fmShareDenyRead or fmShareDenyNone));
    count := 0;
    while not eof do
    begin
      inc(count);
      x := fields[0].AsInteger;
      fs.write(x, sizeOf(x));
      if count > 1023 then
      begin
        pb.Value := pb.Value + 1;
        application.processmessages;
        count := 0;
      end;
      next;
    end;
    close;
    fs.free;
    pb.Value := 0;
  end;

  dropBackupTable;

  lbl.text := 'Ready.';
end;

procedure TForm1.FBBackProgress(ASender: TFDPhysDriverService; const AMessage: string);
begin
  if pb.Value = pb.Max then
    pb.Value := 2
  else
    pb.Value := pb.Value + 1;
  application.processmessages;
end;