Split Backup in SQL Server

How many times we come across a situation where the backup size of a SQL Server database is too huge and you run out of space on a single disk. You are having enough space but they are splitted on different drives. 

Microsoft has provided a beautiful solution for this. Its Split backup in SQL Server. Some of the advantages of Split backups are

1. You have split the backup file into different smaller pieces and then place different piece in different drive. This reduces your issues of having large space in single drive.

2. Since the Split Backup run in different threads, your backup process will be quite faster.

3. If you want to move a huge backup file to different location, moving a single huge file might be problematic. If you split the file you can move in pieces.

Below is the T-SQL script to have Split Backup in SQL Server


USE master
BACKUP DATABASE [AdventureWorks2008R2] TO
DISK = 'D:\MSSD_LAB\MSSQL_BACKUPS\Test_Split_Backup\MyDisk1\AdventureWorks2008R2_1',
DISK = 'D:\MSSD_LAB\MSSQL_BACKUPS\Test_Split_Backup\MyDisk2\AdventureWorks2008R2_2',
DISK = 'D:\MSSD_LAB\MSSQL_BACKUPS\Test_Split_Backup\MyDisk3\AdventureWorks2008R2_3',
DISK = 'D:\MSSD_LAB\MSSQL_BACKUPS\Test_Split_Backup\MyDisk4\AdventureWorks2008R2_4'
WITH INIT , NOUNLOAD , NAME = 'AdventureWorks2008R2 Test Split backup', NOSKIP , STATS = 20, NOFORMAT

If you see above, I have created 4 folders MyDisk1, MyDisk2, MyDisk3 and MyDisk4. These can be your different drives as I cannot do this on my test machine as I dont have much drives. Also if you place these different drives on different RAID then the process will be more faster as there will less IO.

I will write in my Next Post which can be found here on How to restore database from SQL Server Split Backup