Article ID: 325334 - Last Review: October 17, 2003 - Revision: 3.2

INF: SQL Server and Striped Backups

View products that this article applies to.

System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

This article was previously published under Q325334

SUMMARY

With striping, you can back up a database to different disks either to increase backup throughput or to distribute space usage across different disks. However, the files may or may not be evenly distributed.

Back to the top

MORE INFORMATION

SQL Server 7.0

If SQL Server stripes physical devices that have different input/output (I/O) throughputs, SQL Server optimizes for speed. This means that faster devices receive more backup data that is written to disk than the slower device in the same period of time. As a result of this behavior, faster disks may run out of space and you receive following error message:

BackupMedium::ReportIoError: write failure on backup device 'device name'. Operating system error 112(There is not enough space on the disk.).

The following example demonstrates this issue. The example is based on the following hardware configuration:

There are two physical disks: disk 0 and disk 1.
Logical drive C corresponds to disk 0, and drive E corresponds to disk 1.
Disk 0 is significantly faster than disk 1.
Database files do not reside on either of these two disks.
There is no additional I/O activity on drive C or drive E except the backup.

Scenario 1

If you run the following backup command

backup database testdb to disk='c:\temp\testdb_1.bak', disk='e:\temp\testdb_2.bak'  with init

file Testdb_1.bak may be significantly larger than Testdb_2.bak when the backup is complete.

Scenario 2

If you run the following backup command

backup database testdb to  
disk='c:\temp\testdb_1.bak', disk='c:\temp\testdb_2.bak', disk='c:\temp\testdb_3.bak', 
disk='e:\temp\testdb_4.bak', disk='e:\temp\testdb_5.bak', disk='e:\temp\testdb_6.bak' 
with init

Files Testdb_1.bak, Testdb_2.bak, and Testdb_3.bak are similar in size, while files Testdb_4.bak, Testdb_5.bak, and Testdb_6.bak are similar in size. However, files Testdb_1.bak, Testdb_2.bak, and Testdb_3.bak may be significantly larger than files Testdb_4.bak, Testdb_5.bak, and Testdb_6.bak.

In both scenarios, drive C may run out of space and the error message may appear before the backup process is complete if drive C has limited space. If you must have files evenly distributed on both disks, you must use disks that have comparable I/O throughputs.

Back to the top

SQL Server 2000

In SQL Server 2000, regardless of the I/O throughput differences, SQL Server tries to distribute the backup data evenly to the devices. In the two scenarios that are listed in the "SQL Server 7.0" section, all files are similar in size when the backup process is complete. In this case, the slower disk may become a backup bottleneck in terms of performance. If increasing performance is your primary goal, you must avoid using the slow disk in striping and use disks with comparable throughputs instead.

Back to the top