approximate timespans for db backups?

Question

approximate timespans for db backups?

sqlguy-736318

Hall of Fame

Points: 3859
More actions
October 1, 2013 at 5:25 pm

#283344

Hello, I was wondering if you generally see a fairly consistent timespan for database backups? For example, I just backed up a 5700MB db and it took about 14 seconds. That's about 407MB/s.
Based on this metric, would it be reasonable to assume that a 57000MB db would take about 140 seconds to backup? Or do you find that backups become slower or faster at some point?
Also, do you tend to see fairly consistent backup times based on database size between different database instances, or do you find that there are db factors which have a significant effect on db backup time? Assume average CPU and RAM between db servers?

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

Depends on IO throughput and database size mostly. A backup to my external drive over USB 2 is not going to be as fast as a backup to an SSD.

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

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 2

Also depends on the amount of files you back your database up across.

KumarVelayutham SSCrazy Points: 2204 More actions · Answer 3

AS Gila info,Database size,IO throughput and also depends on backup destination (to tape,SAN or local disk)!

Regards,
Kumar

KumarVelayutham SSCrazy Points: 2204 More actions · Answer 4

AS Gila info,Database size,IO throughput and also depends on backup destination (to tape,SAN or local disk)!

Regards,
Kumar

adb2303 SSCertifiable Points: 7588 More actions · Answer 5

To add to other comments:

If the database you're backing up is spread over multiple volumes, you get a reader thread per volume. It reads into in memory buffer (the number of which can be specified by buffercount parameter). The size of the buffer is specified by maxtransfersize. If you're striping your backup over multiple files, you get a writer thread per destination file. Also, if you're using the backup compression option; that's heavily affected by the type of data in your database.

dan-572483 SSCoach Points: 15870 More actions · Answer 6

If you have SQL 2008 Enterprise or SQL 2008 R2 Standard/Enterprise, you can use backup compression and run backups in less than half the time (in my experience) as non-compressed backups.