SUMMARY
How the SQL agent handles databases with non-alphanumeric characters in their names
DESCRIPTION
SQL Server allows most non-alphanumeric characters to be used in a database name, but these characters can sometimes cause issues when backing up and restoring the databases. Here are some things to keep in mind.
- Databases whose names contain single or double quotes will not show up in the list of databases that can be backed up.
- When restoring a database, the UI will disallow the use of some non-alphanumeric characters in the database name that are know to cause problems.
- Sometimes, a non-alphanumeric character in a database name will be fine in one place, but not another. For example, having a comma in a database name is fine, unless it is at the end of the database name (see below).
- When restoring a database to an alternate instance, the agent creates a directory path on the SQL Server that includes the SQL instance name and database name. Any non-alphanumeric key sequences that are not legal for directory names in Windows may cause the restore to fail when the agent attempts to create the restore directory structure.
- The SQL Server agent uses VSS for backups and restores. VSS is known to have problems with database names containing some non-alphanumeric character sequences. For example, any database name with a comma at the end, i.e. "MyDatabase," will cause VSS to appear to succeed in restoring the database, when in fact the database does not get restored.
RESOLUTION
Whenever possible, do not use non-alphanumeric characters for database names. If a restore of a database containing non-alphanumeric characters in the name fails in a mysterious way, try changing the name to one that does not contain non-alphanumeric characters.
For proper support, a Database name must match the following rule sets:
1) First character must be one of the following: A standard unicode 3.2 letter, a number, the @ symbol, the # symbol, or an underscore _
2) following characters may be any of the above plus: the $ symbol is also available for further characters, the second character should not be a second @ symbol if the first character was one
3) Embeded spaces (leading, trailing, or in the name) are NOT allowed
4) The database name must not be a reserved keyword in SQL
5) special characters not noted above are not permitted.
LINK TO ADVISORIES
NOTES
Per Microsoft documentation on Database Identifiers, SQL support for naming is limited in specific ways, however, per other documentation, SQL create database code allows databases with names that violate these rules. This can create customer complexity and confusion, especially when relying un underlying APIs or 3rd party services that confirm to the standard identifier rule set. As Unitrends leverages VSS and other Microsoft coded APIs that themselves are limited by datbase identifier rules, unitrends cannot overcome these issues directly. Databases protected by Unitrends must conform to these rules to avoid complication in either backup or recovery.