Backing Up A SQL Server Database Into Multiple Backup Files
On a recent project I was tasked with backing up a very, very large database which would need to be uploaded to an Azure server. Considering the sheer size of the database, nearly 1.6TB, I quickly realized the best option was to split the backup into multiple files. The main concern was the availability of hard drive space to store the backup. Splitting the backup into multiple files allows you to save each backup to a different location thus reducing the concern for available disk space on a single drive.
In addition to disk space, splitting the backup will also greatly reduce the overall time it takes to perform the backup as multiple threads can be utilized and a higher I/O can be achieved. However, it’s worth noting that there is a limit here. Ideally you do not want to create more backup files than you have physical processors. If you have 6 processors, building 8 files will greatly reduce the servers ability to utilizing threading and slow the process down significantly.
I also highly recommend backup compression be enabled as well.
The steps outlined below will walk you through the process of backing up a database into multiple backup files.
Once you’re connected the SQL Server in SQL Management Studio, right-click the database and select Tasks >>Back Up… as shown below.
In this example I will be backing up the JDev database.
On the Back Up Database window make sure the Backup Type is set to Full and the correct database is selected. If any backups are listed under the Destination section select them and click Remove to clear it out. This will help insure that all backup files are configured to write the the proper destination, whether that be the same location or split onto multiple disks. In this example, since I am working with a small database, I will be configuring each backup file to write to the same drive.
Once any existing backup destinations have been cleared out, click Add to configure the first file. In this example we will configure 3 backup files.
By default, the location for most backups will be something similar to the path below (depending on your environment and version of SQL):
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\
Click the ellipses to specify a new path as shown below.
After clicking the ellipses you will see the window where you will specify the file location and file name. It is important to make sure the path (or paths) that you will be selecting have sufficient drive space to store the backups. As mentioned above, I will be putting all files on the same drive, though you can specify a separate drive for each backup if desired. In this example I will be putting all files on the R:\ drive.
It is also important that each file have a unique name. In this case I will be naming the first file JDev01.bak
Once the path and filename have been specified, click OK. You will now see the path and file name listed in the Select Backup Destination Window. Click OK again.
Under the Destination section of the Back Up Database window you will now the first backup file listed.
In order to add additional files simply follow the steps again by clicking Add… and configure the additional files. In the screenshot below you will see that I have configured 2 additional backup files, both set to write to the R:\ drive.
If you select the wrong drive filename, you can always select the backup and click Remove and then go add it back.
Once all backup locations and file names have been configured, click OK to start the backup process. Once complete you can open up the file path on the server and confirm that the files were created.
Additionally, all these steps can be further simplified by scripting them out in SQL. The following script will perform the same steps as outlined in the GUI steps above.
BACKUP DATABASE [JDev]
TO DISK = N'R:\JDev02.bak',
DISK = N'R:\JDev01.bak',
DISK = N'R:\JDev03.bak' WITH NOFORMAT, NOINIT,
NAME = N'JDev-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
You can also script out the restore of the backup files using the following script.
RESTORE DATABASE [JDev]
FROM DISK = N'R:\JDev01.bak',
DISK = N'R:\JDev02.bak',
DISK = N'R:\JDev03.bak'
GO
In most cases a single backup file will suffice when backing up a database but understanding when and how to split out the backup into multiple files is an important aspect of administering a database. Whether it’s part of a disaster recovery plan or due to disk space limitations, splitting out backups into multiple files may be the solution to your database backup problem.
One last thing to note before I end this post. There is a small, but potentially headache inducing caveat to splitting out database backup files, especially when writing to multiple drives. Should one of the drives become corrupt or inaccessible after the backup has been written, none of the backup file will be usable. Deleting or otherwise losing any of the individual backup files will prevent you from restoring the database. Though this is not a common problem, it’s worth knowing and if you’re planning on splitting out backups, you should be aware of this.