SQL Server with Rick Martinez

Feb 2 2010   9:42PM GMT

SQL SERVER 2005 Striping Backup Sets



Posted by: Rick Martinez
BACKUP, SQL, SQL 2005, SQL SERVER 2005, SQL SERVER Backup, STRIPING, T-SQL

Striping backups involves using more than one device (disk or tape) for a single backup set operation. When performing a database backup, you can use up to 64 devices (disk or backup) in your backup operation. This is particularly useful for very large databases, because you can enhance backup performance by striping the backup files across seperate drives. Striping the backup files means each file is written simultaneously. Striped backups use parallel write operations, and can significantly speed up backup operations.
Here is a sample:

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AdventureWorks_Stripe1.bak',
DISK = 'D:\Backup\AdventureWorks_Stripe2.bak',
DISK = 'E:\Backup\AdventureWorks_Stripe3.bak'

This creates three files which are each used to store one third of the backup information needed to restore the database.

Comment on this Post

Leave a comment: