Splitting SQL Server backups to multiple files

Splitting database backups helps save space and run your SQL Server backups more efficiently. Get tips for executing this from Ashish Kumar Mehta.

Database size is increasing across businesses all over the world, and as a database administrator (DBA) one should make sure that database backups are completed on time.

For more on SQL Sever backup and recovery

Go back to backup school

Troubleshooting your SQL Server backups

Top 10 backup and recovery tips for SQL Server

However, there are scenarios when you wouldn't have enough space to complete SQL Server backups to a particular drive or the database backup is taking considerable time to complete. One possible solution might be to split database backup between multiple files in SQL Server to reduce the backup time and to use available space on multiple drives.

Why split database backups?

  1. Achieve faster database backup by splitting database backups to multiple files. By writing database backups to multiple files located on multiple drives, you can easily achieve a higher I/O, thereby reducing the time necessary to perform database backups.
  2. Using the Split File Backup option, you can easily split a very large backup file into multiple files of the same size.
  3. Since the database backup is split into multiple files, each file will be smaller, allowing the DBA to copy the files easily across the network or to tape.
  4. Open SQL Server Management Studio, expand Databases node and then right-click the database for which you would like to perform a Split File Full backup and click Tasks à Back Up… as shown in the screenshot below. For the purposes of this article, I am using the sample database named AdventureWorks2008R2.
Splitting SQL Server databases
Splitting SQL Server databases

1. In the Back Up Databases dialog box, click the Add… button, which is under Destination, to specify the path of database backup files. In the screenshot below, you can see that the AdventureWorks2008R2 database is backed up to two files. The first backup striped file resides on the C drive, and the second backup striped file resides on the D dive. By specifying the backup files on two different drives, you can reduce the time needed for database backups as SQL Server will be able to use multiple threads while performing backups. When you use striped database backups each backup file will be of identical size, helping you to utilize disk spaces more effectively. For example, let's suppose that your full database backup size is 10 GB and you want to break the backup file in such a way that each file is not more than 2 GB each. Then, while performing backup, you can split the backup to be written on 5 different files. Once the backup is completed, you will find that each file is approximately 2 GB in size.

Splitting backup databases
Splitting backup databases

2. Finally, to start the striped backup of the database, click OK in the Back Up Database dialog box. Once the backup is successful, you will see a dialog box similar to the one shown in the screenshot below.

Please note that if you are using SQL Server 2008 Enterprise Edition or SQL Server 2008 R2 or SQL Server 2012 Standard and Enterprise Editions, you can even leverage the built-in Database Backup Compression feature to further reduce the database backup size.

SQL Server management studio
SQL Server management studio

Using the below-mentioned T-SQL code it's possible to perform a full backup of the AdventureWorks2008R2 database by splitting the backups to two files.

BACKUP DATABASE [AdventureWorks2008R2]

               TO          DISK = N'C:\DBBackups\AdventureWorks2008R2_SplitFile1.BAK', 

                              DISK = N'D:\DBBackups\AdventureWorks2008R2_SplitFile2.BAK'

               WITH    NOFORMAT, NOINIT, 

                                             NAME = N'AdventureWorks2008R2-Full Database Backup',

                                             SKIP, NOREWIND, NOUNLOAD, 

                                             STATS = 10

GO

Restore database
Restore database

3. Open SQL Server Management Studio; right-click Databases node and select the Restore Database… option from the drop-down menu as shown in the screenshot below.

4. In the Restore Database dialog box, specify the name of the destination database (for this example, the database will be restored as RestoreDatabaseFromSplitFiles), Specify the Source for the restore under From Device and select the checkbox under Restore as shown in the screenshot below. One important thing to note is you would need both of the database backup files to successfully restore the database.

Restore database from split files
Restore database from split files

5. Finally, to restore the database from the striped backup files click OK in the Restore Database dialog box. Once the restore is successful, you will get to see a dialog box similar to the one shown in the image below.

If you don't specify both split files with which the full backup was performed, you would end up getting the error message displayed below. Hence, you need to make sure both of the split database backup files are secured safely to successfully restore your database in case of a failure.

Error Message

Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Split database restore success

Split database restore success

Restoring a database from multiple files

Using the T-SQL code below, one can restore the database from multiple split files:

RESTORE DATABASE [RestoreDatabaseFromSplitFiles]

               FROM    DISK = N'C:\DBBackups\AdventureWorks2008R2_SplitFile1.BAK', 

                              DISK = N'D:\DBBackups\AdventureWorks2008R2_SplitFile2.BAK'

               WITH 

               FILE = 1, 

               MOVE N'AdventureWorks2008R2_Data'  TO 
N'D:\DB\RestoreDatabaseFromSplitFiles_Data.mdf', 

               MOVE N'AdventureWorks2008R2_Log'  TO 
N'D:\DB\RestoreDatabaseFromSplitFiles_Log.LDF', 

               NOUNLOADSTATS = 10

GO

This was first published in March 2013

Dig deeper on SQL Server Backup and Recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close