estimated time for restoring a database from 100 GB.bak file?

Question

wondering that there is a chance of estimating time of restoration from a full backup file(lets say 100Gb .bak file)

couldsome one giveme any hint?

---

-

Answer 1

Hi,

Is this purely theoretical? If not you should do test restores of your backup to make sure that it works and then you will also have a very reliable estimate of how long your recovery time will be during a restore. Otherwise as a estimate I always expect a restore from tape to take 1.5 - 2 times the amount of time it takes to do the backup however this is a real rough guess and is based on doing test restores.

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 2

If you are using  SQL 2005 and onwards you can use the sys.dm_exec_requests DMV to get estimates for some long running operations like this.

 SELECT percent_complete, estimated_completion_time, *

FROM sys.dm_exec_requests

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

use master
go
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(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA 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)
FROM sys.dm_exec_sql_text(sql_handle)))FROM sys.dm_exec_requests r 
WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

The above script is really helpful to find the time taken or estimated time for backup and restore.

Answer 4

If you are using sql 2000 -you can use with stats='you need to define here how much percentage completion'  (even you can use in other versions how ever in SQL server 2005+ onwards you have DMV-sys.dm_exec_requests  you can track estimation time along with the percentage completion of restoration).

Just fyi..... the DMV sys.dm_exec_requests is also shows other operation status of percentage as well--

percent_complete

real

Percentage of work completed for the following commands: ALTER INDEX REORGANIZE AUTO_SHRINK option with ALTER DATABASE BACKUP DATABASE DBCC CHECKDB DBCC CHECKFILEGROUP DBCC CHECKTABLE DBCC INDEXDEFRAG DBCC SHRINKDATABASE DBCC SHRINKFILE RECOVERY RESTORE DATABASE, ROLLBACK TDE ENCRYPTION Is not nullable.

---

Rama Udaya.K ramaudaya.blogspot.com

Answer 5

I have a .bak file that is 120gb in size. I do test restores quite reguarly and it takes around about an hour for a local restore. It varies on where bak is being restored to (local or network restore) also how many processors you have. Hope this helps :)