Comments (16)
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.
Related Issues (20)
- Exclude Multiple tables
- Include the name of the day, for daily differential backups.
- Default Backup Location Folder doesn't exists HOT 1
- Fallback backup location
- ChangeBackuplocation
- Add an option to generate the Task Scheduler commands for SQL Express
- Update Statistics very slow on a 3Tb database HOT 3
- PRINT when TimeLimit has been reached in IndexOptimize HOT 1
- Diff backup of new database created fom another database is not being converted to full.
- (JOBNAME) variable not working on SQL Server 2014? HOT 2
- ColumnStore Index Fragmentation Issue
- Support Veeam Databses backups
- IndexOptimize job randomly reporting failure even when the log file says the Outcome succeeded HOT 6
- running rebuild on individual index takes a long time compared to native SQL commands HOT 2
- Enhance Index Rebuild Decision Process with Page Density Factor
- Parallel processing for index maintenance
- IndexOptimize - wrong ONLINE setting when table contains ColumnStore index
- Clustered indexes
- 7Zip compression
- @Clearuptime does not consider is_copy_only backups
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 sql-server-maintenance-solution.