What's Your Backup Speed?

Question

Post reply

What's Your Backup Speed?

Steve Jones - SSC Editor

SSC Guru

Points: 734449
More actions
November 3, 2016 at 9:52 pm

#400987

Comments posted to this topic are about the item What's Your Backup Speed?

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

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

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

Steve Jones - SSC Editor (11/3/2016)
I think it's one of the most solid subsystems in SQL Server

I should jolly well think so!!!

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Ivan Argentinski SSC Enthusiast Points: 111 More actions · Answer 2

1.4 TB database, 52 mins backup time.

So, 1 TB would be like 37 mins. Approximately 27 GB/min, 450 MB/s.

This is with Compression = On.

Database is on RAID 6, 10 SSDs. Backup is on 8 HDDs, don't remember the RAID level.

dave.farmer SSCrazy Points: 2431 More actions · Answer 3

165GB in 10 min 24 sec, which would mean just over an hour for 1TB (63 minutes).

DB files on SAN disk, SQL 2008 standard, Red Gate SQL Backup with level 1 compression (produces 21.5GB file at 87% compression).

441GB in anything from 18 to 25 minutes. 18 minutes would mean 1TB in under 41 minutes.

Again DB files on SAN, Red Gate SQL Backup with level 1 compression (produces 13GB file at 97% compression) but this time we're running SQL 2012 Enterprise.

rob_dalzell Old Hand Points: 315 More actions · Answer 4

20-mins (190GB in 221s @ 880MB/s - my Client's most important database)

Windows Server 2008 R2 Enterprise x64 (VM 4vCPU 14GB)

SQL Server 2008 R2 Enterprise x64

Native backups via Ola Hallengren

Compression

Checksum

8-stripes

BufferCount 64

Blocksize 65536

Files on HPE 3PAR StoreServ 8200 (16x 3.84TB cMLC SSD SFF RAID5 (3+1))

Backup across network to HPE StoreEasy 1650 NAS server (8x 4TB 7.2K SAS RAID6)

10 Gbit/s Ethernet (no jumbo frames or dedicated backup network)

ken.trock SSCertifiable Points: 5147 More actions · Answer 5

2.4TB in just over 2 1/2 hours, or 1TB in just over an hour. This is for ~40DBs in total, compression ON. SQL 2012 using native backups.

Ken

bklein2 Ten Centuries Points: 1077 More actions · Answer 6

DB Size = 1.1TB

Backup Duration = 2Hrs 26Min

We use EMC's Avamar backup client

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

2.3 TB in an hour and a couple of minutes. On a good night, it takes about 56 minutes. And that's to NAS storage, which is a huge "safety of the data" bonus.

We have a 10GB pipe between the server and the NAS storage. I don't know the configuration of that storage since I'm very fortunate to have an infrastructure team that does an incredible job. Of course, we're also using compression so the throughput isn't actually 2.3GB per hour but the net result is a 2.3TB box does its full backups in about an hour. That also says a bit about the machines they built for the company.

we're also doing offsite DR to another box using "Nimble". Again, I don't personally know much about the configuration but it, too, does and awesome job and, even with the huge overnight imports and updates we do, it keeps up very nicely.

--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)

djackson 22568 SSChampion Points: 11733 More actions · Answer 8

This is not a good example for people to use to estimate their performance, but is a good thing to read to understand how badly things can go.

We have a database from Intersystems, they provide a product named Cache. According to them it is not an RDBMS. It does store data in multiple files, and our system uses multiple mount points on an RHEL 7 system. We recently restored production to a test environment. The servers are almost identical, the differences do not affect performance in any measurable way.

Restoring 700GB took 27 hours!

Yes, we know there are issues. We are working on that.

This restore was done from a Commvault tape backup system that utilizes disks to increase backup and restore speed. The disks are later written to tape. It is a well designed system. I don't want to get into what caused the delay as it would sound too much like pointing fingers, but what is important is that even though we have a fine network and backup system and the servers are outstanding, we are not able to restore in a timely manner.

How did we find out? We tested.

Dave

Patrick Cahill SSCertifiable Points: 6360 More actions · Answer 9

826 GB

Average backup time 14 Min 7 Seconds

Compression is turned on

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 10

this is averages over a few months:

average database size was 186 GB, on Compellent SAN

average backup time (using TSQL commands, with compression) to external Drobo disk array was 20 minutes

average backup time (using CommVault) to tape was 79 minutes

so extrapolating for 1 TB would be:

115 minutes to Drobo disk

433 minutes to CommVault tape (ouch)

glad we're discontinuing CommVault backup of databases directly to tape!

David.Poole SSC Guru Points: 75898 More actions · Answer 11

David.Poole

SSC Guru

Points: 75898

November 4, 2016 at 9:46 am

#1910642

How about the restore speed?

LinkedIn Profile

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

Nightly restore of 2 main production databases:

DB1: 515GB, 13.35% free space, 10.0GB Log file

DB2: 76GB, 36.69% free space, 33.5GB Log file (yeah... I know)

Total time to restore including time to build the log file... 17:34 to 18:56 (mm:ss)

--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)

MadAdmin SSChampion Points: 11261 More actions · Answer 13

Backup times were before vs after optimisation for one of my databases.

Native sql default options

BACKUP DATABASE successfully processed 4098542 pages in 3049.584 seconds (10.499 MB/sec)

Native sql Buffercount=500, maxtransfer = 1MB

BACKUP DATABASE successfully processed 4098705 pages in 110.046 seconds (290.979 MB/sec)

Redgate - SQLBackup

BACKUP DATABASE successfully processed 4098225 pages in 513.281 seconds (62.377 MB/sec).

SQLBackup did not change times when I passed in different buffercount and maxtransfersize.

Native sql

Buffercount = 8

RESTORE DATABASE successfully processed 4780071 pages in 140.001 seconds (266.743 MB/sec).

Buffercount = 16

RESTORE DATABASE successfully processed 4780071 pages in 137.478 seconds (271.638 MB/sec).

Buffercount = 32

RESTORE DATABASE successfully processed 4780071 pages in 137.080 seconds (272.427 MB/sec).

Buffercount = 64

RESTORE DATABASE successfully processed 4780071 pages in 145.945 seconds (255.879 MB/sec).

Buffercount = 512

RESTORE DATABASE successfully processed 4780071 pages in 147.236 seconds (253.635 MB/sec).

Page count is different as I did the backups optimisation first then did the restore optimisation weeks later, which had very little to do witht he buffercount and a lot to do with rightsizing an unnecessarily bloated log file.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Jeff_C Old Hand Points: 344 More actions · Answer 14

We recently had to perform some backup and restore tests in order to estimate the impact of compressed vs non compressed backups and the effect they would have on our dedupe backup tape infrastructure. Times for the backups and restore are shown below. The backups are dumped to a san (XtremeIo) mounted local volume. The restores are performed across a wan link via unc path to a non-prod server. These are from a SQL2012 Enterprise Server running 16 cores.

Type BackupSizeGBOnDiskSizeGBStripesDurationMB/minSystemStateBackupNumber

Compressed1,404 302 1 78 18,432BatchRunning1

Compressed1,390 294 1 103 13,818BatchRunning2

Compressed1,479 313 1 90 16,830BatchRunning3

Compressed1,434 303 1 88 16,687BatchRunning4

UnCompressed1,479 1,479 1 240 6,309LowLoad 5

Compressed1,451 307 1 94 15,808BatchRunning6

UnCompressed1,489 1,489 1 84 18,151LowLoad 7

UnCompressed1,532 1,532 1 87 18,032MediumLoad8

UnCompressed1,580 1,580 1 146 11,082LowLoad 9

UnCompressed1,575 1,575 1 84 19,200LowLoad 10

UnCompressed1,579 1,579 1 91 17,768LowLoad 11

UnCompressed1,584 1,584 1 87 18,644LowLoad 12

UnCompressed1,594 1,594 16 41 39,811LowLoad 13

UnCompressed1,463 1,463 16 35 42,803LowLoad 14

UnCompressed1,467 1,467 16 37 40,600LowLoad 15

UnCompressed1,508 1,508 16 37 41,735LowLoad 16

UnCompressed1,496 1,496 16 37 41,403LowLoad 17

UnCompressed1,502 1,502 16 41 37,513LowLoad 18

Type BackupSizeGBOnDiskSizeGBStripesDurationMB/minSystemStateUsingBackupNumber

Compressed1,479 313 1 1609,467LowLoad 3

Compressed1,479 313 1 12412,215LowLoad 3

Compressed1,434 303 1 10813,597LowLoad 4

Uncompressed1,479 1,479 1 6882,201LowLoad 5

Uncompressed1,532 1,532 1 1,4821,059HighLoad 8

Uncompressed1,580 1,580 1 4653,479LowLoad 9

Uncompressed1,575 1,575 1 5672,844HighLoad 10

Uncompressed1,584 1,584 1 6182,625LowLoad 12

Uncompressed1,594 1,594 16 5193,145LowLoad 13

Uncompressed1,463 1,463 16 4893,064LowLoad 14

Uncompressed1,467 1,467 16 3953,803LowLoad 15

UnCompressed1,508 1,508 16 4683,300ModerateLoad 16

UnCompressed1,496 1,496 16 5922,588LowLoad 17

Uncompressed1,502 1,502 16 4863,165LowLoad 18