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.
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.
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')