How can I backup a large SQL Server database without impacting performance?
- Isolate backup I/O from the rest of the I/O on your system. While it may take longer and be more prone to hiccups, backing up over the network instead of to a local disk may help alleviate the direct impact on the instance. Even in a virtual machine you should be able to expose other storage so that SQL Server can write to a different I/O subsystem.
- It will be unlikely to make any difference whatsoever but, if this is run from a job, why do you need to use the
STATS
option? Are you sure you need the other options (NOUNLOAD
,NOSKIP
,NOFORMAT
)? I haven't done any extensive performance testing on the entire matrix of options, but IMHO you should only use the options that you know you need. - Run your full backups at a different time that doesn't interfere with regular user activity. This isn't always possible, but even in 24/7 operations there are peak and lull times.
- If you move to 2008+ you can take advantage of backup compression (Enterprise on 2008, Standard or Enterprise in 2008 R2+). If you can't move to 2008 or don't have the adequate edition, there are 3rd party backup tools that will do compression for you, and they're all pretty good at it. Since 2008 I've been able to use native compression, and I have been quite satisfied with both speed and compression % there with no need to further invest in 3rd party. But with 2005 I remember having good success with Red-Gate SQL Backup; Quest LiteSpeed is also quite good, but I have no idea what kind of effort is being put into it since the Dell acquisition.
- If your databases are in full recovery, you could go longer between full backups, trading that for the knowledge that if you have to recover to a point in time, you will potentially have more logs to restore. You could combine this option with Ali's suggestion below of taking diffs throughout the week - if you had to recover on Thursday, you would need to restore 1 full, 1 diff, and the logs since the diff.
- If you have a lot of data that is stale and unchanging, you could consider hosting that in a different database with a different recovery plan, or at least on different filegroups, and split up your backup operations that way. If you have reference or archive data that can be made read-only, and if you are in simple recovery, you can move it to its own read-only filegroup, back it up once, and not have to include it in your nightly backups ever again. See these MSDN topics:
Performing piecemeal restores
Example: Piecemeal Restore of Only Some Filegroups (Simple Recovery Model)
This is a common issue, there are multiple solutions and really depends on your environment. Let's go through them:
1-Backup Compression On The Fly
In 2008 R1 Backup Compression became available in Enterprise, in 2008R2 it became available in Standard. This is HUGE. It will save you A LOT of time. If you can upgrade go for it. If you can't, check out RedGate's HyperBak utility, or Quest LiteSpeed. Both have a free trial.
2- Full and Diff Backups
I inherited a 2TB prod database cause a lot of timeouts for a 24/7 major internet company I worked out. We enabled full and differential backups which saved us a lot of time. I would take a full backup Sunday 12:00am when activity was low, and take diffs during the week. This saved a lot of space. Diff's work different than transaction logs as they work on what database pages were changed. Any changed pages are backed up. Thus you do a full restore, then the diff restore to add the modified pages.
3- What Is Your Bottleneck?
Bottleneck analysis is important to diagnose. Are you backing up to the same disk array as your data files? Are your data files being pegged? What is your DISK SEC/READ and DISK SEC/WRITE for the data disks during backups? I modified the backups to create 4 files. Each file has it's own thread writer and in our SAN that worked out great. Test it out, I shaved off 45 minutes by just creating 4 backup files. Just make sure your disk metrics listed above are low. Get a baseline.
4- Replicate to a Different Server And Back That Up
This one is slightly advanced. You have to make sure your replicated database is up to date and you need proper monitoring for that. If it is though, you can just backup the replicated database.