Let’s start with a very brief definition of some RAID levels.

• RAID 0 : Stripe your data across multiple disks. Writing a portion of the data to each disk. This improves performance but increases the risk of corruption.
• RAID 1 : Mirror your data across multiple disks. This duplicates the data identically on each disk. This reduces the risk of corruption but decreases performance.
• RAID 10 : Combine both methods. Stripe your data across multiple disks and mirror it at the same time.

Interestingly these same options are available with our SQL Server backups.

RAID 0

Splitting the backup data between multiple files.

This is actually a fairly common way to speed up a large backup. IO is one of the slowest part of the whole process so by splitting the backup into multiple files we can reduce our backup time by quite a bit. Having multiple files will also increase your restore time but you do have to make sure that all of the files are available. If you lose one file then the whole backup is useless.

This is done by listing multiple locations for the backup.

BACKUP DATABASE AdventureWorks2014
TO DISK='C:\Backups\AdventureWorks1.bak', 
   DISK='D:\Backups\AdventureWorks2.bak', 
   DISK='E:\Backups\AdventureWorks3.bak'
WITH FORMAT

RAID 1

Creating multiple copies of the backup data.

I don’t see this a whole lot but if you happen to need more than one copy of a backup then this can save you some effort. SQL will write up to 4 copies of a backup (the main copy + three mirrors). All of the copies have to all be of the same type. IE DISK, TAPE or URL. One of the benefits of this is the increased likelihood that at least one of your copies will be restorable. Another use for this clause is to create a second copy to be used for an alternate purpose (send to the vendor for example) while still keeping a copy in your normal backup location. You can of course take the backup and copy it afterwards but this lets you do it in a single command and should be quite a bit faster.

Important note: This is an Enterprise only feature.

We use the MIRROR TO clause to create extra copies.

BACKUP DATABASE AdventureWorks2014
TO DISK = 'C:\Backups\AdventureWorks_Mirror1.bak'
MIRROR TO DISK = 'D:\Backups\AdventureWorks_Mirror2.bak'
WITH FORMAT

Warning: This option should be used with caution. If the backup fails at any of the mirror locations then the entire backup fails, including the original.

RAID 10

Combining both striping and mirroring.

Last but not least you can combine the two options creating multiple striped backups. This has all the restrictions of striping and mirroring but includes the fact that the number and type of striped files must be the same for each mirror.

BACKUP DATABASE AdventureWorks2014
TO DISK='C:\Backups\AdventureWorks1.bak', 
   DISK='D:\Backups\AdventureWorks2.bak', 
   DISK='E:\Backups\AdventureWorks3.bak'
MIRROR TO DISK='F:\Backups\AdventureWorks1_Mirror1.bak', 
          DISK='G:\Backups\AdventureWorks2_Mirror1.bak', 
          DISK='H:\Backups\AdventureWorks3_Mirror1.bak'
MIRROR TO DISK='I:\Backups\AdventureWorks1_Mirror2.bak', 
          DISK='J:\Backups\AdventureWorks2_Mirror2.bak', 
          DISK='K:\Backups\AdventureWorks3_Mirror2.bak'
WITH FORMAT

Edit: I’ve made a couple of corrections.

• Under striping the backup. I incorrectly said that you will not gain any performance if all of the files use the same IO. This statement has been removed.
• Added a caution when mirroring your backup.

Filed under: Backups, Microsoft SQL Server, SQLServerPedia Syndication Tagged: backups, microsoft sql server