Backup & Restore

SQL Server database backup into multiple files

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.

backup-multiple-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.

Advertisements

One thought on “SQL Server database backup into multiple files

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s