Tuesday, 29 September 2015 00:00

Splitting SQL Server backups to multiple files

Rate this item
(0 votes)

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?

  • 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.
  • Using the Split File Backup option, you can easily split a very large backup file into multiple files of the same size.
  • 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.
  • 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.
  • 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.
  • 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.

    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

  • Open SQL Server Management Studio; right-click Databasesstrong> node and select the Restore Database… option from the drop-down menu as shown in the screenshot below.
  • 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.
  • 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

    Reference : http://searchsqlserver.techtarget.com/tip/Splitting-SQL-Server-backups-to-multiple-files

Last modified on Tuesday, 29 September 2015 14:29
Data Recovery Expert

Viktor S., Ph.D. (Electrical/Computer Engineering), was hired by DataRecoup, the international data recovery corporation, in 2012. Promoted to Engineering Senior Manager in 2010 and then to his current position, as C.I.O. of DataRecoup, in 2014. Responsible for the management of critical, high-priority RAID data recovery cases and the application of his expert, comprehensive knowledge in database data retrieval. He is also responsible for planning and implementing SEO/SEM and other internet-based marketing strategies. Currently, Viktor S., Ph.D., is focusing on the further development and expansion of DataRecoup’s major internet marketing campaign for their already successful proprietary software application “Data Recovery for Windows” (an application which he developed).

25 comments

  • Comment Link Miguel Wednesday, 10 January 2018 06:34 posted by Miguel

    Hurrah! At last I got a blog from where I be capable of actually get valuable data concerning my study and knowledge.

  • Comment Link Delphia Thursday, 14 December 2017 21:27 posted by Delphia

    This is very interesting, You're a very skilled blogger. I have
    joined your feed and look forward to seeking more of your excellent post.
    Also, I have shared your web site in my social networks!

  • Comment Link Lawrence Wednesday, 13 December 2017 11:44 posted by Lawrence

    Great blog here! Also your site so much up fast! What web host are you the usage of?

    Can I am getting your affiliate hyperlink for your host? I wish
    my website loaded up as quickly as yours lol

  • Comment Link Flor Wednesday, 06 December 2017 00:22 posted by Flor

    Have you ever considered about adding a little bit more than just your articles?
    I mean, what you say is fundamental and all. However just
    imagine if you added some great pictures or video clips to give your posts more, "pop"!
    Your content is excellent but with images and video clips, this blog could certainly be one of the greatest
    in its niche. Superb blog!

  • Comment Link Avis Saturday, 25 November 2017 23:08 posted by Avis

    Hi! I know this is kinda off topic however , I'd figured I'd ask.
    Would you be interested in trading links or maybe guest
    writing a blog article or vice-versa? My website addresses a lot of the same subjects as yours
    and I feel we could greatly benefit from each other.
    If you happen to be interested feel free to send me an email.
    I look forward to hearing from you! Superb blog by the way!

  • Comment Link Fermin Friday, 10 November 2017 19:47 posted by Fermin

    Hi! Someone in my Myspace group shared this site with us so I came to take a look.
    I'm definitely enjoying the information. I'm bookmarking
    and will be tweeting this to my followers!
    Wonderful blog and wonderful style and design.

  • Comment Link Karl Thursday, 09 November 2017 18:07 posted by Karl

    Excellent blog you have here but I was curious about if you knew of any discussion boards that
    cover the same topics discussed in this article?
    I'd really like to be a part of group where I can get comments from other experienced individuals that share the same interest.
    If you have any suggestions, please let me know.
    Cheers!

  • Comment Link Camilla Friday, 03 November 2017 19:50 posted by Camilla

    Hey! Would you mind if I share your blog with my twitter group?
    There's a lot of folks that I think would really enjoy your content.
    Please let me know. Many thanks

  • Comment Link Marilou Wednesday, 01 November 2017 11:24 posted by Marilou

    First of all I would like to say awesome blog! I had
    a quick question in which I'd like to ask if you do not mind.
    I was curious to know how you center yourself and clear your mind prior to writing.
    I've had a tough time clearing my mind in getting my
    ideas out there. I truly do enjoy writing but it just seems like the first 10 to
    15 minutes are generally wasted just trying to figure out how to begin.
    Any ideas or hints? Cheers!

  • Comment Link Corey Wednesday, 01 November 2017 04:57 posted by Corey

    Hello There. I discovered your blog using msn. This is an extremely neatly
    written article. I will make sure to bookmark it and return to learn more of your helpful information. Thanks for the post.
    I will definitely comeback.

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.