Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
If the filegroups are read only, what's the rush to do them all at once? :-) –  Aaron Bertrand Sep 5 '13 at 18:42
    
Having a full set of files on another server I can recover with :) –  Dan Waterbly Sep 5 '13 at 19:35
    
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? –  Aaron Bertrand Sep 5 '13 at 19:38
    
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. :) –  Dan Waterbly Sep 5 '13 at 20:36
    
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. –  Aaron Bertrand Sep 5 '13 at 20:39
show 1 more comment

2 Answers

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.

share|improve this answer
    
Turns out SQL Server will only allow one backup at a time regardless of filegroup read only state. Perhaps this has changed after 2008R1? –  Dan Waterbly Sep 5 '13 at 19:36
add comment
up vote 0 down vote accepted

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: http://blogs.msdn.com/b/arvindsh/archive/2008/12/30/concurrency-of-full-differential-and-log-backups-on-the-same-database.aspx

share|improve this answer
    
It would be helpful to future visitors if you would post a link to the source of this information. –  Michael - sqlbot Sep 5 '13 at 21:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.