Comments (15)
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.
from dbatools.
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
from dbatools.
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.
from dbatools.
so, not a problem on dbatools, do you agree ?
from dbatools.
No, i don't agree : the problem occurs before the 259 caracters limits
from dbatools.
ahem ... "Path" for sql is the full path, so both directory + filename . the 259 char limit is on the "sum" of both
from dbatools.
agree, but with a 229 caracters long (path+filepath) : SQL is working whereas dbatools Backup-DbaDatabase failed
from dbatools.
Got it : dbatools add the dbname twice in the path ! even if the token already specify dbname !
from dbatools.
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
from dbatools.
that's just how dbatools composes the directory, it's going to break a lot of code to remove "dbname" from the path
from dbatools.
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 ?
from dbatools.
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 .
from dbatools.
I don't see this as a bug and would like to close the issue.
from dbatools.
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.
from dbatools.
ER : #9346
from dbatools.
Related Issues (20)
- Get-DbaDbBackupHistory - exception on data source length in connection HOT 7
- Issue with creating new directory when Invoke-DbaDbLogShipping. HOT 1
- Find-dbadbunusedindex | write-dbadbtabledata - Incorrect syntax near ')' HOT 7
- Azure Automation Account - issue loading the module HOT 6
- Odd Experience when using Connect-DbaInstance HOT 3
- Test-DbaWindowsLogin add property on account name mismatch HOT 3
- Invoke-DbaQuery's parameter list is oddly formatted. This appears to break the online docs and Get-Help. HOT 9
- Update-DbaInstance can not use secure PS Remoting HOT 1
- dbatools.io / github Slack Invite has expired (again) HOT 1
- Copy-DbaAgentJob fails when trying to copy CDC jobs HOT 3
- New-DbaAgentJobStep -DatabaseUser is not setting the username for the job. HOT 2
- PowerShell 7.4.2 version error with dotnet 8.0.4 and Microsoft.Data.SqlClient assembly HOT 1
- Warning after run Get-Dbatoolsconfig or Find-DbaInstance on Windows Server 2016 HOT 2
- Restore-DbaDatabase with -Continue when Database has Memory Optimized File Group and/or multiple differential_base_lsn HOT 1
- Behavior of `Get-DbaDatabase -Database DoesNotExist` HOT 2
- New-DbaAgentSchedule flags an error with a start time before 10000? HOT 4
- Install-DbaFirstResponderKit is installing version 8.18, which is old HOT 4
- Publish-DbaDacPackage appears to ignore DacFxPath option HOT 1
- write-dbadbdatatable - cannot use connection created by connect-dbainstance HOT 13
- Harmonize the description of the parameter SqlInstance
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from dbatools.