OlaHallengren Maintenance Solution – DatabaseBackup – Configurable Backup Directory folder names
If you are a DBA then you understand the importance of having robust Database Maintenance Solution. Like everybody in SQL Server Community, Ola Hallengren’s Maintenance Solution is my favorite.
Two days back, I was deploying OlaMaintenance Solution on our current servers, and wanted all backups to be thrown into BackupDirectory like 'E:\Backups\ServerName_DatabaseName_BackupType_yyyymmdd_hhmmss.bak' . But current code design of OlaMaintenance Solution produces backups following below path pattern:-
|
1 |
'@Directory\ServerName\DatabaseName\BackupType\ServerName_DatabaseName_BackupType_yyyymmdd_hhmmss.Extension' |
In order to achieve the same, I took BrentOzar’s version of Ola Hallengren’s stored procedure [dbo].[DatabaseBackup] from GitHub, and tweaked it little further. One can find the modified code that I am using at below url from my github repository:-
Below are the various ways this procedure can be used now:-
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- Modified By Ajay Dwivedi -- [backupfile] = ServerName_DatabaseName_BackupType_yyyymmdd_hhmmss.bak EXEC [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', --@Directory = 'E:\Backup\**DATABASENAME**', /* Output like 'E:\Backup\DatabaseName\backupfile.bak' */ --@Directory = 'E:\Backup\**DATABASENAME**\**BACKUPTYPE**', /* Output like 'E:\Backup\DatabaseName\BackupType\backupfile.bak' */ --@Directory = 'E:\Backup', /* Output like 'E:\Backup\ServerName\DatabaseName\BackupType\backupfile.bak' */ @Directory = 'E:\Backup\*', /* Output like 'E:\Backup\backupfile.bak' */ @BackupType = 'FULL', @Compress = 'y' |
Friends! I hope this will be helpful to you as well. Feel free to connect with me for any query. If you find the post useful, please do like and share. Happy Coding 🙂
Related Posts
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.
Leave a Reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
pls share clustering,always on
Sorry Venkata. I have only few AO servers in my organization. So, don’t get enough AlwaysOn or Cluster issues to play with 🙂