SQL Server Backup/Restore and Completion Time Estimation

The estimated reading time for this post is 2 minutes

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

SELECT
session_id,
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
WHERE command in ('BACKUP DATABASE','BACKUP LOG','RESTORE DATABASE','RESTORE VERIFYON','RESTORE HEADERON')

 

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz