[SOLVED] Show MSSQL database backup progress status
lazarus / 16-Databases

----------------------------------------------------------------------
ALAU2007 · 2014-12-15 08:09

When I back up the MSSQL database with the following:

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.

, it suspends my program until it finished.  I want to show status of
backup progress such as MSSQL script:

Code: [Select]SELECT
r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_compl
etion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed
Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA
Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS
[ETA Hours],
CONVERT(VARCHAR(1000),(SELECT
SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE
(r.statement_end_offset-r.statement_start_offset)/2 END) SQLScript
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE
DATABASE','BACKUP DATABASE')

How can I show the backup progress status via percentage or progress
bar?

Thanks

----------------------------------------------------------------------
tudi_x · 2014-12-15 12:28

I think you need a separate thread to invoke the backup process and
the main thread to handle also the update of the progress bar based on
the criteria you consider as progress.
The main thread will prevent the application to freeze if the checking
for progress is below a noticeable freezing threshold.

----------------------------------------------------------------------
ALAU2007 · 2014-12-16 02:08

Thanks for comment.

I have tried to adopt the multi-threat approach but it still pended
until backup finished.  BTW, I tried a simple way by calling backup
program and showing progress in main program, e.g. :

Code: [Select]procedure TForm1.Button1Click(Sender: TObject);  
var
  aProcess: TProcess;
begin
  aProcess:=TProcess.Create(nil);
  try
    aProcess.InheritHandles := false;
    aProcess.Options := [];
    aProcess.ShowWindow := swoShow;
    aProcess.CommandLine := 'Backup.exe';
    aProcess.Execute;
  finally
    aProcess.Free;
  end;     
  Timer1.Enabled:= true;   
end;

procedure TForm1.Timer1Timer(Sender: TObject);
begin
  SQLQuery1.Active := False;
  SQLQuery1.Active := true;
  ProgressBar1.Position := SQLQuery1.FieldByName('Percent
Complete').AsInteger;
  Label1.Caption := InttoStr( ProgressBar1.Position ) + '%';
  if ProgressBar1.Position >= 100 then begin
    Timer1.Enabled := False;
    SQLQuery1.Active := False;
    Label1.Caption := 'Done';
  end;
end;  

It can work but I prefer to use multi-thread to process within one
program.

Please advise.

----------------------------------------------------------------------
engkin · 2014-12-16 02:40

Quote from: ALAU2007 on December 16, 2014, 03:08:27 amI have tried to
adopt the multi-threat approach but it still pended until backup
finished. 
...
I prefer to use multi-thread to process within one program.
Share your thread code.

----------------------------------------------------------------------
ALAU2007 · 2014-12-19 09:13

I follow the multithreading example to implement the following code to
process

Code: [Select]Type
  TMyBackupThread = class(TThread)
  private
  protected
    procedure Execute; override;
  public
    constructor Create(CreateSuspended: boolean);
  end;
   
.....
   
procedure TForm1.Button4Click(Sender: TObject);
var
  MyBackupThread : TMyBackupThread;
begin
  MyBackupThread := TMyBackupThread.Create(True); // With the True
parameter it doesn't start automatically
  if Assigned(MyBackupThread.FatalException) then
    raise MyBackupThread.FatalException;
  MyBackupThread.Execute;
  MyBackupThread.Terminate;

end;

procedure TMyBackupThread.Execute;
var Conn: TMSSQLConnection;
    Tran: TSQLTransaction;
begin
  Conn := TMSSQLConnection.Create(nil);
  Tran := TSQLTransaction.Create(nil);
  try
    Conn.HostName := '127.0.0.1\SQLExpress';
    Conn.UserName := 'sa';
    Conn.Password := 'XXXX';
    Conn.DatabaseName := 'MyDatabase';
    Conn.Params.Add( 'AutoCommit=true' );
    Conn.Transaction := Tran;
    Conn.Open;
    Conn.ExecuteDirect( 'backup database MyDatabase to disk =
N''c:\temp\test_backup.bak''; ' );
    Conn.Close;
  finally
    Tran.Free;
    Conn.Free;
    ShowMessage( 'End' );
  end;

end;

constructor TMyBackupThread.Create(CreateSuspended: boolean);
begin
  FreeOnTerminate := True;
  inherited Create(CreateSuspended);
end;  


I have show the clock in the form heading like the example.  When the
backup is performed, the clock is also suspended until finishing.

Please comment.  Thanks!

----------------------------------------------------------------------
engkin · 2014-12-20 03:50

Quote from: ALAU2007 on December 19, 2014, 10:13:03 amI follow the
multithreading example to implement the following

I don't know which example you are referring to.

When you want a thread to run some code, you do *not* call Execute
directly, instead call Start and leave the thread mechanics to handle
your code inside Execute.

Change MyBackupThread.Execute; in your code to MyBackupThread.Start;
and try again. That should resolve blocking the main thread while the
backup thread is working. You still need to query the server about the
progess of this command. Something like what Remus Rusanu is
suggesting in this Stack Overflow post

----------------------------------------------------------------------
ALAU2007 · 2014-12-21 06:47

My program is depended on multithreadingexample1.lpr

I apply the checking SQL script in the SQLQuery1 as the following:

Code: [Select]{
 *********************************************************************
******
 *                                                                   
    *
 *   This source is free software; you can redistribute it and/or
modify   *
 *   it under the terms of the GNU General Public License as published
by  *
 *   the Free Software Foundation; either version 2 of the License,
or     *
 *   (at your option) any later version.                             
    *
 *                                                                   
    *
 *   This code is distributed in the hope that it will be useful, but 
    *
 *   WITHOUT ANY WARRANTY; without even the implied warranty of      
    *
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU     *
 *   General Public License for more details.                        
    *
 *                                                                   
    *
 *   A copy of the GNU General Public License is available on the
World    *
 *   Wide Web at <http://www.gnu.org/copyleft/gpl.html>. You can also 
    *
 *   obtain it by writing to the Free Software Foundation,           
    *
 *   Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.  
    *
 *                                                                   
    *
 *********************************************************************
******

  Abstract:
    Demo to show, how to start a thread and how synchronize with the
main
    thread.
    Important: The cthread unint must be added to the uses section of
the .lpr
               file. See multithreadingexample1.lpr.
}
unit MainUnit;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, Forms, Controls, Graphics, Dialogs, StdCtrls,
ComCtrls,
  sqldb, mssqlconn;

type

  { TMyBackupThread }

  TMyBackupThread = class(TThread)
  private
  protected
    procedure Execute; override;
  public
    constructor Create(CreateSuspended: boolean);
  end;


  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Label1: TLabel;
    MSSQLConnection1: TMSSQLConnection;
    ProgressBar1: TProgressBar;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure Button1Click(Sender: TObject);
  private
  public
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
var
  MyBackupThread : TMyBackupThread;
  i : integer;
begin
  MyBackupThread := TMyBackupThread.Create(True); // With the True
parameter it doesn't start automatically
  if Assigned(MyBackupThread.FatalException) then
    raise MyBackupThread.FatalException;

  // Here the code initialises anything required before the threads
starts executing

  //Test_Dummy
  MyBackupThread.Start;

  Label1.Caption := 'Start';
  SQLQuery1.Active := False;
  SQLQuery1.Active := true;
  i := 0;
  while ( ProgressBar1.Position < 100 ) and ( i <= 1000 ) do begin
    SQLQuery1.ExecSQL;
    ProgressBar1.Position := SQLQuery1.FieldByName('Percent
Complete').AsInteger;
    i := i + 1;
    Label1.Caption := InttoStr( i ) + ' XXX ' + InttoStr(
ProgressBar1.Position ) + '%';
  end;
  SQLQuery1.Active := False;
  Label1.Caption := 'Done';
end;

{TBackupThread}

procedure TMyBackupThread.Execute;
var
  Conn: TMSSQLConnection;
  Tran: TSQLTransaction;
begin
  Conn:=TMSSQLConnection.create(nil);
  Tran:=TSQLTransaction.create(nil);
  try
    Conn.HostName:='127.0.0.1\sqlexpress';
    Conn.UserName:=''; //trusted authentication/SSPI
    Conn.Password:=''; //trusted authentication/SSPI
    Conn.DatabaseName:='Test_Dummy';
    Conn.Params.Add('AutoCommit=true');
    Conn.Transaction:=Tran;
    Conn.Open;
    Conn.ExecuteDirect('backup database Test_Dummy to disk =
N''C:\TEMP\TEST_Dummy.bak'' with format, init');
    Conn.Close;
  finally
    Tran.Free;
    Conn.Free;
  end;
end;

constructor TMyBackupThread.Create(CreateSuspended: boolean);
begin
  FreeOnTerminate := True;
  inherited Create(CreateSuspended);
end;

end.
     

For MyBackupThread.Start, it gets no response.  If I change it to
MyBackupThread.Execute, it worked. But suspended
until finished.


Thanks for your comment.

----------------------------------------------------------------------
engkin · 2014-12-21 14:58

Quote from: ALAU2007 on December 21, 2014, 07:47:26 amMy program is
depended on multithreadingexample1.lpr
Thanks, now I see which example you were referring to. As you can see
it does not use Execute directly.

Quote from: ALAU2007 on December 21, 2014, 07:47:26 amFor
MyBackupThread.Start, it gets no response. 
What do you mean by it gets no response, can you explain more? What do
you see on Label1?

Quote from: ALAU2007 on December 21, 2014, 07:47:26 amIf I change it
to MyBackupThread.Execute, it worked. But suspended
until finished.
Because you simply used the thread to hold a procedure, and called the
procedure directly. You did not use it as a thread.

----------------------------------------------------------------------
ALAU2007 · 2014-12-22 07:58

Thanks for all.  

The following codes can be worked.

Code: [Select]{
 *********************************************************************
******
 *                                                                   
    *
 *   This source is free software; you can redistribute it and/or
modify   *
 *   it under the terms of the GNU General Public License as published
by  *
 *   the Free Software Foundation; either version 2 of the License,
or     *
 *   (at your option) any later version.                             
    *
 *                                                                   
    *
 *   This code is distributed in the hope that it will be useful, but 
    *
 *   WITHOUT ANY WARRANTY; without even the implied warranty of      
    *
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU     *
 *   General Public License for more details.                        
    *
 *                                                                   
    *
 *   A copy of the GNU General Public License is available on the
World    *
 *   Wide Web at <http://www.gnu.org/copyleft/gpl.html>. You can also 
    *
 *   obtain it by writing to the Free Software Foundation,           
    *
 *   Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.  
    *
 *                                                                   
    *
 *********************************************************************
******

  Abstract:
    Demo to show, how to start a thread and how synchronize with the
main
    thread.
    Important: The cthread unint must be added to the uses section of
the .lpr
               file. See multithreadingexample1.lpr.
}
unit MainUnit;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, Forms, Controls, Graphics, Dialogs, StdCtrls,
ComCtrls,
  sqldb, mssqlconn;

type

  { TMyBackupThread }

  TMyBackupThread = class(TThread)
  private
    fStatusText: string;
    procedure ShowStatus;
  protected
    procedure Execute; override;
  public
    constructor Create(CreateSuspended: boolean);
  end;

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Label1: TLabel;
    MSSQLConnection1: TMSSQLConnection;
    ProgressBar1: TProgressBar;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure Button1Click(Sender: TObject);
  private
  public
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
var
  MyBackupThread : TMyBackupThread;
begin

  MyBackupThread := TMyBackupThread.Create(True); // With the True
parameter it doesn't start automatically
  if Assigned(MyBackupThread.FatalException) then
    raise MyBackupThread.FatalException;

  // Here the code initialises anything required before the threads
starts executing

  //Test_Dummy
  MyBackupThread.Start;

  Label1.Caption := 'Start';
  SQLQuery1.Active := False;
  while ( ProgressBar1.Position < 100 ) do begin
    SQLQuery1.Active := true;
    SQLQuery1.ExecSQL;
    ProgressBar1.Position := SQLQuery1.FieldByName('Percent
Complete').AsInteger;
    Label1.Caption := FormatFloat( '##0%',
SQLQuery1.FieldByName('Percent Complete').AsInteger )
          + '%, Estimated completion Time ' + FormatFloat( '#0.00',
SQLQuery1.FieldByName('ETA Min').AsFloat ) + ' min.';
    Application.ProcessMessages;
    SQLQuery1.Active := False;
  end;
end;

{TBackupThread}

procedure TMyBackupThread.ShowStatus;
// this method is only called by Synchronize(@ShowStatus) and
therefore
// executed by the main thread
// The main thread can access GUI elements, for example Form1.Caption.
begin
  Form1.Label1.Caption := fStatusText;
end;

procedure TMyBackupThread.Execute;
var
  Conn: TMSSQLConnection;
  Tran: TSQLTransaction;
begin
  fStatusText := 'Backup Starting ...';
  Synchronize(@Showstatus);  //If I remark this, it causes "access
violation" error
  Conn:=TMSSQLConnection.create(nil);
  Tran:=TSQLTransaction.create(nil);
  try
    Conn.HostName:='127.0.0.1\sqlexpress';
    Conn.UserName:=''; //trusted authentication/SSPI
    Conn.Password:=''; //trusted authentication/SSPI
    Conn.DatabaseName:='Test_Dummy';
    Conn.Params.Add('AutoCommit=true');
    Conn.Transaction:=Tran;
    Conn.Open;
    Conn.ExecuteDirect('backup database Test_Dummy to disk =
N''C:\TEMP\TEST_Dummy.bak'' with format, init');
    Conn.Close;
    fStatusText := 'Backup Completed';
    Synchronize(@Showstatus);
  finally
    Tran.Free;
    Conn.Free;
  end;
end;

constructor TMyBackupThread.Create(CreateSuspended: boolean);
begin
  FreeOnTerminate := True;
  inherited Create(CreateSuspended);
end;

end.


One more question.  If I remark the 'Synchronize(@Showstatus);' 
statement in program, it causes 'Access Violation'.

Why?

----------------------------------------------------------------------
nomorelogic · 2014-12-24 10:33

hi

I've used your code (linux x64), all goes well.

Commenting line:
Code: [Select]Synchronize(@Showstatus);
I receive no error, as I expected.