How to create SQL Server database split backup files

Introduction

One of the main tasks for every database administrator is creating a reliable disaster recovery plan. The plan always includes multiple backup and restore operations. Usually, opting for conventional, single-file backups should suffice, but in some cases, resources like disk space, backup time, or both could be the issue. This is usually the case when working with large databases.

In these situations, backups can be easily split into multiple files, and stored on multiple smaller hard drives, or even thumb drives. Backing up large databases by using conventional backups may take way too much time, and requires the vast amount of free hard disk space on a single drive. By using the split backup method, one large backup file gets split into multiple smaller files of similar file size. The smaller split files are much easier to handle: they can be easily copied on media with smaller capacity or distributed across the network. These files could be stored on a single, or multiple drives. By writing database backups to multiple drives, the time needed for the backup process gets much shorter, since more than one thread gets involved in the process, and higher I/O is achieved. SQL Server 2008 and later even have built-in Database Backup Compression, that enables further size reduction for the backup files. However, there is one huge drawback for using the split backup files on multiple drives: if any of the drives fail, the entire set of backup files becomes useless.

There are a few ways to configure a backup process to create split backup files: by using T-SQL scripts or SQL Server Management Studio.

Creating split backup files with T-SQL script

The script for creating split backup files is very similar to script for conventional backups. The only difference is the destination path. Script for conventional backups contains only one backup path. The split backup script must have multiple paths defined: one path for each split backup file. In this case, 3 split backup files are created for a single database backup, and all of them are stored on different drives. Be sure to have all necessary read and write permissions on the drives used for backup and restore, and make all the necessary folders defined in the path.

BACKUP DATABASE AdventureWorks2014 
TO DISK = 'C:\Backup\AdventureWorks2014/1.bak',
   DISK = 'D:\Backup\AdventureWorks2014/2.bak',
   DISK = 'E:\Backup\AdventureWorks2014/3.bak'
GO

The conventional backup for AdventureWorks2014 database takes approximately 195 MB of free space. It takes about 13 seconds to create conventional backup on my machine. When using the split backup query, three files of 65.5 MB are created, and the whole process lasts 7 seconds. The time needed to create the backup files is almost halved!

To restore the database from the split backup files, use the similar query:

RESTORE DATABASE [AdventureWorks2014]
FROM DISK = N'C:\Backup\AdventureWorks2014/1.bak',
     DISK = N'D:\Backup\AdventureWorks2014/2.bak',
     DISK = N'E:\Backup\AdventureWorks2014/3.bak'
GO

The restore process lasted 9 seconds for the split backup query, while using the conventional backup took 11 seconds.

Creating split backup files with SQL Server Management Studio

To create split backup files with SQL Server Management Studio, perform the following steps:

Open the Object Explorer, expand the Databases node, and right click on the database that needs to be backed up. From the dropdown menu select Tasks, and click on the Back up… option.
In the General tab, specify the database that is used for the backup and the backup type (full or differential). To specify multiple backup paths, click Add… button.
Set the backup destination by typing the path and the backup filename, or browse for the backup folder by using the browse button on the right. Click OK when done. Repeat the step until all destinations are set. The list of backup paths is displayed on the bottom of the page.
By clicking OK button on the lower right, the backup process starts. The success message is displayed when backup process completes.

To restore the database from split backup files with SQL Server Management Studio:

Open the object explorer, right click on Databases node, and select Restore Database…
In General tab, mark Device radio button, and browse for the backup files with browse button on the right.
In Select backup devices window, click Add.
Browse for the backup files in the folder tree, or enter the backup path and file name manually. Click OK when done. Repeat the step until all of the backup files are listed, and click OK when finished.
Program loads all file paths and displays the name, type, and destination of the database that is about to be restored. To complete the restore process, click OK.