How to create SQL Server database split backup files


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 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, SQL Server Management Studio or ApexSQL Backup.

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'

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'

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:

  1. 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.

  2. 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.

  3. 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.

  4. 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:

  1. Open the object explorer, right click on Databases node, and select Restore Database…

  2. In General tab, mark Device radio button, and browse for the backup files with browse button on the right.

  3. In Select backup devices window, click Add.

  4. 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.

  5. Program loads all file paths, and displays the name, type and the destination of the database that is about to be restored. To complete the restore process, click OK.

Creating split backup files with ApexSQL Backup

ApexSQL Backup is the simple, user friendly backup and restore manager. Among many other features, the application supports split backup files. The entire backup process is defined through a wizard-style setup, it can be saved as a policy or a schedule, and can be reused later. To create split backup files with Apex SQL Backup follow these steps:

  1. Run the application and connect to the SQL Server instance(s) that need to be backed up.

  2. On the upper left, click Backup button from the Maintenance tasks group. This will start the Backup wizard.

  3. In Database step of the Backup wizard, choose the SQL Server instance from the drop down menu, check the box for the database that needs to be backed up, and tick the radio button in front of the preferred backup type. Be aware that at least one full backup file needs to exist in order to be able to perform differential or transaction log backups. Proceed to the next step.

  4. In Type and output step, automatic naming rules for the backups and descriptions may be defined. Different parameters like database name, backup type, server name, date, or time may be automatically included in the name or description of the backup. To add more parameters for automatic naming, click on the browse button on the right (), and select one or more parameters from the grid. To use custom database name/description, simply delete the contents from name/description fields, and enter the preferred name.

  5. Destination paths also need to be defined in this step. Remove the default SQL Server backup path by clicking on red X next to the path. Click Add button on lower right to specify the backup paths.

  6. Enter the backup path manually, or browse for the preferred destination with browse folder button. The naming rules for the backup files could be applied in this step in the same way as in the step 4. Repeat the step, until all backup paths are listed. Proceed to the next step when done.

  7. In Options step of the wizard, some additional actions like verification, compression and encryption may be applied to the backup process.

  8. In Schedule step, there is an option to automate the backup process by defining a schedule. To do this, simply tick the Schedule radio button, and set the preferred schedule. If there is no need for the automated backup, leave the Execute immediately radio button checked.

  9. In Submit step, the defined backup operation will be displayed. To execute the operation, click Next. The check mark will appear in front of the successfully executed backup task. Click Finish to exit the wizard.

To restore the database with ApexSQL Backup:

  1. Click on the Restore button from the Maintenance tasks group.

  2. The Restore wizard starts. In Destination step, choose the server, and the database to restore to. Alternatively, enter the name for a new database to restore to, if existing databases need to be kept for some reason.

  3. In Source step, choose to restore from Specific backup files. Click Add button, and browse for the split backup files that were created in previous steps. Be sure to select the restore type that matches the type that was used for the backups.

  4. In Options step, set the additional tasks and checks that may be included in restore process.

  5. In Schedule step, the restore process may be automated by defining a schedule. Tick the Schedule radio button, and set the preferred schedule if database needs to be restored frequently. If there is no need for the automated restore, leave the Execute immediately radio button checked, and proceed to the next step.

  6. In Submit step, all pending actions are listed. To execute them, click next. Click Finish to exit the wizard when restore completes.

See also

May 6, 2016