Script to Split SQL Server Database Backups into Multiple Files

Problem

The requirement is to create a simple tool that will split a large SQL Server database backup file into several equal sized smaller backup files. The advantage of breaking up the backup into many smaller files is that you can copy the smaller files around your network or to smaller storage devices for portability.  This could also be helpful if you have a storage issue and don’t have room for one large SQL Server backup file.

Solution

How to Backup a SQL Server Database into Multiple Files

My solution involves creating a rather simple T-SQL stored procedure in the SQL Server master database, called dbo.usp_DBSplitBackup that will get the following parameters:

• @dbName – the database name for which to do the backup.
• @primaryDrive – the primary drive letter (for odd backup files if primary<>secondary or for all backup files if primary=secondary).
• @secondaryDrive – the secondary drive letter (for even backup files if primary<>secondary).
• @backupDir – the backup folder path (without the drive letter and ':\' that follows). For example, if the backup is in c:\sql\backup\data – we will write 'sql\backup\data'.
• @nParts – the number of parts to split the backup.

The procedure constructs a backup database statement and executes the statement dynamically by using the EXEC statement.

SQL Server Stored Procedure to Split Backup into N Parts

Here is the SQL Server stored procedure which you can create in the master database.

-- =================================================================================================
-- Author:         Eli Leiba
-- Create date:    2018-09
-- Procedure Name: dbo.usp_DBSplitBackup 
-- Description:
--           The procedure takes 5 parameters:
--            1. @dbName = Logical database name
--            2. @primaryDrive = primary drive in which the odd parts of backup are stored.
--            3. @secondaryDrive = secondary drive in which the even parts of the backup are stored.
--            4. @backupDir = backup directory to store all backup parts
--            5. @nParts = number of files for which to split the backup.
--           
--           The procedure splits the database backup files to the number of files 
--           given each file is numbered and sized as equal as possible.
--           All the backup parts are stored in the Backup directory.
-- =================================================================================================
CREATE PROCEDURE dbo.usp_DBSplitBackup (
   @dbName SYSNAME,
   @primaryDrive CHAR (1),
   @secondaryDrive CHAR (1),
   @backupDir NVARCHAR (200),
   @nParts TINYINT)
AS
BEGIN
   SET NOCOUNT ON
   DECLARE @backupTSQLCmd NVARCHAR (2000)
   DECLARE @idx TINYINT = 0
   SET @idx += 1
   SET @backupTSQLCmd = CONCAT ('BACKUP DATABASE ', '[',@dbName,'] TO ')
 
   WHILE @idx <= @nParts
   BEGIN
      SET @backupTSQLCmd += CONCAT (
            'DISK = ',
            '''',
            iif(@idx % 2 = 1, @primaryDrive, @secondaryDrive),
            ':\',
            @backupDir,
            '\',
            @dbName,
            '_',
            rtrim(ltrim(STR(@idx))),
            '.BAK',
            '''',
            ', '
            )
      SET @idx += 1
   END
   SET @backupTSQLCmd = left (@backupTSQLCmd, len(@backupTSQLCmd) - 1)
   PRINT @backupTSQLCmd
   EXEC (@backupTSQLCmd)
   SET NOCOUNT OFF
END
GO

Sample SQL Server Stored Procedure Execution

Split the Northwind database backup into 10 equal parts and store in folder “C:\Datashare\SQLBackup”.

Run the following script:

use master
go
EXEC dbo.usp_DBSplitBackup  
   @dbName='Northwind',
   @primaryDrive='C',
   @secondaryDrive='C',
   @backupDir='Datashare\SQLBackup',
   @nParts=10
go

Here are the results after running the command above.  You can see it creates 10 different backup files.

BACKUP DATABASE [Northwind] 
TO DISK = 'C:\Datashare\SQLBackup\Northwind_1.BAK', DISK = 'C:\Datashare\SQLBackup\Northwind_2.BAK', 
   DISK = 'C:\Datashare\SQLBackup\Northwind_3.BAK', DISK = 'C:\Datashare\SQLBackup\Northwind_4.BAK', 
   DISK = 'C:\Datashare\SQLBackup\Northwind_5.BAK', DISK = 'C:\Datashare\SQLBackup\Northwind_6.BAK', 
   DISK = 'C:\Datashare\SQLBackup\Northwind_7.BAK', DISK = 'C:\Datashare\SQLBackup\Northwind_8.BAK', 
   DISK = 'C:\Datashare\SQLBackup\Northwind_9.BAK', DISK = 'C:\Datashare\SQLBackup\Northwind_10.BAK'
Processed 1200 pages for database 'Northwind', file 'Northwind' on file 1.
Processed 2 pages for database 'Northwind', file 'Northwind_log' on file 1.
BACKUP DATABASE successfully processed 1202 pages in 0.964 seconds (9.738 MB/sec).

Here are the files:

Here is the code to write the backup to a single SQL Server backup file:

EXEC dbo.usp_DBSplitBackup 
   @dbName='Northwind', 
   @primaryDrive='C',
   @secondaryDrive='C',
   @backupDir= 'TESTBACK', 
   @nParts=1

The is the file:

Next Steps

• You can create and compile this simple procedure in your master database and use it as a simple T-SQL tool for splitting large database backup files into several small files.
• Note that in order to restore the database from the backup files, you need ALL backup files for the RESTORE DATABASE command.
• If you use two different drives, make sure that both drives have the same backup folder paths.
• The procedure should work for SQL Server version 2012 and later.
• Enhance the stored procedure as follows: write to more than 2 drives, add a date and time stamp to the file names, etc.
• Check out the SQL Server Backup Tutorial.
• Check out the SQL Server Backup Tips.