SQL Server Backup – How to Make a Database Backup Go Faster?

backupsql serversql-server-2008

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:

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

Best Answer

I think the perfect way to speed the backup process (and restore) of the 1TB database is to take SAN or VMWare snapshots.

Nevertheless, the answer that your potential employer expected is ok, but in some circumstances may give no results at all. For this approach to work you have to have multiple disk arrays. To simplify this idea, just imagine if instead of 1 SSD, you were writing your 5 different backup files to 5 different SSDs in parallel. This is what they had in mind.

Your script is perfectly fine, but if you test it on your laptop, as mentioned earlier, you may get little to no improvement at all. Plus you have to consider other factors, like throughput of a network etc.

A good article to start would be https://www.mssqltips.com/sqlservertip/935/backup-to-multiple-files-for-faster-and-smaller-sql-server-files/

Related Question