How to Take Backups on a Defined Directory in SQL Server

ola-hallengrensql server

I am using Ola-Hallengren scripts and now I have a situation in which I require to take backups without creating sub-directories for each DB. I need to define a centralized path like C:\Backups for all FULL, DIFF and LOG backups.

Any help would be appreciated.

Best Answer

You just need to change the Directory parameter in the Ola Hellengren script. Go to the job, click the Step --> Edit-->@Directory = 'C:\Backup',

This will be enough.

Adding to your particular requirement, I went into the Store proc and was able to figure out. This took me some time so apologies for that. Please mark this as answer once you are able to complete your task.

Below are the steps to get your required directory :

For getting all backups to just one location, you will need to remove few parameters from the store proc [dbo].[DatabaseBackup]. There will be this part where you set variebles( Look for the comments) -- Set variables
Go ahead and look for the below part :

SELECT ROW_NUMBER() OVER (ORDER BY ID), DirectoryPath + CASE WHEN RIGHT(DirectoryPath,1) = @DirectorySeparator THEN '' ELSE @DirectorySeparator END + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar(max)),'\','$') END + @DirectorySeparator + @CurrentDatabaseNameFS + @DirectorySeparator + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END, Mirror, ROW_NUMBER() OVER (PARTITION BY Mirror ORDER BY ID ASC), 0, 0 FROM @Directories
ORDER BY ID ASC

Now you need to remove the parameters : @CurrentDatabaseNameFS and UPPER(@CurrentBackupType). This will create all your backups to a single location as below

$C:\Backup\LAPTOP-8BATS2T5$

Go through each of the available parameters in Ola hellengren scripts. Ola Hellengren

Related Solutions

SQL Server – Implement Ola Hallengren Backup Plan for Different Databases

No you don't need to install the Ola's solution again since the initially installed commandexecute.sql and databasebackup.sql would have been saved on master DB as SP.

You just need to create a new job , as one created early but this time for 5 left over DB;s and schedule it weekly per you're timings.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',-- Name of those 5 DB's would come here
@Directory = 'C:\Backup',-- You're backup path
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y'

enter image description here You can use above script while creating the T-SQL job and schedule it accordingly.

More details on how to do above refer to schedule job

Also, just to make sure both, initial backup and this newly created backup job not to run on same time you can put a condition or per the analysis when you're daily backup job completes, you can schedule this new weekly/nightly backup job for remaining 5 DB's

SQL Server – Why Take Full Backups When COPY_ONLY is Available?

As long as you don't care that:

in the async model, the REDO queue may fall way behind, so the backups on your secondary may not represent the point in time that you think they would if they were taken on the primary.
you will not be able to use DIFFERENTIAL backups in this model.
in the event of a restore, you will have to manually determine which log backups contain the LSN ranges you'll need to restore on top of the chosen COPY_ONLY backup and, given the above, the timestamps on the bak/trn files may not be accurate enough to determine the set of files and sequence you need.
backup history will not be recorded centrally.

Also, I still think there are going to be scenarios where this ultra-long log chain, or relying on a COPY_ONLY backup taken in between log backups, are going to make it difficult to restore. Personally, I would be keen to perform a full, proper backup on the primary on some regular interval, say once a week. YMMV.

Best Answer

Related Solutions

SQL Server – Implement Ola Hallengren Backup Plan for Different Databases

SQL Server – Why Take Full Backups When COPY_ONLY is Available?

Related Question