SQL Server Backup/Restore and Completion Time Estimation

Taking database backup is a compulsory task if your data is critical. SQL Server backup and restore operations can take from few seconds to few hours or some cases few days which most of DBAs or system administrators assume that SQL Server is crashed or hanged during backup or restore operation. Therefor they restart the operation! The other assume is that database backup file only keeps data till the time that backup command is executed, so I need to say ‘NO!’ instead SQL Server database backup consists of every single data records up to the backup completion time.
In this article I would like to explain the basic backup and restore operation internals.

Backup Internals

SQL Server backup process copies the whole database into the backup device, to accomplish this task, SQL Server performs a checkpoint and then keeps the last LSN value to make sure to minimize the transaction log backup. the next step is to read every single data pages and copy into the backup device and at the last check the last LSN value and makes a transaction log backup. SQL Server backup process never reads changed data page once the checkpoint operation is done.

backup internals

Restore Internals

SQL Server restore process copies all the data pages frm backup device into the database data files at the first stage, then it reads the backed up transaction log and begins the ‘Redo’ phase which commits every transactions that is committed after the backup checkpoint time, this stage is called ‘Roll Forward’ as well. The last step which consumes so much time and resources is called ‘Undo’ or ‘Roll Backward’, SQL Server roll backs every uncommitted transaction. Keep in mind that database is only available after Undo phase, except in Enterprise edition which is using ‘Lock Logging’ technique to rollback uncommitted transactions asynchronously.

Backup / Restore Completion Time Estimation

[codesyntax lang=”tsql”]

convert(nvarchar(255),db_name(database_id)) as [database],
case command
when 'BACKUP DATABASE' then 'Database Backup'
when 'RESTORE DATABASE' then 'Database Restore'
when 'RESTORE VERIFYON' then 'Database Restore Verification'
when 'RESTORE HEADERON' then 'Database Restore Verification - Header'
else 'LOG' end as [type],
start_time as [started],
dateadd(mi,estimated_completion_time/60000,getdate()) as [Est. Completion Time],
datediff(mi, start_time, (dateadd(mi,estimated_completion_time/60000,getdate()))) - wait_time/60000 as [Minutes Left],
datediff(mi, start_time, (dateadd(mi,estimated_completion_time/60000,getdate()))) as [Est. Wait Time],
convert(varchar(5),cast((percent_complete) as decimal (4,1))) as [% Complete],
getdate() as [Current Time]
from sys.dm_exec_requests



Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles


Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788


Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan