Recent

Author Topic: [SOLVED] Show MSSQL database backup progress status  (Read 7459 times)

ALAU2007

  • New member
  • *
  • Posts: 9
[SOLVED] Show MSSQL database backup progress status
« on: December 15, 2014, 09:09:58 am »
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_completion_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

« Last Edit: December 29, 2014, 09:59:53 am by ALAU2007 »

tudi_x

  • Hero Member
  • *****
  • Posts: 538
Re: Show MSSQL database backup progress status
« Reply #1 on: December 15, 2014, 01:28:42 pm »
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.
Lazarus 2.0.2 64b on Debian LXDE 10

ALAU2007

  • New member
  • *
  • Posts: 9
Re: Show MSSQL database backup progress status
« Reply #2 on: December 16, 2014, 03:08:27 am »
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.

« Last Edit: December 16, 2014, 03:12:57 am by ALAU2007 »

engkin

  • Hero Member
  • *****
  • Posts: 2513
Re: Show MSSQL database backup progress status
« Reply #3 on: December 16, 2014, 03:40:39 am »
I 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

  • New member
  • *
  • Posts: 9
Re: Show MSSQL database backup progress status
« Reply #4 on: December 19, 2014, 10:13:03 am »
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

  • Hero Member
  • *****
  • Posts: 2513
Re: Show MSSQL database backup progress status
« Reply #5 on: December 20, 2014, 04:50:52 am »
I 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

  • New member
  • *
  • Posts: 9
Re: Show MSSQL database backup progress status
« Reply #6 on: December 21, 2014, 07:47:26 am »
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

  • Hero Member
  • *****
  • Posts: 2513
Re: Show MSSQL database backup progress status
« Reply #7 on: December 21, 2014, 03:58:02 pm »
My 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.

For 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?

If 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

  • New member
  • *
  • Posts: 9
Re: Show MSSQL database backup progress status
« Reply #8 on: December 22, 2014, 08:58:37 am »
Thanks for all.   :D

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

  • Full Member
  • ***
  • Posts: 129
Re: Show MSSQL database backup progress status
« Reply #9 on: December 24, 2014, 11:33:51 am »
hi

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

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

 

TinyPortal © 2005-2018