Sql-server – How to backup a large SQL Server database without impacting performance

backupsql serversql-server-2005

We've had reports of queries running slowly or timing out early in the morning, and the only job I see running that I think could affect this is our database backup job.

The database itself is about 300GB, and the backup job starts at 4:30am and doesn't finish until a little after 7:00am. The current syntax of our backup job is:

BACKUP DATABASE [DatabaseName]
TO DISK = N'E:\Database Backups\DatabaseName.Bak'
WITH INIT, NOUNLOAD, NAME = N'DatabaseName.Bak',
NOSKIP, STATS = 10, NOFORMAT

E:\ is a partition on the server which holds both the databases and the database backups.

It should also probably be noted that this is a virtual server, not a dedicated standalone server. We started getting complaints about slowdowns during the backup process right after we switched to a virtual server, so I think it may be related.

Is there a way to run this backup job so it doesn't affect the query performance while its running?

We are using SQL Server 2005

Best Answer

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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)

Related Question