SQL Backup to Multiple Files3 min read

I recently had a project that required uploading a large database to an FTP server. Sounds simple enough except that each folder on the FTP server had a size limit on file uploads. Uploading one database backup that exceeded the GB limit was out of the question. The solution that came to mind was splitting the backups into separate files. By splitting the backup, smaller backup files could be uploaded to individual folders on the FTP server and fit within the folder limits.

Advantages

Since the FTP server was also known to have trouble with large uploads and large downloads, smaller files also helped alleviate some headaches that could be caused by connection issues. Uploading a handful of small files became much easier than trying to upload one large file and watching it upload for an hour only to see it fail at 95%. Experiencing that once is one time too many.

The last time I had used split backups was due to the speed advantage. When backing up to one file, only one tread is assigned. When backing up with multiple files, multiple threads will be used which can reduce the time it takes for the backup to complete. Combine that with the ability to backup to multiple drives (especially if you have a drive that is nearing capacity) and you can come out with a much more acceptable backup time.

How to Backup

The script below will backup the database into two files:

Navigate to that path and we’ll find two .bak files:

How to Restore

Restoring from multiple files is similar. Starting with the same script, replace “BACKUP” with “RESTORE” and replace “TO” with “FROM” to have a restore script:

Keep it Together

Be mindful of where your backup files are going if you are backing up to multiple folders or drives. Without all of the backup files, restores are not going to happen. For example, restoring one of two files will return this error: