How can I make a database backup go faster? I recently attended an interview for my next job and was asked this:

How can I speed up the backup of a database sized close to 1TB or 800 GB in SQL Server?

My Answer: We can use Database Compression or use a third-party tools like Light Speed.
But they expected this answer which I was not aware of:

Solution : Increase throughput by adding more destination files

Example
I created this sample script by adding more backup destination files:
DBCC TRACEON (3605, -1) 
DBCC TRACEON (3213, -1) 

BACKUP DATABASE [AdventureWorks2008] TO 
    DISK = N'E:\ADWBackup\AdventureWorks2008-Full.bak',
    DISK = N'E:\ADWBackup\AdventureWorks2008-Full-File2.bak', 
    DISK = N'E:\ADWBackup\AdventureWorks2008-Full-File3.bak'
WITH 
    NOFORMAT, INIT, NAME = N'AdventureWorks2008-Full Database Backup', 
    SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 

DBCC TRACEOFF(3605, -1) 
DBCC TRACEOFF(3213, -1)

Can you please answer the below questions:

What is the use of following this method instead of using a single file?
Will there be any time difference if I do like this?
How can I restore the DB using the multiple split files?
Will it really increase the speed of the backup?
Theory

Multiple targets for the backup can be very useful, but only in certain situations.


Your source database can be read faster than your current single file backup can write.
Your backup targets for each database are on separate spindles.
You aren't using shared SAN HBA or iSCSI or other bandwidth between the SQL Server instance and the media.
i.e. the IOPS from writing Backup File A DO NOT use the same disks as writing Backup File B.


If all of these are true, then it's possible that some degree of parallelism (many output files) will increase the speed.  If all of these are not true, more than likely you'll cause one or more sets of disks to thrash, and your multi-file backups will actually be slower and also may cause OS filesystem or storage level fragmentation, because you're writing Backup File A and Backup File B at the same time!

Note that many of the same things apply that I covered in my answer to Transaction Log Backups Serial or Parallel?, since this is about writing in parallel (though not necessarily reading, depending on how your filegroups and files are laid out).



Experiment

To really understand this, you may need to see about borrowing some equipment from close friends.


Obtain one (1) fast solid state disk


Any of the good SATA2 or SATA3 or mSATA Samsung, Crucial, Mushkin, etc. internal drives will do
You need to ensure that you can read faster than you can write.

Beg, borrow, or buy at least two disk drives in addition to the drive your SQL Server data and log files are on.


One (1) of these can be a USB2 external drive (or a USB3 drive) plugged into a USB2 port
One (1) of these can be a USB2 external drive plugged into a USB3 port


We can be reasonably sure the USB2 and USB3 ports aren't sharing a bus different controllers
You can't put a USB3 drive into a USB3 port here, because we WANT our backup devices to be slow individually, but not contend with each other for bus bandwidth

One or more drives can be SATA spinning disks

Log into your local SQL Server Developer or SQL Server Express instance


Create a reasonably big database with data and log files on the fast SATA SSD.
Now try backing up twice in a row (two BACKUP DATABASE commands in the same SQL batch) to each backup device, one device at a time
Record the speed of backup and verify on only the second backup to each, i.e. after caches have been filled.
Now you know how fast each is by themselves; one is likely better than the others.
Now try backing up twice in a row (two BACKUP DATABASE commands in the same SQL batch) to ALL backup devices at once (i.e. with multiple DISK arguments)



See the speed difference?

Now play around with BUFFERCOUNT, MAXTRANSFERSIZE, and if you're on developer edition, COMPRESSION.

ALWAYS use the CHECKSUM option!