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
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?