You are here:Home » SQL Server General » Split Database Full Backup to Multiple files - SQL Server

Split Database Full Backup to Multiple files - SQL Server

As a maintenance activity DBA needs to take care of the database backups and monitoring for the same because database backups are more important in recovery. Production databases have more sizes and because of heavily size of the databases it is required more time and IO to maintain 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 ne more for the same. So SQL Server has one more features to database backups can split to multiple files. Using this way SQL Server runs the multiple thread of database backups of each file and can be completed faster comparatively in less time and IO.

Let us see the demonstration of the complete database backups and split 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 the 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 the image above the IO and time, now please compare these 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 the 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 of all of the backup sets as how the split backup can reduce the time and IO to perform the database backups.