Backup-DbaDatabase : ERROR The length of the device name provided exceeds supported limit (maximum length is:259) when database name is long

[rferraton]

Backup-DbaDatabase : ERROR The length of the device name provided exceeds supported limit (maximum length is:259) when database name is long

[niphlod]

hi @rferraton , are you able to backup manually to the path ?
IMHO It's not a problem of dbatools per se, rather that sql can't have backup paths longer than 259 chars.

[rferraton]

Nope :

BACKUP DATABASE [TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_123456789012345678901234567890] TO  DISK = N'D:\MSSQLBACKUP\MSI\MSSQLSERVER\TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_123456789012345678901234567890\Full\TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_123456789012345678901234567890_FULL_20240506145012.bak' WITH NOFORMAT, NOINIT,  NAME = N'TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_123456789012345678901234567890-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

work :
11 percent processed.
21 percent processed.
31 percent processed.
41 percent processed.
51 percent processed.
61 percent processed.
71 percent processed.
81 percent processed.
91 percent processed.
100 percent processed.
Processed 416 pages for database 'TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_123456789012345678901234567890', file 'TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_123456789012345678901234567890' on file 1.
Processed 1 pages for database 'TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_123456789012345678901234567890', file 'TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_123456789012345678901234567890_log' on file 1.
BACKUP DATABASE successfully processed 417 pages in 0.026 seconds (125.056 MB/sec).

Completion time: 2024-05-06T15:25:35.0713253+02:00

NB : the given path is 229 caracters long : no more than the threashold of 259 returned by the first error.

I will try with a longer path but, for the given case (backup path and filepath) the Backup-DbaDatabase Failed wheareas the SQL Backup (same backup path and file path) is successfull

[rferraton]

After testing with a longer database name i receive a SQL Server Error

BACKUP DATABASE [TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_12345678901234567890123456789012345678901234567890123456789] TO  DISK = N'D:\MSSQLBACKUP\MSI\MSSQLSERVER\TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_12345678901234567890123456789012345678901234567890123456789\Full\TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_12345678901234567890123456789012345678901234567890123456789_FULL_20240506145012.bak' WITH NOFORMAT, NOINIT,  NAME = N'TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_123456789012345678901234567890-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Msg 3057, Level 16, State 1, Line 4
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.
Msg 3013, Level 16, State 1, Line 4
BACKUP DATABASE is terminating abnormally.

[niphlod]

so, not a problem on dbatools, do you agree ?

[rferraton]

No, i don't agree : the problem occurs before the 259 caracters limits

[niphlod]

ahem ... "Path" for sql is the full path, so both directory + filename . the 259 char limit is on the "sum" of both

[rferraton]

agree, but with a 229 caracters long (path+filepath) : SQL is working whereas dbatools Backup-DbaDatabase failed

[rferraton]

Got it : dbatools add the dbname twice in the path ! even if the token already specify dbname !

[rferraton]

My path is ${BackupDirectory}\servername\instancename\dbname\backuptype using -ReplaceInName switch in the Backup-DbaDatabase command. In that case the directory path + filepath length = 229 caracters

Unfortunatly Dbatools use ${BackupDirectory}\servername\instancename\dbname\backuptype\dbname in it's path (which is not the path that was defined). In that case the directory path + filepath length > 259 caracters

[niphlod]

that's just how dbatools composes the directory, it's going to break a lot of code to remove "dbname" from the path

[rferraton]

Hum... the behavior of the -ReplaceInName is "not correct" and is not possible to build a backup directory hierarchy that "centralize" all the backup types of a database under a unique dbname directory. This lead to more complex restore scripts and security concern about backup directories (3 grants instead of 1).

Adding the dbname at the end of backuppath is not silly but why not :
if (ReplaceInName is used -And dbname token in the path) then don't add the dbname directory at the end of path

I do understand your worries about impacts of such a change, why not adding a new parameter in the Backup-DbaDatabase that will avoid to create a dbname subdirectory : -NoDBSubdir for exemple ?

[niphlod]

sorry @rferraton , I'd say this is not a bug anymore but rather an enhancement request ....
I'm not an avid "replaceinname" user, but sticking to the documentation I'd say everything is working as intended ...

"""
If this switch is set, the following list of strings will be replaced in the FilePath and Path strings:
instancename - will be replaced with the instance Name
servername - will be replaced with the server name
dbname - will be replaced with the database name
timestamp - will be replaced with the timestamp (either the default, or the format provided)
backuptype - will be replaced with Full, Log or Differential as appropriate
"""

or is it doing something wrong ?

your example on this issue is

Backup-DbaDatabase .... "D:\MSSQLBACKUP\servername\instancename\ dbname \backuptype" -FilePath "servername_ dbname _backuptype_timestamp.bak" ....

if you have a very long db name, you're using it BOTH in the path AND in the backup filename .

[andreasjordan]

I don't see this as a bug and would like to close the issue.

[rferraton]

Currently, in dbatools Backup-DbaDatabase, the happening of the dbname diverge from the ola hallengren directory "standard" solution. I try to find a solution that will allow Backup-DbaDatabase to "stick" with OH directories.

I agree it is not a "bug" as the documentation never say that if you already specified the dbname in the path, it will still always appending dbname in the end of the path.
But you may also agree with me that it is not "natural" when you define a path that already contains dbname.

May be you can put it in a enhancement request : add a switch that will stop to always append dbname in the directory backup path and strictly respect the given path variable.

[rferraton]

ER : #9346