In this article, I am going to explain how we can split the large backup file into multiple small backup files using SSMS 2016. I will also explain how we can combine multiple backups to restore a database and split the backup of the SQL database using SQL Server Management Studio (SSMS).

When we generate a backup of the large database, it is advisable to generate the backup in multiple files. This process reduces the time to generate the backup. If your business requirement is to download the backup of a large database, then downloading multiple backup files method is way faster than downloading one large backup file.

To demonstrate the process, I have restored the AdventureWorks2017 database on my workstation. We are going to perform this task using SQL Server Management Studio.

To generate the backup, open SQL Server Management Studio -> Connect to the database engine -> Expand Databases -> Right-click on AdventureWorks2017 database -> Hover on Tasks -> Click on Back Up…

You can refer to the following image:

A dialog box Backup database opens. We want to generate a full backup of the AdventureWorks2017 database, hence choose AdventureWorks2017 from the database drop-down box. In the backup type drop-down box, choose FULL. See the following image:

To generate the multiple backup files, click on Add. (Screen-1) A dialog box, Select Backup Location opens (Screen-2). In this dialog box, we can specify the location where you want to put the backup file. In the File Name text box, you can see the backup path, which is the default location of the backup file. We can specify a different location. To choose the desired location, click on the balloon (…) See the following image:

In Locate Database Files dialog box, choose the desired location where you want to put the backup file. Provide the desired name of backup in the File Name text box. Click OK to close the dialog box. See the following image:

On the Select Backup Destination dialog box, the backup destination has been changed. Click OK to close the Select Backup Destination dialog box. See the following image:

On the Back Up Database screen, you can see the location of the backup in the list box within the destination section. See the following image:

To generate multiple backup files, follow the above process again. Once the destination of the backup files has been configured, the Back Up Database dialog box looks like the following image:

#backup and restore #sql server management studio (ssms) #sql