dataplat
Backup-DbaDatabase : ERROR The length of the device name provided exceeds supported limit (maximum length is:259) when database name is long
Verified issue does not already exist?
I have searched and found no existing issue
What error did you receive?
[Backup-DbaDatabase] Backup of [TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_123456789012345678901234567890] failed | Microsoft.Data.SqlClient.SqlError: 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.
Steps to Reproduce
# provide your command(s) executed pertaining to dbatools
Backup-DbaDatabase -SqlInstance localhost -Type Full -CompressBackup -Checksum -Verify -FileCount 1 -Path "D:\MSSQLBACKUP\servername\instancename\dbname\backuptype" -FilePath "servername_dbname_backuptype_timestamp.bak" -TimeStampFormat "yyyyMMdd_HHmm" -ReplaceInName -CreateFolder -EnableException -Database TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_123456789012345678901234567890
# rename a test database to TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_123456789012345678901234567890
# same problem even when not using replaceinname
Backup-DbaDatabase -SqlInstance localhost -Type Full -CompressBackup -Checksum -Verify -FileCount 1 -Path "D:\MSSQLBACKUP\MSI\MSSQLSERVER\TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_123456789012345678901234567890\Full" -FilePath "TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_123456789012345678901234567890_FULL_20240506145012.bak" -TimeStampFormat "yyyyMMdd_HHmm" -CreateFolder -EnableException -Database TESTDATABASE_ABC_1eed02d4-f58b-4110-9cee-78c2fa65123a_123456789012345678901234567890
Please confirm that you are running the most recent version of dbatools
2.1.14
Other details or mentions
Error happen with the database name is long. i think about a problem with a GetFullPath
What PowerShell host was used when producing this error
Windows PowerShell (powershell.exe)
PowerShell Host Version
Name Value
PSVersion 5.1.22621.2506
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.22621.2506
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
SQL Server Edition and Build number
Microsoft SQL Server 2022 (RTM-CU12-GDR) (KB5036343) - 16.0.4120.1 (X64) Mar 18 2024 12:02:14 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Home 10.0 <X64> (Build 22631: ) (Hypervisor)
.NET Framework Version
PSChildName Version
Client 4.8.09032 Full 4.8.09032 Client 4.0.0.0
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.
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
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.
ahem ... "Path" for sql is the full path, so both directory + filename . the 259 char limit is on the "sum" of both
agree, but with a 229 caracters long (path+filepath) : SQL is working whereas dbatools Backup-DbaDatabase failed
Got it : dbatools add the dbname twice in the path ! even if the token already specify dbname !
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
that's just how dbatools composes the directory, it's going to break a lot of code to remove "dbname" from the path
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 ?
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 .
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.