2

I am currently working on a database restore process, the progress of which is shown to the user in a progress dialog.

I have a code (in MFC), where restore query of a single database is prepared and executed. I get a progress for this process by using a query posted by Veldmuis in this thread:

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(6, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed MIN],
    CONVERT(NUMERIC(6, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA MIN],
    CONVERT(NUMERIC(6, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [ETA Hours],
    CONVERT(VARCHAR(100), 
               (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)
                FROM sys.dm_exec_sql_text(sql_handle)))
FROM 
    sys.dm_exec_requests r 
WHERE 
    command IN ('RESTORE DATABASE', 'BACKUP DATABASE')

and it works perfectly fine.

Right now in my program I would like to add the option to perform restore of differential backup which requires the restore of the full backup first. Therefore I modified my restore query so that full and differential backup are being made directly one after another. Here comes the problem, because when the query looks like:

RESTORE DATABASE full_backup_name FROM...
RESTORE DATABASE differential_backup_name FROM...

After the first backup is restored, the progress moves back.

I believe there is some easy way to modify the selecting query, but I am still quite a novice when it comes to SQL/SQL Server and I'm struggling to do it. My first thought was to close those restores inside a transaction and then get the progress for the command = 'transaction_name', but it turned out that it is not possible to perform backup/restore operations inside the transaction.

I hope someone could help me with finding the way I should follow to solve this problem (maybe there is some other way to set an alias for the whole operation of 2 restores). I know that as a last resort I can turn my query into two separate queries, but this is the thing I would like to avoid - it would take quite a lot of work to do it.

adampp93
  • 21
  • 1
  • 4
  • 1
    I am suspicious that this is not something that is easily remedied. The 2 operations have to run in series and cannot run in parallel. When the first select is run the second operation does not yet exist and so its run time cannot be measured. – alexherm Sep 04 '19 at 21:45
  • If you are using .NET you could set the STATS and consume the progress messages in the client application. – David Browne - Microsoft Sep 04 '19 at 22:22
  • Unfortunately I'm not. I forgot to mention it, but I'm using MFC(C++) – adampp93 Sep 05 '19 at 05:34

0 Answers0