This is part 1 of 2 part series on taking smart backups.  Part 1 will be taking smart transaction logs in SQL Server 2017, then we will take smart differential/full backups on 2017, and finally, we will take smart differential/full backups on SQL Server 2005 through 2016.

SQL Server 2017 introduced two fields to help with taking smart backups.  One was for taking smarter log backups, for this have DMV sys.dm_db_log_stats that have two fields log_backup_time and log_since_last_backup_mb.  With the combination of these two fields, we can put some logic in the jobs that I use for Ola’s scripts that use my config tables from my Github repository.  To support this change we will be added three new fields to the DatabaseBackupConfig table:

• SmartBackup
• LogBackupTimeThresholdMin
• LogBackupSizeThresholdMB

If you are already using the solution on GitHub you can use the following code to add the new columns plus the on needed for smart differential and full backups we will discuss in just a minute:

ALTER TABLE dbo.DatabaseBackupConfig
ADD SmartBackup CHAR(1) NOT NULL CONSTRAINT DF_DatabaseBackupConfig_SmartBackup DEFAULT 'N',
LogBackupTimeThresholdMin TINYINT NULL,
LogBackupSizeThresholdMB SMALLINT NULL
GO

Next, if you want to run on smart log backups for your 2017 instances you can run this code against all your servers just change the number of minutes you want to wait between log backups and the size you want to allow your transactions to grow to before taking a log backup.  In my case, we are waiting 60 minutes between backups or for 1 GB of changes to build up.

DECLARE @MajorVersion SQL_VARIANT 
SELECT @MajorVersion = SERVERPROPERTY('ProductMajorVersion')
IF (@MajorVersion >= 14)
BEGIN
UPDATE dbo.DatabaseBackupConfig SET SmartBackup = 'Y' 
WHERE BackupType IN ('LOG')

UPDATE dbo.DatabaseBackupConfig SET LogBackupTimeThresholdMin = 60, 
LogBackupSizeThresholdMB = 1024 
WHERE BackupType IN ('LOG')
END

Now let’s take a look at part a look at the logic in the code that allows for the smart log backups:

IF (@MajorVersion >= 14) AND (@SmartBackup = ''Y'')
BEGIN
SELECT @DBInclude = COALESCE(@DBInclude + '','','''') + d.Name 
FROM sys.databases d
CROSS APPLY sys.dm_db_log_stats(d.database_id) dls
WHERE (dls.log_backup_time  <= DATEADD(MINUTE, @LogBackupTimeThresholdMin * -1, GETDATE()) OR dls.log_backup_time IS NULL) OR dls.log_since_last_log_backup_mb >= @LogBackupSizeThresholdMB 
OR d.name NOT IN (
SELECT d.name
FROM sys.databases d
CROSS APPLY sys.dm_db_log_stats(d.database_id) dls
WHERE d.database_id > 4;)
AND d.database_id > 4;
END
ELSE
BEGIN
SELECT @DBInclude = CONCAT(COALESCE(@DBInclude + '','',''''), name) 
FROM sys.databases d
WHERE d.database_id > 4;
END

So between getting all the variables from our configuration table and calling the stored procedure, we check to be sure we are at the version of SQL Server that supports smart backups and that we want to use smart backups on this system.  Then we select all databases where the log backups have not been table taken in the specified threshold in the table.  Then we concatenated together all the databases that have reached the size threshold as well.  If you are not on SQL Server 2017, everything operates as normal.

Get the complete code for the job at GitHub.

NEXT: We will setup smart backups for differential backups in 2017.