DatabaseBackup - configurable folder names about sql-server-maintenance-solution HOT 16 CLOSED

from sql-server-maintenance-solution.

I don't understand why you're coding a solution when it can be specified on the parameter line... If you're concerned that different servers are doing backups, then either centralize the SQL Agent or when creating the jobs for Ola's script, customize value then.

from sql-server-maintenance-solution.

Code is working at the client, so did a pull request for it (above).

from sql-server-maintenance-solution.

Hi BrentOzar

I was in touch with Ola on October 2016 about the backup directory structure. I didn't want to have the $ directory before having a directory per db, and idem for directory by backup type.

He sent to me a "non-production" update of the stored prcedure DatabaseBackup.

It has two new parameters with defaults like this:

@DirectoryStructure nvarchar(max) = '{ServerName}${InstanceName}{DatabaseName}{BackupType}',
@AvailabilityGroupDirectoryStructure nvarchar(max) = '{ClusterName}${AvailabilityGroupName}{DatabaseName}{BackupType}',

If you don't want the server name you could call the stored procedure like this:

@DirectoryStructure = '{DatabaseName}{BackupType}'

As I've no news from Ola since this moment, and that no new version has been release since october 2016 (I hope Ola is well !), I merged these modifications with the latest production package (07 Oct 2016), and I test it since few weeks. For the moment, it seem to be ok.
I use it with @DirectoryStructure = '{DatabaseName}'

Do you want I give you the updated stored procedure ?

from sql-server-maintenance-solution.

@JMFL92 thanks, but I'd rather stick with the public versions on Github. If you want to do your own pull request, that's totally cool. Thanks!

from sql-server-maintenance-solution.

I was wrong in my description. I just updated it.
Ok, I will do that as soon as i've some time ;)

from sql-server-maintenance-solution.

Hi Brent. If the override allows backups from different databases to land in the same folder, xp_delete_file would not be limited to one database. Perhaps server, instance, and database folders are required unless xp_delete_file is replace with code that limits the delete to files from the same server, instance, and database. Because the default is to use the BAK extension for both full and diff, it might mean the backup type is also needs to be a folder. (Is there a reason not to use DIF for diff backups?) A replacement of xp_delete_file could read the info directly from the backup file. If the default filename is not replaced, a replacement could use the filename to filter on server, instance, and database name.

If xp_cmdshell is not enabled, it might be hard to replace xp_delete_file and delete the right files in the procedure.

from sql-server-maintenance-solution.

@drstonephd hmm, I'm confused. Are you saying you want to replace xp_delete_file with some other kind of command? If so, I'm afraid that's well beyond my spare time capabilities, but you're welcome to create an issue and work with Ola to implement that. It's way outside of the scope of this issue, though.

If you were suggesting something else, can you clarify?

from sql-server-maintenance-solution.

Hi. Making the folder structure configurable requires either replacing xp_delete_file or a check to insure the files for retention are isolated. This means we still need all those folders.

from sql-server-maintenance-solution.

OK, cool, if you need that for your use cases, you're welcome to add a separate issue for that. I don't need that for my use cases, so I wouldn't be doing that work. Thanks though!

from sql-server-maintenance-solution.

The tricky thing with having a configurable directory structure is the cleanup.

DatabaseBackup is using xp_delete_file to delete backup files. xp_delete_file deletes backup files based on a directory path, a file extension, and a modified date.

This means that if for example full and differential backups are in the same directory and have the same file extension, then you could have a differential backup job deleting your full backups. There can also be scenarios where you have databases with the same name on multiple instances or servers, backing up to the same directory.

On the other hand, this is one of the most requested features in the backup script.

from sql-server-maintenance-solution.

I have been looking at this some more, and I am leaning towards adding new parameters for the directory structure.

One question with using the existing parameter is that if the user specifies for example C:\Backup, should the stored procedure create the sub-directories (as it would today), or should it back up directly to C:\Backup?

There is also the question how to specify different directory structures, for databases that are in availability groups, and databases that are not in availability groups.

Another thing is that today the directory parameter supports specifying multiple directories with a comma in between (striped backup). If I would use the existing parameter, the user would need to specify the directory structure for each of the directories.

from sql-server-maintenance-solution.

I now have a test version where the directory structure, the file names, and the file extensions are configurable. Please contact me (https://github.com/olahallengren) if you would like to test it.

from sql-server-maintenance-solution.

I now have a public preview version. You can download it here.

from sql-server-maintenance-solution.

I created a branch for this feature, [feature-configurable_directory_structure].

from sql-server-maintenance-solution.

This feature has now been released.
https://ola.hallengren.com/versions.html

Documentation:
https://ola.hallengren.com/sql-server-backup.html#DirectoryStructure
https://ola.hallengren.com/sql-server-backup.html#AvailabilityGroupDirectoryStructure

from sql-server-maintenance-solution.