How long will it take to restore? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How long will it take to restore?

Author

Topic

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2007-11-07 : 16:50:13

Hey SQL experts.

How Long will it take to restore 460GB database and what are the precaution we should take care of

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-07 : 16:58:10

There is no way for us to estimate this for you as it depends on hardware, database file sizes, etc. Every environment is different.

Add the WITH STATS option to your RESTORE DATABASE command so that you can view status while it is running.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2007-11-07 : 17:00:40

thanks for reply.

backup took 22 hours but restore only took 6 hours. So i was wondering it was too fast. I wanna sure restore went fine.

thanks TARA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-07 : 17:05:59

I'd highly recommend a third party back tool so that your backups and restores are faster. We use SQL Litespeed. It reduces backup files sizes and backup times by about 75%. I don't recall the restore time, but it's still faster than the native restore.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-07 : 17:20:01

Echo the Litespeed option. It has saved a ton of space and time in backup/restore operations. With that size of a database..definitely worth considering all the options for a 3rd part tool.

Haywood
Posting Yak Master

221 Posts

Posted - 2007-11-07 : 17:22:43

You can retrieve times based on running restores or backup operations via:

[CODE]
SET NOCOUNT ON
GO

SELECT DB_NAME(database_id) AS [DBName]
, session_id AS [SPID]
, Command
, 'Estimated End Time' = CONVERT(varchar(26),Dateadd(ms,estimated_completion_time,Getdate()),100)
, 'Estimated Seconds To End' = CONVERT(decimal(9,2),(estimated_completion_time * .001))
, 'Estimated Minutes To End' = CONVERT(decimal(9,2),(estimated_completion_time * .001 / 60))
, 'Backup Start Time' = CONVERT(varchar(26),start_time,100)
, 'Percent Complete' = percent_complete

FROM sys.dm_exec_requests

WHERE command IN ('BACKUP DATABASE','RESTORE DATABASE')
GO
[/CODE]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2007-11-07 : 17:28:47

Sorry i didn't get it. Database is restoring in Norecovery mode right now.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-07 : 23:15:47

Still in restoring? How did you get 6 hours then?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources