search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.91k stars 756 forks source link

New errors in DatabaseBackup script #829

Closed DGWright61 closed 2 weeks ago

DGWright61 commented 2 weeks ago

I have been running this script without fail for about 2 years. I am now getting an error about parameters. (If this isn't actually a bug, please forgive me)

SQL Server version and edition Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) Mar 19 2021 19:41:38 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Version of the script 2024-10-27 11:48:54

What command are you executing?

EXEC dbo.DatabaseBackup
  @Databases = 'DB1,DB2',
  @Directory = '\\RemoteShare\Utilities\WIMS',
  @BackupType = 'FULL',
  @DirectoryStructure = NULL,
  @FileName = '{DatabaseName}_{BackupType}_{Year}{Month}{Day}.{FileExtension}',
  @Verify = 'N',
  @CleanupTime = 4320,
  @CopyOnly = 'Y',
  @Compress = 'Y';

What output are you getting?

Date and time: 2024-11-01 08:58:50
Server: MYDBSERVER
Version: 13.0.5888.11
Edition: Standard Edition (64-bit)
Platform: Windows
Procedure: [DBATools].[dbo].[DatabaseBackup]
Parameters: @Databases = 'DB1,DB2', @Directory = '\\RemoteShare\Utilities\WIMS', @BackupType = 'FULL', @Verify = 'N', @CleanupTime = 4320, @CleanupMode = 'AFTER_BACKUP', @Compress = 'Y', @CopyOnly = 'Y', @ChangeBackupType = 'N', @BackupSoftware = NULL, @CheckSum = 'N', @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @MinBackupSizeForMultipleFiles = NULL, @MaxFileSize = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = 'N', @EncryptionAlgorithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @NoRecovery = 'N', @URL = NULL, @Credential = NULL, @MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = 'AFTER_BACKUP', @MirrorURL = NULL, @AvailabilityGroups = NULL, @Updateability = 'ALL', @AdaptiveCompression = NULL, @ModificationLevel = NULL, @LogSizeSinceLastLogBackup = NULL, @TimeSinceLastLogBackup = NULL, @DataDomainBoostHost = NULL, @DataDomainBoostUser = NULL, @DataDomainBoostDevicePath = NULL, @DataDomainBoostLockboxPath = NULL, @DirectoryStructure = NULL, @AvailabilityGroupDirectoryStructure = '{ClusterName}${AvailabilityGroupName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}', @FileName = '{DatabaseName}_{BackupType}_{Year}{Month}{Day}.{FileExtension}', @AvailabilityGroupFileName = '{ClusterName}${AvailabilityGroupName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}', @FileExtensionFull = NULL, @FileExtensionDiff = NULL, @FileExtensionLog = NULL, @Init = 'N', @Format = 'N', @ObjectLevelRecoveryMap = 'N', @ExcludeLogShippedFromLogBackup = 'Y', @DirectoryCheck = 'Y', @StringDelimiter = ',', @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @LogToTable = 'N', @Execute = 'Y'
Version: 2024-10-27 11:48:54
Source: https://ola.hallengren.com

Msg 50000, Level 16, State 5, Procedure DatabaseBackup, Line 2283 [Batch Start Line 2]
The value for the parameter @CleanupTime is not supported. Cleanup is not supported if the token {DatabaseName} is not part of the directory.

Msg 50000, Level 16, State 6, Procedure DatabaseBackup, Line 2283 [Batch Start Line 2]
The value for the parameter @CleanupTime is not supported. Cleanup is not supported if the token {BackupType} is not part of the directory.

Msg 50000, Level 16, State 7, Procedure DatabaseBackup, Line 2283 [Batch Start Line 2]
The value for the parameter @CleanupTime is not supported. Cleanup is not supported if the token {CopyOnly} is not part of the directory.

Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 2283 [Batch Start Line 2]
The documentation is available at https://ola.hallengren.com/sql-server-backup.html.
Stiffboard commented 2 weeks ago

Looks like you are putting backup type in filename instead of folder. Error message is quite clear. Cleanup parameter need a folder name, put at least backup type and database name in Directory instead of null

olahallengren commented 2 weeks ago

Have you been doing any changes to the parameters recently?

Did the errors start when you updated to the 2024-10-27 - version? Which version were you running before?

olahallengren commented 2 weeks ago

I can add that the errors are by design.

If the database name is not in the directory path, it can happen that the cleanup for one database is deleting backup files for other databases.

If the backup type is not in the directory path, it can happen that the cleanup for a differential backup is deleting full backup files.

This is because of how xp_delete_file works: xp_delete_file is the extended stored procedure that DatabaseBackup (and the maintenance plans) use to delete backup files. xp_delete_file deletes backup files based on a directory, a file extension, and a modified date.

I have checked and the code has been the same since the @DirectoryStructure parameter was introduced 2018-05-23.

DGWright61 commented 2 weeks ago

I should add that I am running this in a SQL Agent job. It's been running for two years without fail and no change. However, your second comment does make sense. Perhaps my CleanupTime had not expired until just now thus revealing the issue. Unfortunately, this is an unusual case because the backup files for both databases need to be in the same folder so the vendor can pick them up to move offsite. Guess I'll need to find a different way to accomplish this particular task.

olahallengren commented 2 weeks ago

I should add that I am running this in a SQL Agent job. It's been running for two years without fail and no change. However, your second comment does make sense.

Maybe there had been some customization in your old version?

Perhaps my CleanupTime had not expired until just now thus revealing the issue.

This should not affect things.

Unfortunately, this is an unusual case because the backup files for both databases need to be in the same folder so the vendor can pick them up to move offsite. Guess I'll need to find a different way to accomplish this particular task.

The easiest is if you search for the error messages in the code and comment out these checks.

DGWright61 commented 2 weeks ago

I haven't customized your code. I really don't want to do that. Just use it as is. I'm using it to backup other databases in the same SQL instance. I'll figure out another way to do this particular task.