Analyze and Improve SQL Server Backup Schedule

By: Aakash Patel   |   Updated: 2019-11-22   |   Comments   |   Related: > Backup

Problem

A centralized backup server to support SQL Server can sometimes become a resource bottleneck in the backup stream if you have many servers writing database backups as the same time. It usually happens when you have a large SQL Server database inventory and all of your database servers have similar backup schedules. In this tip, we will look into SQL Server backup schedules and how you can use this to reduce backup server contention and possibly making SQL Server backups faster by simply rescheduling backup jobs.

Solution

It is considered a best practice to have a separate backup server from where your database files reside to avoid a single point of failure. When you have a large inventory of SQL Server database servers and all of them are backing up at or around the same time, it may overwhelm the backup server and become sluggish. In turn, backups will run for a longer period and will likely impact transaction log backups and other processing on the server during that time window. If transaction log backups take longer, the transaction log can grow and become large and possibly fill up the drive on the server and may not comply with your RPO agreement either.

If you don’t have a very strict maintenance window, you can stagger the backup operations and spread them out across weekdays and times. You can use the below script to audit the full backup schedule. The script is created in the context to run it on CMS to get a nice overview of backup schedule for the entire server inventory. The query result should give you a clear understanding of how to stagger the backup jobs to reduce overlapping time windows.

Potentially, spreading out backup operations will make backups faster across the board. In our environment, we were able reduce backup duration by 30-50% on servers with large and lots of database(s) by simply avoiding an overlapping schedule. You can use the same script below to monitor your SQL Server differential backups schedule with a minor update of changing the code below from this bs.type to 'd' to this bs.type to 'i'.

The below script will also inform you about databases with uncompressed backups. To use compressed backups, make sure to turn on the server level configuration ‘backup compression default’ if it was missed during server setup. Backup compression has been around for since SQL Server 2008 and is available in both Standard and Enterprise editions since SQL Server 2008 R2.

Here is the script to run.

SELECT
  bkup.compressed,
  COUNT(*) AS total_dbs,
  bkup.[weekday],
  DATEDIFF(MINUTE, MIN(bkup.backup_start_date), MAX(bkup.backup_finish_date)) AS duration_minutes,
  AVG(speed_mb_sec) avg_mb_sec,
  MIN(bkup.backup_start_date) AS backup_began,
  MAX(bkup.backup_finish_date) AS backup_finished
FROM (SELECT
  DATENAME(WEEKDAY, bs.backup_start_date) AS [weekday],
  bs.backup_start_date,
  bs.backup_finish_date,
  speed_mb_sec = (bs.compressed_backup_size / 1048576.0) /
  CASE
    WHEN DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) > 0 THEN DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)
    ELSE 1
  END,
  CASE
    WHEN (bs.backup_size % bs.compressed_backup_size) > 0 THEN '1'
    ELSE '0'
  END [compressed],
  RANK() OVER (PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC) AS rank
FROM msdb..backupset bs (NOLOCK)
WHERE 1 = 1
AND bs.type = 'd' --full backups
AND bs.is_copy_only = 0
AND bs.database_name NOT IN ('master', 'msdb', 'model') --excludes server with no user dbs
AND bs.backup_start_date > DATEADD(DAY, -7, GETDATE()) --backups in last one week
--and datename(weekday,bs.backup_start_date) = 'Monday' -- filter by day
) bkup
WHERE bkup.rank = 1 -- latest full backup 
GROUP BY bkup.compressed,
         bkup.[weekday];

Here is sample output.  This shows the last time full backups were run on the server.  I shows how many backups are compressed, number of backups, overall time for the backups and when the backups started and finished.

Next Steps

• Try splitting backups into multiple files to make it faster
  • Backup to multiple files for faster and smaller SQL Server files
  • SQL Server Script to Automatically Split Database Backups into Multiple Backup Files
• Explore native BACKUP options such as MAXTRANSFERSIZE, BUFFERCOUNT, etc.
  • Options to Improve SQL Server Backup Performance

About the author

Aakash Patel is a Senior SQL Server DBA for a software firm in Connecticut with 10+ years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips