Recently, I used this very powerful backup method and have seen significant improvement in backup speed. So I wanted to share it with everyone.

When I did backups, I used to take entire backup to one backup disk location using the standard BACKUP command shown below. If the database is large the backup process could take longer.

[sourcecode language=”sql”]
BACKUP DATABASE AdventureWorks
TO DISK =N’H:\Backups\AdventureWorks\AdW_01012011.BAK’
WITH STATS = 10
[/sourcecode]

Sql Server backup process is very scalable. The only limitation to it is the speed of the backup device, i.e. how fast it can read/write data. So even if you have a RAID disk drive, its speed is limited. And when you have a large database ranging more than 500 GB to multiple TB’s, FULL backups can take much longer to complete.

So stripe backup can help you cut the backup time by many folds. If you have multiple drives attached to the Server, then we could essentially stripe the backup to separate disks and take advantage of the speed of multiple hardware devices to cut short the backup time. Backup process makes sure of stripe, almost, equal amount of data to each location.

[sourcecode language=”sql”]
BACKUP DATABASE AdventureWorks
TO DISK =N’H:\Backups\AdventureWorks\AdW_01012011_1.BAK’
, DISK = N’I:\Backups\AdventureWorks\AdW_01012011_2.BAK’
, DISK = N’J:\Backups\AdventureWorks\AdW_01012011_3.BAK’
WITH STATS = 10
[/sourcecode]

Note: In some cases, Backup Striping could cause complexity to existing BACKUP/RESTORE procedures. Now you have to remember where each part of the backup file is located, so this technique is not for everyone. Its only for large databases where the benifit our weighs the complexity.

Thought not very popular we could do the same for BACKUP LOG command, as shown below:

[sourcecode language=”sql”]
BACKUP LOG AdventureWorks
TO DISK =N’H:\Backups\AdventureWorks\AdW_01012011_1.TRN’
, DISK = N’I:\Backups\AdventureWorks\AdW_01012011_2.TRN’
, DISK = N’J:\Backups\AdventureWorks\AdW_01012011_3.TRN’
WITH STATS = 10
[/sourcecode]

Hope this helps,
_SqlTimes