SQL Server Script to Automatically Split Database Backups into Multiple Backup Files

By:   |   Comments   |   Related: > Backup


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.


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)
   DECLARE @backupTSQLCmd NVARCHAR (2000)
   SET @idx += 1
   SET @backupTSQLCmd = CONCAT ('BACKUP DATABASE ', '[',@dbName,'] TO ')
   WHILE @idx <= @nParts
      SET @backupTSQLCmd += CONCAT (
            'DISK = ',
            iif(@idx % 2 = 1, @primaryDrive, @secondaryDrive),
            ', '
      SET @idx += 1
   SET @backupTSQLCmd = left (@backupTSQLCmd, len(@backupTSQLCmd) - 1)
   PRINT @backupTSQLCmd
   EXEC (@backupTSQLCmd)

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
EXEC dbo.usp_DBSplitBackup  

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

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:

SQL Server backup file list

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

EXEC dbo.usp_DBSplitBackup 
   @backupDir= 'TESTBACK', 

The is the file:

single SQL Server backup 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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms