olahallengren
Path/File Name Length Issue
Discovered this last week on a server that hosts SharePoint databases (ya know, the one's with the GUIDs in the database name).
The maximum path length check (~Line 2500 [below]) doesn't take into consideration the cluster/AG name when computing if the path will be too long. Since the cluster/AG is used in the path and prefixed to each database name, the length should be counted a second time (it's included in the DirectoryPath by the time this code gets executed.
Here's an example with the actual names replaced to protect the innocent...
- Directory: \the-cifs-share-server.with.fqdn\zone1_sql_prod_cifs\DAILY
- Cluster: longclustername
- AG Name: somerealylongagname
- Database Name: unnecessarily_long_name_a_dba_would_never_make_ever_up_ubuiqiuitoussharepointguidinname
That setup produces this which is 263 characters long: \the-cifs-share-server.with.fqdn\zone1_sql_prod_cifs\DAILY\longclustername$somerealylongagname\unnecessarily_long_name_a_dba_would_never_make_ever_up_ubuiqiuitoussharepointguidinname\FULL\longclustername$somerealylongagname_unnecessarily_long_n..._FULL_20221106_230100.bak
That in turns results in this:
Msg 3057, Sev 16, State 1, Line 1 : Invalid device name. The length of the device name provided exceeds supported limit (maximum length is:259). Reissue the BACKUP statement with a valid device name. [SQLSTATE 42000] Msg 3013, Sev 16, State 1, Line 1 : BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]
SELECT @CurrentMaxFilePathLength = CASE WHEN EXISTS (SELECT * FROM @CurrentDirectories) THEN (SELECT MAX(LEN(DirectoryPath + @DirectorySeparator)) FROM @CurrentDirectories) WHEN EXISTS (SELECT * FROM @CurrentURLs) THEN (SELECT MAX(LEN(DirectoryPath + @DirectorySeparator)) FROM @CurrentURLs) END + LEN(REPLACE(REPLACE(@CurrentDatabaseFileName,'{DatabaseName}',@CurrentDatabaseNameFS), '{FileNumber}', CASE WHEN @NumberOfFiles > 1 AND @NumberOfFiles <= 9 THEN '1' WHEN @NumberOfFiles >= 10 THEN '01' ELSE '' END))
I am currently experiencing this exact issue.
I was thinking of maybe mapping the long directory\folder structure to a drive and then just using the drive letter location but how to do it per use or file?
Also the permissions to create mapped drives from SSMS / code is problematic.
This is a warning message returned by the SQL server and not an issue with the OLA backup script. The limit is set by Microsoft SQL server and cannot be changed. To be able to meet the limit you need to look at options to reduce the length. For example, relocating the backups to a shorter path.
