Backups taking extreme amount of time

timnjoan33

Up until about a month ago, our nightly backups of a 215gb db were taking around 2.5 to 3 hours. Since then, they have taken anywhere from 8 to 18 hours.
I have tried backing up locally and to a network share and the times remain from 8-18 hours. I have also moved the backup times around to make sure it's not colliding with another process on the server.
I checked with our netadmin people and there have been no hardware changes to the server or nic.
I'm using version 3.0.0.3.

Any ideas?

HJoyce

I'd definitely recommend you upgrade to the latest version of SQL Backup. There have been performance enhancements to the speed of the backup since version 3.

It's not a free upgrade, but you'd get a new 14 day trial if you wanted to check out how much faster your backups are.

petey

The backup process comprises of 3 distinct phases: SQL Server reading data from disk, SQL Backup compressing the data, and SQL Backup writing the data out to disk.

We can measure the throughput of SQL Server reading the data from disk by running a backup using SQL Backup with the NOCOMPRESSWRITE option e.g. (note that I'm using the command line interface as I will then be able to cancel the process)

sqlbackupc -sql "BACKUP DATABASE pubs TO DISK = 'e:\temp\pubs.sqb' WITH INIT, NOCOMPRESSWRITE"

We can then measure the throughput when compression is added, with the NOWRITE option e.g

sqlbackupc -sql "BACKUP DATABASE pubs TO DISK = 'e:\temp\pubs.sqb' WITH INIT, NOWRITE"

In your case, if you have a smaller database that is also experiencing a slowdown in backup speed, it might be easier to test using that database instead of the 245 GB one.

You need not wait for the backups to complete to get a measure of the throughput. SQL Server provides a performance counter (SQLServer:Backup Device) that will monitor the throughput in real-time. Note that you can only select an instance when the backup is running. By using the command line interface, you can stop the backup anytime if you feel you've obtained enough measurements.

If testing the NOCOMPRESSWRITE option on a single processor/core machine, you might need to set the backup to run at a low priority, otherwise Performance Monitor might not be able to display the performance counter for selection. To run at a low priority, use the THREADPRIORITY option e.g.

sqlbackupc -sql "BACKUP DATABASE pubs TO DISK = 'e:\temp\pubs.sqb' WITH INIT, NOCOMPRESSWRITE, THREADPRIORITY = 2"

VERY IMPORTANT NOTE: If you allow the backups to complete using the NOCOMPRESSWRITE or NOWRITE options, the backup is assumed to be made by SQL Server. If you made a full backup, the next differential backup will be made based on changes to this 'virtual' full backup. Always make a physical backup after using the NOCOMPRESSWRITE or NOWRITE options. This does not apply to version 4.5 and above.