At our company we are upgrading our Ola Hallengren script, but we have now run into the problem that the distribution database previous got included in 'DatabaseBackup - USER_DATABASES - DIFF' but now only gets included in 'DatabaseBackup - SYSTEM_DATABASES - FULL'. How can this distribution database still be included in the backup of the diff userdatabases?
This is the code in the latest Ola Hallengren script:
INSERT INTO @Jobs ([Name], CommandTSQL, DatabaseName, OutputFileNamePart01, OutputFileNamePart02)
SELECT 'DatabaseBackup - USER_DATABASES - DIFF',
'EXECUTE [dbo].[DatabaseBackup]' + CHAR(13) + CHAR(10) + '@Databases = ''USER_DATABASES'',' + CHAR(13) + CHAR(10) + CASE WHEN @BackupURL IS NOT NULL THEN '@URL = N''' + REPLACE(@BackupURL,'''','''''') + '''' ELSE '@Directory = ' + ISNULL('N''' + REPLACE(@BackupDirectory,'''','''''') + '''','NULL') END + ',' + CHAR(13) + CHAR(10) + '@BackupType = ''DIFF'',' + CHAR(13) + CHAR(10) + '@Verify = ''Y'',' + CHAR(13) + CHAR(10) + '@CleanupTime = ' + ISNULL(CAST(@CleanupTime AS nvarchar),'NULL') + ',' + CHAR(13) + CHAR(10) + '@Checksum = ''Y'',' + CHAR(13) + CHAR(10) + '@LogToTable = ''' + @LogToTable + '''',
@DatabaseName,
'DatabaseBackup',
'DIFF'
I've tried to put:
@Databases = ''USER_DATABASES, distribution''
But that didn't work. And I've tried to declare a customer variable with the distribution database which also did not seem the work. Does anyone have experience on how to fix this?
If you mean the replication distribution database, you can't, for the last 6 years. That's essentially a system database and is treated this way since 2019.
From the Version History page
10 Jun 2019
Changed so that the Distribution database is considered a system database.
The best option is to adapt to this change.