SQL Server: Why is the backup speed so slow?

Question

I have a backup query like this:

BACKUP DATABASE @temp_baza TO DISK = @temp_bak
BACKUP LOG @temp_baza TO DISK = @temp_log

It is run by sqlcmd like this:

sqlcmd -l 120 -S %SQL_SERVER% -i %KOPIA_KATALOG%backupPELNY.sql 
       -o %KOPIA_KATALOG%output_PELNY.txt -v NAZWA_BAZY="%NAZWA_BAZY%" 
       -v KOPIA="%PELNY_KOPIA%\"
       

In output_PELNY.txt I have this kind of results:

BACKUP DATABASE successfully processed 645127 pages in 2819.651 seconds (**1.787 MB/sec**).

or

BACKUP DATABASE successfully processed 26338 pages in 227.348 seconds (**0.905 MB/sec**).

Main DB is on one Disc, backup is on second disk.

When I use Explorer to copy files between these two disks, I get a transfer speed of approximately 100 MB/s.

QUESTION:

Why is the backup speed so slow - I mean less than 3MB/s?

REMARKS:

• Windows 2012 Essential + SQL Server 2008 R2 EXPRESS
• Intel XEON E3-1270 v3 + 16GB RAM

Answer 1

To solve your problem, please refer to the following links:

1. Options to Improve SQL Server Backup Performance
2. Is your SQL Server backup running slow? Here’s how you can speed it up
3. Super-Fast Backup and Restore Throughput for SQL Server
4. MS SQL Server backup optimization
5. How to Make SQL Server Backups Go Faster

Below are some sample queries for download

To solve the problem, I suggest that the following part of the query:

backup database DBNAME to disk='C:\1\DBNAME.bak' with buffercount=16 ,maxtransfersize=4194304