Along with the increasing of data on user database, it takes more and more time to finish a backup task. I wish to get my backup task running faster. I found out here that there is a way to make this possible by writing backup to multiple files. The other advantages to create backup into multiple files is that the backup process can run using multiple threads and will finish faster as well as having much smaller files that can be easily moved over network or copied to optical media. If we also have multiple disk array then we can write backup files to different array and will get better I/O throughput.
There are two different ways to write backup into multiple files The first one is using similar T-SQL command and using SQL Server Management Studio.
Backup into multiple files using T-SQL Command :
The following T-SQL Command write database backup into five different files.
USE master GO BACKUP DATABASE AdventureWorks TO DISK='F:\SQLDatabaseBackup\AdventureWorks01.bak', DISK='G:\SQLDatabaseBackup\AdventureWorks02.bak', DISK='H:\SQLDatabaseBackup\AdventureWorks03.bak', DISK='I:\SQLDatabaseBackup\AdventureWorks04.bak', DISK='J:\SQLDatabaseBackup\AdventureWorks05.bak' WITH INIT, NOUNLOAD, NAME = 'AdventureWorks Backup', NOSKIP, STATS=10, NOFORMAT GO
Backup into multiple files using SQL Server Management Studio :
From object explorer, right click on database name, point to Tasks, and then click on Back Up… On backup database page, just click Add button to add additional output backup files.
That’s all I have to do to write my backup database into multiple files. On next article I will write how to restore database from multiple different backup files.