Recent

Author Topic: [SOLVED] Cannot perform a backup or restore operation within a transaction  (Read 11573 times)

ALAU2007

  • New member
  • *
  • Posts: 9
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?
« Last Edit: December 15, 2014, 08:51:33 am by ALAU2007 »

eny

  • Hero Member
  • *****
  • Posts: 1587
Re: Cannot perform a backup or restore operation within a transaction
« Reply #1 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.
All posts based on: Win10 (Win64); Lazarus 1.8.0 'stable' (#56594 win64) unless specified otherwise...

ALAU2007

  • New member
  • *
  • Posts: 9
Re: Cannot perform a backup or restore operation within a transaction
« Reply #2 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. 

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Cannot perform a backup or restore operation within a transaction
« Reply #3 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?
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Cannot perform a backup or restore operation within a transaction
« Reply #4 on: October 16, 2013, 12:54:31 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.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

ALAU2007

  • New member
  • *
  • Posts: 9
Re: Cannot perform a backup or restore operation within a transaction
« Reply #5 on: October 17, 2013, 03:58:32 am »
It works.  Thanks for all.
 :D

ALAU2007

  • New member
  • *
  • Posts: 9
Re: Cannot perform a backup or restore operation within a transaction [SOLVED]
« Reply #6 on: December 03, 2014, 10:06:14 am »
It works.  Thanks for all.
 :D

The comment of BigChimp is worked.
« Last Edit: December 15, 2014, 08:50:53 am by ALAU2007 »

zonafets

  • New member
  • *
  • Posts: 9
Re: Cannot perform a backup or restore operation within a transaction
« Reply #7 on: December 11, 2014, 01:17:42 pm »
Please set it as [SOLVED] to facilitate others to find solutions quickly.

Thanks