[SOLVED] Cannot perform a backup or restore operation within a
transaction
lazarus / 16-Databases

----------------------------------------------------------------------
ALAU2007 · 2013-10-15 16:15

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 · 2013-10-15 17:10

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

----------------------------------------------------------------------
ALAU2007 · 2013-10-16 02:16

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 · 2013-10-16 06:09

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

----------------------------------------------------------------------
BigChimp · 2013-10-16 10:54

Quote from: ALAU2007 on October 15, 2013, 06:15:51 pmHow 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 · 2013-10-17 01:58

It works.  Thanks for all.

----------------------------------------------------------------------
ALAU2007 · 2014-12-03 09:06

Quote from: ALAU2007 on October 17, 2013, 03:58:32 amIt works.  Thanks
for all.


The comment of BigChimp is worked.

----------------------------------------------------------------------
zonafets · 2014-12-11 12:17

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

Thanks