Issues with Directory Structure/File Name Extension feature

CommanderBond commented 6 years ago

I just tried to reconfigure backup jobs using the new capabilities for Directory Structure/File Name Extension feature. However, I get "The value for the parameter @CleanupTime is not supported. Cleanup is not supported if the token {BackupType} is not part of the directory." when setting @CleanupTime as before and …

In full backups jobs:

@DirectoryStructure = '{ServerName}${InstanceName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}',
@FileName = '{ServerName}${InstanceName}_{DatabaseName}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}_{BackupType}_{Partial}_{CopyOnly}.{FileExtension}',
@FileExtensionDiff = 'bak'

It doesn't matter if @FileExtensionDiff is provided or not.

In differential backup jobs:

@DirectoryStructure = '{ServerName}${InstanceName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}',
@FileName = '{ServerName}${InstanceName}_{DatabaseName}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}_{BackupType}_{Partial}_{CopyOnly}.{FileExtension}',
@FileExtensionDiff = 'dif'

Expected behavior in folder structure and file names would be: ServerName\DatabaseName\ServerName_DatabaseName_20180524_080000_FULL.bak ServerName\DatabaseName\ServerName_DatabaseName_20180524_080100_DIFF.dif

Resorting the file name parts would ensure correct sort order based on date and time and .bak/.dif file name extensions would be distinct for cleanup procedure xp_delete_file. This is how my folder/file structure looked like when I took my backups via Maintenance Plans that perfectly cleaned up the right file extensions using different cleanup times.

olahallengren commented 6 years ago

Currently there is just a simple check that you have to specify the {BackupType}, if you are using the Cleanup parameter. I will look into improving the logic for this.

For now you have to comment out this check as a workaround.

CommanderBond commented 6 years ago

Thank you Ola!

reinisbz commented 5 years ago

Ola, I know you are busy, but I'm wondering why this check was implemented in the first place? Is it something to do with the logic that was in place to do the clean up in an earlier version or is there a more sophisticated reason why you believe the folder BackupType should be included in the folder structure for most backup setups. Just looking for your 2 cents on it.

olahallengren commented 5 years ago

If you don't have the BackupType as a part of the directory path, there is a risk that a differential backup job is deleting full backups (if they have the same file extension).

This is because of how xp_delete_file works.

reinisbz commented 5 years ago

Got it. I suspected that might be it. Thanks for the info, and all your work on the maintenance solution.

dalekerr commented 5 years ago

Was playing around with my directory structures and got the error that is being discussed here: Cleanup is not supported if the token {BackupType} is not part of the directory.

+1 for me for the ability to specify a value for @CleanupTime while not being forced to have separate directories for each backup type.

alexander7567 commented 5 years ago

I too would like to see the logic behind this change so I can remove old backups without the directory.

fredagstaco commented 5 years ago

@olahallengren How does the script determine which individual files to delete? I know it's based on @CleanupTime , but not exactly how that works. Does it set some property on the file?

olahallengren commented 4 years ago

DatabaseBackup is using xp_delete_file to delete old backup files. (This is the same extended stored procedure that the Maintenance Plans are using.)

xp_delete_file deletes backup files based on a directory, a file extension, and a modified date.

Here is an example:

EXECUTE dbo.xp_delete_file 0, N'D:\Backup\ServerName$InstanceName\DatabaseName\FULL', 'bak', '2020-01-11T16:56:44'

Now let's look at the checks that I have:

Database name in the directory path If the database name is not in the directory path, then it could happen that a backup of one database, is deleting a backup of another database.
Backup type in the directory path If the backup type is not in the directory path, then it could happen that a differential backup job, is deleting full backups. (This can be improved by also considering the file extensions, as @CommanderBond is suggesting.)
Copy_only in the directory path If copy_only is not in the directory path then it could happen that a copy_only full backup, is deleting non copy_only full backups.

alexander7567 commented 4 years ago

Would it be possible to switch this to be recursive as found here? Would that help?

https://stackoverflow.com/questions/24582996/sql-server-xp-delete-file-parameters

From Website: Xp_delete_file take five parameters:

File Type = 0 for backup files or 1 for report files.
Folder Path = The folder to delete files. The path must end with a backslash "\".
File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.
Date = The cutoff date for what files need to be deleted.
Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.

olahallengren / sql-server-maintenance-solution

Issues with Directory Structure/File Name Extension feature #52