Can I run concurrent backups of multiple read only filegroups?

Question

I have 62 readonly filegroups in a SQL server 2008 enterprise database. Can I backup multiple read only filegroups at the same time? I would assume this to be the case but given this database is over 20TB in size I do not want to invalidate any files by giving it the ol' college try.

• Aaron Bertrand: If the filegroups are read only, what's the rush to do them all at once? :-)
• Dan Waterbly: Having a full set of files on another server I can recover with :)
• Aaron Bertrand: Well, yeah, I get that. I wasn't suggesting that you don't have a good reason to take the backups. But if they're read only it's not like running them sequentially is going to destroy some point-in-time consistency. Also, if it takes 10 hours to run them serially, and 12 hours to run them concurrently because you're stressing a different choke point by running them all at once, are you any further ahead? In other words, it might end up being faster or slower depending on several factors. Also are you really going to invalidate anything if you run a backup of a read-only filegroup and it fails?
• Dan Waterbly: Thanks for the insight Aaron, the reason I wanted to speed it up is because we have 7 other network cards that I can get another 1gbs each from and disk storage to support the io. My plan was to run 8 at once because the resources would support it. As far as the invalidation question, I just wanted to be cautious and not assume. Having 20TB of a broken backup because I assumed I could do something I couldn't would suck. :)
• Aaron Bertrand: Well I'm just saying if you try and it fails, you just start over. You're not going to have 20TB of broken backup if you can't even get two to run at the same time.

Answer 1

Yes you can do multiple full file-group backups concurrently. The primary concern I would have would be for disk I/O and network I/O throughput doing so many at once. They'll still complete, just all slowly. Unless you have the I/O to handle 20TB at once.

• Dan Waterbly: Turns out SQL Server will only allow one backup at a time regardless of filegroup read only state. Perhaps this has changed after 2008R1?

Answer 2

This is not a supported feature of SQL Server 2008 R1 Enterprise.

Though, not officially from Microsoft, concurrent backups of anything but the transaction log cause other backups running on the same database to block. Source: Link

• Michael - sqlbot: It would be helpful to future visitors if you would post a link to the source of this information.