In Delphi is there a way to backup and restore a database using FireDAC

Question

Is there a way to backup and restore a database using FireDAC?

I'm using Delphi 10.1 Berlin and connecting to MSSQL Server. Ideally it would backup all the interesting attributes of the database (records, indexes etc..), but a solution that just does a backup and restore of the data would be fine (I can recreate all the meta-data prior to restoring).

Answer 1

I confess I'm a bit puzzled by your q because the answer seems a bit too easy, namely use FireDAC's TDFConnection to execute SqlServer TransactSql scripts to back up and restore the database. I hope you don't regard that as cheating ;)

Example project code

type
  TForm1 = class(TForm)
    FDConnection1: TFDConnection;
    btnBackUp: TButton;
    btnRestore: TButton;
    FDMetaInfoQuery1: TFDMetaInfoQuery;
    FDPhysMSSQLDriverLink1: TFDPhysMSSQLDriverLink;
    DBGrid1: TDBGrid;  //  connected to DataSource1
    DataSource1: TDataSource;  // connected to FDMetaInfoQuery1
    Memo1: TMemo;
    procedure btnBackUpClick(Sender: TObject);
    procedure btnRestoreClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  [...]

const
  scBackUpPath = 'D:\MSSql2014\Backup\';
  scBackupExtn = '.Bak';

procedure TForm1.Log(const Msg : String);
begin
  Memo1.Lines.Add(Msg);
end;

function TForm1.DatabaseName: String;
begin
  Result := FDMetaInfoQuery1.FieldByName('Catalog_Name').AsString;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  FDMetaInfoQuery1.MetaInfoKind := mkCatalogs;  // gets list of databases
  FDConnection1.Connected := True;
end;

procedure TForm1.BackupDB(const DBName: String);
var
  FileName,
  Sql : String;
begin
  FileName := scBackUpPath + DBName + scBackUpExtn;
  Sql := 'backup database %s to disk = ''%s''';
  Sql := Format(Sql, [DBName, FileName]);
  Log('Backing up ' + DBName + ' using SQL: ' + Sql);
  try
    Screen.Cursor := crHourGlass;
    Update;
    FDConnection1.ExecSQL(Sql);
    Log('Done');
  finally
    Screen.Cursor := crDefault;
  end;
end;

procedure TForm1.btnBackUpClick(Sender: TObject);
begin
  BackUpDB(DatabaseName);
end;

procedure TForm1.btnRestoreClick(Sender: TObject);
begin
  RestoreDB(DatabaseName);
end;

procedure TForm1.RestoreDB(const DBName: String);
var
  FileName,
  Sql : String;
begin
  FileName := scBackUpPath + DBName + scBackUpExtn;
  if FileExists(FileName) then begin
    //  Note:  beware the 'with replace' in the following
    Sql := 'restore database %s from disk = ''%s'' with replace';
    Sql := Format(Sql, [DBName, FileName]);
    Log('Restoring ' + DBName + ' using SQL: ' + Sql);
    try
      Screen.Cursor := crHourGlass;
      Update;
      FDConnection1.ExecSQL(Sql);
      Log('Done');
    finally
      Screen.Cursor := crDefault;
    end;
  end
  else
    Log('Backup file ' + FileName + ' not found!');
end;

Obviously that's a bit light on error-checking, but i'm sure you'll get the idea.

Before it was phased out I'd use Delphi automation to SqlServer's Sql-DMO library to do this because it was easy to implement progress callbacks like "% completed".

I haven't managed to do anything useful yet with Sql_DOM's successor, SMO, from Delphi and these days I'd do this using a TADOConnection rather than FireDAC if only because there's less baggage involved in getting the list of databases and the ADO Errors collection provides an easy way of getting at any errors encountered.