As a maintenance activity, DBA need to take care of the database backups and monitoring for the same because database backups are more important in recovery. Production live databases have more size and due to that it is require more time and IO to complete it. You can read my earlier posts Automated All Databases Backups Script and Full Backup database from SSMS. 

You may know those full database backups are require more time to complete it as well as backup size will be more for the same. So SQL Server have one more features to database backups can split to multiple files. Using this way SQL Server run the multiple thread of database backups for each files and can be completed faster comparatively with less time and IO. 

Let us see the demonstration of the complete database backups and splitted database backups to multiple file and how it will work and advantage for the same. Here we will perform full database backup of ReportServer database.

We can perform the split backup into multiple files with management studio.



How can we do with TSQL?

#1. Complete Backup: We are running below script for the complete backups and we will see the IO and time required to complete the backup and this will into single file only. 

SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE ReportServer 
TO DISK = 'D:\DBBackups\ReportServer\ReportServer.bak'

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

You can see from image above the IO and time , now please compare those factors with split backups and i will perform from the below script by method #2. 

#2. Split Backup to multiple files: We are running below script for the Split Backup and we will see the IO and time required to complete the backup and this will into multiple file only.

SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE ReportServer TO 
 DISK = 'D:\DBBackups\ReportServer\ReportServer_Split1.bak'
,DISK = 'D:\DBBackups\ReportServer\ReportServer_Split2.bak'
,DISK = 'D:\DBBackups\ReportServer\ReportServer_Split3.bak'

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

Please go through the below screen also which mention the size of the database backups how they are split. 


Conclusion: 
Now you have more idea from all of the backup shots as how the split backup can reduce time and IO to perform the database backups.