Lazarus

Programming => Databases => Topic started by: ALAU2007 on October 15, 2013, 06:15:51 pm

Title: [SOLVED] Cannot perform a backup or restore operation within a transaction
Post by: ALAU2007 on October 15, 2013, 06:15:51 pm

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?

Title: Re: Cannot perform a backup or restore operation within a transaction
Post by: eny on October 15, 2013, 07:10:22 pm

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

Title: Re: Cannot perform a backup or restore operation within a transaction
Post by: ALAU2007 on October 16, 2013, 04:16:38 am

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. 

Title: Re: Cannot perform a backup or restore operation within a transaction
Post by: BigChimp on October 16, 2013, 08:09:25 am

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

Title: Re: Cannot perform a backup or restore operation within a transaction
Post by: BigChimp on October 16, 2013, 12:54:31 pm

Quote from: ALAU2007 on October 15, 2013, 06:15:51 pm

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.


Title: Re: Cannot perform a backup or restore operation within a transaction
Post by: ALAU2007 on October 17, 2013, 03:58:32 am

It works.  Thanks for all.
 :D

Title: Re: Cannot perform a backup or restore operation within a transaction [SOLVED]
Post by: ALAU2007 on December 03, 2014, 10:06:14 am

Quote from: ALAU2007 on October 17, 2013, 03:58:32 am

It works.  Thanks for all.
 :D

The comment of BigChimp is worked.

Title: Re: Cannot perform a backup or restore operation within a transaction
Post by: zonafets on December 11, 2014, 01:17:42 pm

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

Thanks