SQL Server: Have a large Database, use Striped Database Backup & Restore

Anvesh Patel | December 14th, 2016 | Backup, SQL |

What is the problem?

Computer Folders with Documents Sharing Data from Laptop
The problem is a large size of the Database like, 400+ GB size.
The problem is, backup and restore operations are taking a long time to complete.
This long running operation requires more CPU I/O resources.

What is the solution?

The easiest way to improve backup & restore performance is to allow the backup & restore operation to parallelize, which is known as backup & restore striping.

The striping means, storing single database backup into a different file / disk and can restore from this different file / disk.
Internally, It divides single backup file operation into defined multiple physical files and size of these all backup files is almost same.

By default, there is a single data reader and a data writer thread for normal backup & restore operations.

But when we stripped backup & restore operation, it is using multiple thread to complete these operations.
Mostly striping techniques can use whenever your database size is very large.

For example, if your database size is 400+GB, you can split it into four different files and make each database backup file size around 100GB.

T-SQL Script to Striping a database backup (four files):

BACKUP DATABASE AdventureWorks2012
TO 
DISK='D:\AdventureWorks2012_1.bak', 
DISK='D:\AdventureWorks2012_2.bak', 
DISK='D:\AdventureWorks2012_3.bak',
DISK='D:\AdventureWorks2012_4.bak'
WITH STATS = 10
GO

T-SQL Script to restore from Striped database backup (four files):

RESTORE DATABASE AdventureWorks2012
FROM  
DISK='D:\AdventureWorks2012_1.bak', 
DISK='D:\AdventureWorks2012_2.bak', 
DISK='D:\AdventureWorks2012_3.bak',
DISK='D:\AdventureWorks2012_4.bak'
WITH STATS = 10 
GO