2019-11-19

Striped Database Backup

SQL Server allows you to backup your database or transaction log in a striped media set. A striped set is a set of disk files on which backup data is divided into blocks and distributed in a fixed order. Most database administrators overlook this feature. It can be used to speed up the backup process by distributing the backup workload into multiple storage devices.

Below scripts demonstrate how to make a striped backup set:
BACKUP DATABASE [StackOverflow2013]
TO
DISK='D:\DbBackup\StackOverflow2013_1.bak',
DISK='D:\DbBackup\StackOverflow2013_2.bak',
DISK='D:\DbBackup\StackOverflow2013_3.bak',
DISK='D:\DbBackup\StackOverflow2013_4.bak'
WITH STATS = 10
GO

I only used the same drive D:\ on above script, but you can specify different drives for each DISK.
Here is the result:

Below script demonstrate how to restore from a striped backup:
RESTORE DATABASE [StackOverflow2013]
FROM
DISK='D:\DbBackup\StackOverflow2013_1.bak',
DISK='D:\DbBackup\StackOverflow2013_2.bak',
DISK='D:\DbBackup\StackOverflow2013_3.bak',
DISK='D:\DbBackup\StackOverflow2013_4.bak'
WITH STATS = 10

GO


The major downside of striped backup is that if one backup file is corrupted, you cannot restore it.

No comments:

Post a Comment