Database backup exclude list

Question

I have 100s of SQL instances and there are cases where some databases are excluded from backup in the ola backup job. I am doing an backup audit and want to retrieve the excluded database list in the backup job using sql query. How can I do it ? I can query the commandLog, but bit tricky and may not be 100% accurate.

It would be great if we can put query to exclude databases.

EG:

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES, - (Select ExcludeDBName from DBA.dbo.ExcludeDBList)

OR using a table variable:

Declare @BackupExcludeList Table(DatabaseName varchar(100))

insert into @BackupExcludeList(DatabaseName) Select DatabaseName from DBA.dbo.BackupExcludeList

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES, @BackupExcludeList  = @BackupExcludeList 

Answer 1

You can just build the list beforehand:

DECLARE @DatabaseList nvarchar(max) = N'USER_DATABASES',
        @ExcludeList  nvarchar(max);

SELECT @ExcludeList = STRING_AGG(CONVERT(nvarchar(max), 
       N'-' + ExcludeDBName), N',')
  FROM DBA.dbo.ExcludeDBList;

SET @DatabaseList = CONCAT(@DatabaseList, N',' + @ExcludeList);

EXEC [dbo].[DatabaseBackup] @Databases = @DatabaseList;

You could also do it the other way: instead of USER_DATABASES and then removing excluded databases, just build the list of included databases yourself:

DECLARE @DatabaseList nvarchar(max);

SELECT @DatabaseList = STRING_AGG(CONVERT(nvarchar(max), d.name), N',')
  FROM sys.databases AS d
 WHERE d.database_id > 4 
/* AND other criteria */
   AND NOT EXISTS
   (
     SELECT 1
       FROM DBA.dbo.ExcludeDBList AS ex
      WHERE ex.ExcludeDBName = d.name
   );

EXEC [dbo].[DatabaseBackup] @Databases = @DatabaseList;

This can be more flexible as you have more say on what counts as a user database (in addition to explicit exclusions).