[SOLVED] Cannot perform a backup or restore operation within a transaction

[ALAU2007]

I would like to backup the MSSQL database within my program.  I try to execute as:

  mssqlConnection1.ExecuteDirect( 'backup database Test to disk = N''C:\TEMP\TEST.bak'' with format, init');
  sqltransaction1.Commit;

The result is:

   Cannot perform a backup or restore operation within a transaction.

How can I backup the MSSQL database within program?

[eny]

One solution would be to put the backup statement in a stored procedure.
And then call that stored procedure.

[ALAU2007]

Thanks for your advice.

I create the procedure in DB as

  CREATE PROCEDURE BackupDB
  AS
  BEGIN
   SET NOCOUNT ON;
   exec( 'backup database Test to disk = N''C:\TEMP\TEST2.bak'' with format, init;' );
   insert into SysLog values ( convert( char(20), getdate(), 20 ) ) 
  END

and modify the pascal code to execute the procedure as

  mssqlConnection1.ExecuteDirect( 'BackupDB' );
  sqltransaction1.Commit;

The Syslog record is inserted but NO backup file generated.  Please comment. 

[BigChimp]

The obviuos question: I'm assuming that executing the command in e.g. SQL Managemnet studio works?

[BigChimp]

How can I backup the MSSQL database within program?

Code like this worked for me against MS SQL Server 2008R2 with FPC 2.6.2 as well as trunk - it's the autocommit setting that probably fixes it:

Code: [Select]

program mssqlbackup;
// Backs up database test.
 
{$mode objfpc}{$H+}
 
uses
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF}
  Classes, sysutils,
  sqldb,
  mssqlconn;
 
var
  Conn: TMSSQLConnection;
  Tran: TSQLTransaction;
begin
  Conn:=TMSSQLConnection.create(nil);
  Tran:=TSQLTransaction.create(nil);
  try
//adjust credentials
    Conn.HostName:='127.0.0.1';
    Conn.UserName:=''; //trusted authentication/SSPI
    Conn.Password:=''; //trusted authentication/SSPI
    Conn.DatabaseName:='master';
    Conn.Params.Add('AutoCommit=true');
    Conn.Transaction:=Tran;
    Conn.Open;
// adjust path   
    Conn.ExecuteDirect('backup database test to disk = N''C:\TEMP\TEST2.bak'' with format, init');
    Conn.Close;
  finally
    Tran.Free;
    Conn.Free;
  end;
  writeln('Program complete. Press a key to continue.');
  readln;
end.


[ALAU2007]

It works.  Thanks for all.
 

[ALAU2007]

It works.  Thanks for all.
 

The comment of BigChimp is worked.

[zonafets]

Please set it as [SOLVED] to facilitate others to find solutions quickly.

Thanks