How can I backup a large SQL Server database without impacting performance?

Question

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

Answer 1

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)

Answer 2

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.

Answer 3

You can use these parameters:

BLOCKSIZE - Choose Size 512, 1024, 2048, 4096, 8192, 16384, 32768, 65536. (in KB)

BUFFERCOUNT - Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause "out of memory" errors because of inadequate virtual address space in the Sqlservr.exe process. - from MSDN

MAXTRNASFERSIZE - It's from 65536 bytes (64 KB) to 4194304 bytes (4 MB)

Answer 4

Try it. it resolved timeout expired problem while large size db.

Private Sub Command1_Click()
On Error Resume Next
Dim con As New Connection
Dim tm As String
con.CommandTimeout = 500'''Command timeout should be 500


 With con
    .ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=dbiBMS;Data Source=192.168.103.4"
    .Open
 End With
 tm = CStr(Time)

con.Execute " backup database dbiBMS to disk='E:\Database_Backup\Test1.bak' with format "

con.Close
MsgBox tm
Exit Sub
x:
MsgBox Err.Description

End Sub