DatabaseBackup fails to backup certain database names

[DataBeardAdmin]

Description of the issue I ran into an environment with a lot of crazy database names and found that one of the databases fails to validate and cannot be backed up with the DatabaseBackup solution. The database name in question is simply [💩]. Feel free to start laughing as I did. After further investigation, I found the issue was linked to the line: IF @Databases IS NOT NULL AND (NOT EXISTS(SELECT * FROM @SelectedDatabases) OR EXISTS(SELECT * FROM @SelectedDatabases WHERE DatabaseName IS NULL OR DatabaseName = '')) In my tests, I discovered that SELECT CASE WHEN N'💩' = N'' THEN 'Fail' ELSE 'Pass' END results in Fail. I'm wondering if DatabaseName = '' can be replaced with UNICODE(DatabaseName) IS NULL?

SQL Server version and edition I've tested this with test fails on fully patched SQL 2016 & 2019 instances.

Version of the script Version: 2022-12-03 17:23:44

What command are you executing? EXECUTE [dbo].[DatabaseBackup] @Databases = N'💩' (with additional parameters)

What output are you getting? Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 2276 [Batch Start Line 0] The value for the parameter @Databases is not supported.

Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 2276 [Batch Start Line 0] The names of the following databases are not supported: [💩].

Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 2276 [Batch Start Line 0] The documentation is available at https://ola.hallengren.com/sql-server-backup.html.

[srutzky]

The problem is (99.999% certain) that the default collation for the database in which these procs exist is a non-binary, version 80 collation (i.e. one with a name either a) starting with "SQL_", or b) without a version number — 90, 100, or 140). The version 80 collations did not have sort weights for supplementary characters (emojis being among these) as well as many other characters. Characters without sort weights are effectively invisible to sorting and comparisons. Hence, any number of any of these characters have no effect on such operations. For example:

SELECT 1 WHERE N'💩💪' = N'💫' COLLATE SQL_Latin1_General_CP1_CI_AS
-- 1

SELECT 2 WHERE N'💩' = N'' COLLATE SQL_Latin1_General_CP1_CI_AS
-- 2

SELECT 3 WHERE N'💩💪' = N'💫' COLLATE Latin1_General_CI_AS
-- 3

SELECT 4 WHERE N'💩' = N'' COLLATE Latin1_General_CI_AS
-- 4

SELECT 5 WHERE N'💩💪' = N'💫' COLLATE Latin1_General_100_CI_AS
-- nada

SELECT 6 WHERE N'💩' = N'' COLLATE Latin1_General_100_CI_AS
-- nada

For this particular line of code, since the predicate is testing for empty string, it would be best to replace DatabaseName = '' with the following:

DATALENGTH(DatabaseName) = 0

However, there might very well be other lines of code that need fixin’, especially if you have at least one other database also named with only emojis (but those fixes are probably not as simple as this one).

For now, you could try to change the database's default collation to something newer than a version 80 collation (i.e. any collation with a version number in the name), such as: Latin1_General_100_CI_AS_SC.

[olahallengren]

Thank you for reporting this. I have tested the code with case and case-insensitive collations, and also with Chinese characters, but not with emojis. I agree that it should work.

[olahallengren]

@DataBeardAdmin do you still have access to this environment. Could you check the collation of the database where DatabaseBackup is located?

[olahallengren]

This issue has been fixed.

I reproduced the issue using a SQL Server instance with the collation Latin1_General_CI_AS.