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:-

'@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:-

https://github.com/imajaydwivedi/SQLDBA-SSMS-Solution/blob/master/OlaHallengren.com/DatabaseBackup.sql

Below are the various ways this procedure can be used now:-

--	Modified By Ajay Dwivedi

-- [backupfile] = ServerName_DatabaseName_BackupType_yyyymmdd_hhmmss.bak

EXEC [dbo].[DatabaseBackup]	
		@Databases = 'USER_DATABASES', 

		[email protected] = 'E:\Backup\**DATABASENAME**', 
			/* Output like 'E:\Backup\DatabaseName\backupfile.bak' */

		[email protected] = 'E:\Backup\**DATABASENAME**\**BACKUPTYPE**', 
			/* Output like 'E:\Backup\DatabaseName\BackupType\backupfile.bak' */

		[email protected] = '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 🙂

2 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.