Whats the approximate time to restore 570 GB Backup?

Question

Whats the approximate time to restore 570 GB Backup?

jitendra.padhiyar

SSCertifiable

Points: 5824
More actions
September 11, 2015 at 5:43 am

#311534

Hello Masters,
Our clients wanted to migrate one database from Development Machine to Production. The database size is of 570 GB. Client wanted Approximate time to be used to restore this huge database. I am not 100% sure how much time it will require to restore.
According to my knowledge, 1 TB file would take 8-9 hours so 570 GB would take around 6-7 hours. But not sure.
Is there any way to know "how much time it will take to restore?" Any formula for calculation?
Thanks in advance.
Jitendra Padhiyar

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

It depends on the speed of your IO on the system where you're going to restore the database from backup.

About a year ago, we got a new SAN. The backup performance was okay with the old SAN. I took down some times on a few of our full backups and did some restores so I could check performance on the new SAN with concrete data. The new SAN had times 60% lower than the old one. That performance has held steady for a while now.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

jitendra.padhiyar SSCertifiable Points: 5824 More actions · Answer 2

But there could be something through which we can have approximate time value ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Sure. Take a backup of something smaller on the platform you'll be taking the backup on. Time it. Restore it on the environment you'll be restoring to. Time it. Extrapolate the times.

Backup and restore times are mostly a result of the IO throughput of the source and destination. Hence there's no easy formula, because it's dependent on your hardware and configuration.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jitendra.padhiyar SSCertifiable Points: 5824 More actions · Answer 4

jitendra.padhiyar

SSCertifiable

Points: 5824

September 11, 2015 at 6:25 am

#1825953

Thanks GilaMonster!

I will try this strategy. 🙂

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Thanks, Gail. I step away to go meet with someone and miss so much.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 6

Not sure if this will help or not... worth trying..

try backing up to multiple files... 3...4... instead of 1 file.

Also, if you are SQL2008R2 or greater use the COMPRESS option for the backup. Not only does it make the backup file smaller it does run faster.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Markus (9/11/2015)
Not sure if this will help or not... worth trying..
try backing up to multiple files... 3...4... instead of 1 file.
Also, if you are SQL2008R2 or greater use the COMPRESS option for the backup. Not only does it make the backup file smaller it does run faster.

I think backup compression is SQL 2008 or later; I don't think you need R2.

I'm certain that it requires Enterprise Edition. I know this because I'm on Standard Edition.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 8

Ed Wagner (9/11/2015)
Markus (9/11/2015)
Not sure if this will help or not... worth trying..
try backing up to multiple files... 3...4... instead of 1 file.
Also, if you are SQL2008R2 or greater use the COMPRESS option for the backup. Not only does it make the backup file smaller it does run faster.
I think backup compression is SQL 2008 or later; I don't think you need R2.
I'm certain that it requires Enterprise Edition. I know this because I'm on Standard Edition.

Backup compression only requires Enterprise edition in 2008. Starting in 2008 R2, you can compress backups in standard as well. From https://msdn.microsoft.com/en-us/library/bb964719(v=sql.105).aspx:

Backup compression was introduced in SQL Server 2008 Enterprise. Beginning in SQL Server 2008 R2, backup compression is supported by SQL Server 2008 R2 Standard and all higher editions. Every edition of SQL Server 2008 and later can restore a compressed backup.

Cheers!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

Jacob Wilkins (9/11/2015)
Ed Wagner (9/11/2015)
Markus (9/11/2015)
Not sure if this will help or not... worth trying..
try backing up to multiple files... 3...4... instead of 1 file.
Also, if you are SQL2008R2 or greater use the COMPRESS option for the backup. Not only does it make the backup file smaller it does run faster.
I think backup compression is SQL 2008 or later; I don't think you need R2.
I'm certain that it requires Enterprise Edition. I know this because I'm on Standard Edition.
Backup compression only requires Enterprise edition in 2008. Starting in 2008 R2, you can compress backups in standard as well. From https://msdn.microsoft.com/en-us/library/bb964719(v=sql.105).aspx:
Backup compression was introduced in SQL Server 2008 Enterprise. Beginning in SQL Server 2008 R2, backup compression is supported by SQL Server 2008 R2 Standard and all higher editions. Every edition of SQL Server 2008 and later can restore a compressed backup.
Cheers!

Ahhh....Thank you. I learned something new today. I'll look into it when we do our next installation, which will hopefully be soon.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 10

Ed Wagner (9/11/2015)

Jacob Wilkins (9/11/2015)
I'll look into it when we do our next installation, which will hopefully be soon.

We turned on COMPRESS as the default (when we upgraded to a version that allowed it in the Cheap-Charlie non-enterprise version). I did some tests and reckon we got about a 40% improvement (reduction) in backup time (and an 80% reduction in Filesize). Can't find the figure, but I think we got 40% improvement in RESTORE time too - which is good for Disaster Recovery and minimising schedule downtime during a migration to new server.

vedau SSC Veteran Points: 299 More actions · Answer 11

Make sure you have Instant File Initialization enabled too. It will not zeroing out the data file so the restore time will be much faster. Here is the link on how and why to enable it.

http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx

Cheers!

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 12

Markus (9/11/2015)
Not sure if this will help or not... worth trying..
try backing up to multiple files... 3...4... instead of 1 file.
Also, if you are SQL2008R2 or greater use the COMPRESS option for the backup. Not only does it make the backup file smaller it does run faster.

Just be careful on the multiple file thing. If the multiple files all involve the same read/write heads, it can actually slow things down quite a bit.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 13

vedau (9/15/2015)
Make sure you have Instant File Initialization enabled too. It will not zeroing out the data file so the restore time will be much faster. Here is the link on how and why to enable it.
http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx
Cheers!

Remember that won't necessarily help you with restores that have unnecessarily large log files because they need to be "formatted" as VLFs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Jeff Moden (9/15/2015)
Remember that won't necessarily help you with restores that have unnecessarily large log files because they need to be "formatted" as VLFs.

Not so much formatted. Log files must be zeroed out, regardless of the setting of instant init.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass