SQL Server Maintenance Solution

SQL Server Backup, Integrity Check, and Index and Statistics Maintenance

The SQL Server Maintenance Solution is a set of scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2017, SQL Server 2019, SQL Server 2022, and SQL Server 2025. The solution is based on stored procedures. The solution has been designed for the most mission-critical environments, and it is used in many organizations around the world. The SQL Server Maintenance Solution has won multiple awards in the SQL Server community over the years and it is free.

“The best starting point for building your own maintenance plan is the comprehensive and free script from Ola Hallengren. That’s what I recommend to my clients.” - Paul S. Randal

Getting Started

Download MaintenanceSolution.sql. This script creates all the objects and jobs that you need.

Learn more about using the SQL Server Maintenance Solution:

The minimum required version is now SQL Server 2017. If you're on SQL Server 2008-2016, you can download the last supported version.

The SQL Server Maintenance Solution is available on GitHub.

Sign up for the newsletter to be alerted about updates to the solution.

New, more efficient compression algorithm in SQL Server 2025

Microsoft has introduced a new and more efficient compression algorithm, ZSTD, in SQL Server 2025. DatabaseBackup supports ZSTD.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@CompressionAlgorithm = 'ZSTD',
@CompressionLevel = 'LOW'

SQL Server 2025 also supports non-copy-only full and differential backups on secondary replicas. DatabaseBackup supports this.

Backup to S3 storage

Are you backing up to S3 storage? DatabaseBackup has native support for backup to AWS S3 and other S3-compatible storage.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@URL = 's3://myaccount.s3.us-east-1.amazonaws.com/mybucket',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y',
@MaxTransferSize = 20971520,
@BackupOptions = '{"s3": {"region":"us-east-1"}}'

SQL Server Smart Backup to Multiple Files

Are you backing up to multiple files to improve backup performance? Are you running into the size limitations when backing up to Azure Blob Storage?

DatabaseBackup has support for dynamically determining the number of files.

Here's how it can be used to back up databases of 10240 MB or larger to 8 files, and smaller databases to one file.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@Checksum = 'Y',
@NumberOfFiles = 8,
@MinBackupSizeForMultipleFiles = 10240

Here's how it can be used to dynamically calculate the number of backup files, so that each file gets a maximum of 10240 MB.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@Checksum = 'Y',
@MaxFileSize = 10240

Online Resumable Index Rebuilds

SQL Server and IndexOptimize have support for online resumable index rebuilds. This feature lets you resume an index rebuild if it gets aborted. Here's how to use it:

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@Resumable = 'Y'

SQL Server Smart Differential and Transaction Log Backup

SQL Server has support for checking how much of a database has been modified since the last (non-copy-only) full backup, and how much transaction log has been generated since the last log backup. DatabaseBackup uses this to perform smart differential and transaction log backups.

Here's how it can be used to perform a differential backup if less than 50% of the database has been modified, and a full backup if 50% or more of the database has been modified.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'DIFF',
@ChangeBackupType = 'Y',
@MinModificationLevel = 50

Here's how it can be used to perform a transaction log backup if 1 GB of log has been generated since the last log backup, or if it has not been backed up for 300 seconds. This enables you to do more frequent log backups when there is more activity.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'LOG',
@MinLogSizeSinceLastLogBackup = 1024,
@MinTimeSinceLastLogBackup = 300

SQL Server Backup on Linux

The SQL Server Maintenance Solution supports SQL Server on Linux. Here's how you can use it to back up your databases:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = '/var/opt/mssql/backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@Checksum = 'Y'

Striping of backups to Azure Blob Storage

The SQLCAT team has a blog post about how you can optimize performance when backing up to Azure Blob Storage. You can stripe the backups to multiple files, and use the options MAXTRANSFERSIZE and BLOCKSIZE.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@URL = 'https://myaccount.blob.core.windows.net/mycontainer',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y',
@NumberOfFiles = 8,
@MaxTransferSize = 4194304,
@BlockSize = 65536

Working with Availability Groups

The SQL Server Maintenance Solution allows you to select or exclude availability groups. Here's how it works in DatabaseBackup:

EXECUTE dbo.DatabaseBackup
@AvailabilityGroups = 'AG1',
@Directory = 'C:\Backup',
@BackupType = 'FULL'
EXECUTE dbo.DatabaseBackup
@AvailabilityGroups = 'AG1, AG2',
@Directory = 'C:\Backup',
@BackupType = 'FULL'
EXECUTE dbo.DatabaseBackup
@AvailabilityGroups = 'ALL_AVAILABILITY_GROUPS, -AG1',
@Directory = 'C:\Backup',
@BackupType = 'FULL'

What if you want to select all user databases that are not in availability groups? You can use the AVAILABILITY_GROUP_DATABASES keyword in the @Databases parameter:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES, -AVAILABILITY_GROUP_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL'

It works the same way in DatabaseBackup, DatabaseIntegrityCheck, and IndexOptimize.

Intelligent Index Maintenance

The SQL Server Maintenance Solution lets you intelligently rebuild or reorganize only the indexes that are fragmented. In the IndexOptimize procedure, you can define a preferred index maintenance operation for each fragmentation group. Take a look at this code:

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

In this example, indexes that have a high fragmentation level will be rebuilt, online if possible. Indexes that have a medium fragmentation level will be reorganized. Indexes that have a low fragmentation level will remain untouched.

Update Statistics

The IndexOptimize procedure can also be used to update statistics. You can choose to update all statistics, statistics on indexes only, or statistics on columns only. You can also choose to update the statistics only if any rows have been modified since the most recent statistics update.

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

Solve “No Current Database” Issues

Most DBAs have experienced the error message “BACKUP LOG cannot be performed because there is no current database backup” or “Cannot perform a differential backup for database, because a current database backup does not exist”. These errors usually occur when you have created a new database or changed the database recovery model from Simple to Full. The answer is to determine, before you run the backup, whether a differential or transaction log backup can be performed. You can use the DatabaseBackup procedure’s @ChangeBackupType option to change the backup type dynamically if a differential or transaction log backup cannot be performed.

Here’s an example of how to use the @ChangeBackupType option:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'LOG',
@Verify = 'Y',
@ChangeBackupType = 'Y',
@CleanupTime = 24

Run Integrity Checks of Very Large Databases

The SQL Server Maintenance Solution has been designed to do integrity checks of very large databases. In the DatabaseIntegrityCheck procedure you can choose to do the checks on the database level, the filegroup level, or the table level. It also supports limiting the checks to the physical structures of the database:

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKDB',
@PhysicalOnly = 'Y'

SQL Server Community Awards

The SQL Server Maintenance Solution has been voted Best Free Tool in the SQL Server Magazine Awards:

Feel free to contact me if you have any questions.