Database backup and restore time will depend on server and memory allocated.

Example 1

Server: 2.10 GHz CPU, 4.0 GB Memory

File SizeTime to BackupTime to Restore
0.5 GB10 seconds
2 GB40 seconds
35 GB7:50 minutes for Full
283 seconds (4.7 minutes)
6:37 minutes for Full
81 GB13:54 minutes for Transaction Log 8:00 minutes for Transaction Log
201 GB19:20 minutes for Full839 seconds (14 minutes)
35:24 minutes for Full
68 GB13:35 minutes for Transaction Log9:10 minutes for Transaction Log
88 GB6:21 minutes for Transaction Log

Example 2

Server: 2.10 GHz CPU, 32 GB Memory

File SizeTime to BackupTime to Restore
140 GB1:26:00 (1 hour, 26 minutes)

Example 3

Server: 2.30 GHz CPU, 64 GB Memory

Note:

Make sure to shrink the transaction log file before backup the backups.

File SizeDatabase SizeTime to BackupTime to Restore
101 GB383 GB2:20 hours
432 GB720 GB1:45 hours
600 GB600 GB2 hours

Estimate Restore Database Time

SELECT r.session_id AS [Session_Id]
	,r.command AS [command]
	,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [% Complete]
	,GETDATE() AS [Current Time]
	,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [Estimated Completion Time]
	,CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
	,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [Estimated Min]
	,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [Estimated 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) 'Statement text'
			FROM sys.dm_exec_sql_text(sql_handle)
			))
FROM sys.dm_exec_requests r
WHERE command like 'RESTORE%'
or	command like 'BACKUP%'
Last modified: March 19, 2022

Author

Comments

Write a Reply or Comment