Split SQL Server Full backup in multiple files for faster backup

Split SQL Server Full backup in multiple files for faster backup

I had a situation yesterday to download one full database backup from the data-center to our local network. I have taken compressed backup of our database which was around 90 GB and download 90 GB over internet is still a big thing. I had no other choice but to download only.

We have our own FTP between our data-center and our office and I had to use it only. There is a chance that after downloading few GBs If I get a single packet loss in internet, I would have to start from zero all over again. I was not in situation to install DAP or this kind of software which provides “resume” service if downloading gets stopped due to any reason.

So, I have decided to split backup files in 500MB chunk so at any give time, If I lost internet connection, I loss maximum 500MB data and I can afford to start it again. Think If I start downloading of 90 GB and internet lost for few seconds after I completed downloading of 85GB, I would not be in position to spend that much time again.

Splitting backup in multiple files is not only convenient for storing and transferring but it is faster as well. Let us see how we can take SQL Server backup in multiple files.

Herewith, I am going to take full backup of Adventureworks2012 database in one .BAK file only.

SET STATISTICS TIME ON
BACKUP DATABASE AdventureWorks2012
TO DISK = 'E:\SQL2012DataFiles\Adventureworks2012.bak'
SET STATISTICS TIME OFF
GO

Let us see how much time SQL Server has taken to get this backup in one file.

SingleBackup

Now let us take backup in multiple files:

SET STATISTICS TIME ON

BACKUP DATABASE AdventureWorks2012
TO
DISK = 'E:\SQL2012DataFiles\Adventureworks2012_1.bak'
,DISK = 'E:\SQL2012DataFiles\Adventureworks2012_2.bak'
,DISK = 'E:\SQL2012DataFiles\Adventureworks2012_3.bak'

SET STATISTICS TIME OFF
GO

I have taken backup of Adventureworks2012 database in three files, here is the statistics.

MultipleBackup

You can see backup in multiple file is faster then previous attempt. However, you can’t see major difference as this database is small, you can see major difference if you are having database with few GBs.

Now, it would be interesting to see, how we can restore database from multiple files. Here is the script for the same:

USE [master]
GO

RESTORE DATABASE [AdventureWorks2012]
FROM
DISK = N'E:\SQL2012DataFiles\AdventureWorks2012_1.bak'
, DISK = N'E:\SQL2012DataFiles\AdventureWorks2012_2.bak'
, DISK = N'E:\SQL2012DataFiles\AdventureWorks2012_3.bak'
WITH FILE = 2, NOUNLOAD, STATS = 5
GO

some more reading about backup & restore:

Find database backup history in SQL Server (Click Here)
Find database restore date in SQL Server (Click Here)
Backup SQL Server database with encrypted data column/table and restore it somewhere else (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

5 thoughts on “Split SQL Server Full backup in multiple files for faster backup

  1. Pingback: Mirror backup to take full backup at multiple location in SQL Server

  2. Jeet

    Hello,

    When we use split backup, I noticed the size of the backup is equally shared among the number of split we use. Is there a way to control the size of split backup files.

    E.g., I have a DB which is about 1.8 TB and I’m splitting backup of DB in 3 files [backup],[backup1],[backup2]. Now split backup files are approx 600 GB each (please note all 3 split backup files are on 3 different drives).

    I want [backup] file to be 1 TB and [backup1],[backup2] be divided among the rest.

    Is there something we can do to achieve this..

    Regards,
    Jeet

Comments are closed.