Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

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


By:   |   Read Comments   |   Related Tips: More > Backup

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:

SQL Server backup file list

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:

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.


Last Update:


next webcast button


next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools