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.
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.
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)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.