Backup to Azure backup-share form on-premise server - Ola Hallengren scrips

Question

Post reply

Backup to Azure backup-share form on-premise server - Ola Hallengren scrips

gkramer 23701

Ten Centuries

Points: 1383
More actions
June 10, 2022 at 7:12 am

#4047369

Dear all,
This is the first time cross-platform - and I need some help on the following....
Current situation:
We manage a Azure VM with SQL-server 2017 backing up to a Azure file-share using the Ola Hallengren scripts.
We also manage a on-premise server (SQL 2017) backing up local to one of the disks within this server also using the Ola scrips.
Wanted situation:
Backing up from the on-premise server tot the Azure file-share still using the Ola scripts.
Facing this challenge I tried to re-use the excising Ola script on the on-premise server (using @URL iso @Directory)
EXECUTE [dbo].[DatabaseBackup]
@databases = 'TEST-DATABASE',
@Directory = 'F:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\BACKUP\',     -- locale backup location
@url = 'https://somelocation.file.core.windows.net/server-04/backup',                                        -- Azure backup location
@Credential = 'DOMAIN\SVC-ACCOUNT-04',
@BackupType = 'FULL',
@verify = 'Y',
--@CleanupTime = 74,     -- The value for the parameter @CleanupTime is not supported on Azure Blob Storage.
@checksum = 'Y',
@Compress = 'Y',
@LogToTable = 'Y'
Executing this I encounter the error:
Msg 50000, Level 16, State 4, Procedure dbo.DatabaseBackup, Line 2276 [Batch Start Line 1]
The value for the parameter @Credential is not supported.
Searching for this on the Internet I found:
Starting with SQL Server 2012 (11.x) SP1 CU2 through SQL Server 2014 (12.x), you can only backup to a single device when backing up to URL for Azure Blob Storage. In order to backup to multiple devices when backing up to URL, you must use SQL Server 2016 (13.x) and later and you must use Shared Access Signature (SAS) tokens. For examples creating a Shared Access Signature, see SQL Server Backup to URL and Simplifying creation of SQL Credentials with Shared Access Signature (SAS) tokens on Azure Storage with Powershell.
Is it correct that I cannot use the Ola Hallengren scripts to an Azure File-share but a blob-store only?
Where can I find the SAS token (and must I use the SAS-token in stead of the Domain\SVC account)?
If we use an Azure blob-store the @CleanupTime is invalid - how to delete the files after X-hours?
Please shed some light on to this...
Any information/examples are welcome...
Regards
Guus Kramer
The Netherlands
July 8, 2022 at 9:39 am

This was removed by the editor as SPAM
January 15, 2023 at 11:47 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18853 More actions · Answer 1

How to backup a SQL database to an AZURE storage account using the Ola Hallengren scripts.

gkramer 23701 Ten Centuries Points: 1383 More actions · Answer 2

Thanks Ken,

you pushed me in the right direction !

The main issue it didn't work was the difference between an Azure FILE-SHARE and an Azure BLOB-STORAGE.

I was trying to backup to the FILE-SHARE but that is not allowed using the Ola Hallengren scripts ......

Creating the BLOB_STORAGE solved this......

But now I do have a new challange...

Using the Ola scripts the "@CleanupTime" parameter is not allowed !

So how to solve this one....

I found the next Powershell script

Get-ChildItem –Path "https://blobstorage.blob.core.windows.net/folder/backup" -Recurse | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-30))} | Remove-Item

But this one is not recognize the CREDENTIAL / URL "https://blobstorage.blob.core.windows.net/folder/backup" which I made for backing up to the BLOB-STORAGE (which works fine).

I created an SQL-job to use the CREDENTIALS again but ..... no deletion of the files.

It prompts it does not recognize the HTTP as a path name.

It there another way to delete the files backuped in the BLOB-STORAGE after 30 days????

Regards,

Guus Kramer

Ken McKelvey SSCoach Points: 18853 More actions · Answer 3

Using the Ola scripts the "@CleanupTime" parameter is not allowed !

This is a limitation of the SQL Server BACKUP TO URL and not specifically to do with Ola's scripts.

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-ver16

I suspect the easiest way to delete old backups is to schedule an Azure automation runbook. This might help:

https://social.msdn.microsoft.com/Forums/en-US/5e1bb787-96c3-4965-ab00-3fc7ff0b7694/runbook-to-delete-blob-in-a-container?forum=azureautomation

Ken McKelvey SSCoach Points: 18853 More actions · Answer 4

ps. If you have not used Azure Automation before there is an outline tutorial if you keep following the next steps link in the following:

https://docs.microsoft.com/en-us/azure/automation/overview

This reply was modified 2 years, 6 months ago by Ken McKelvey.

gkramer 23701 Ten Centuries Points: 1383 More actions · Answer 5

Ken,

Thanks for the reply.

I found the Azure Process Automation and created a runbook form one of the templates availabe (deleting files in a blob older the X days).

I modified the Powershell script to test it (set it to 0 (zero) days) but unfortunately not only the Ola-backup files where deleted but the entire folder structure was gone .........

I deleted the runbook and want to start all over again.

Based in this method Im looking for a PS script to delete only the TRN, BAK and LOG files created by Ola's script within all folders downwards starting at the root.

If you can guide me to such a script ...... 🙂

Regards,

Guus

Ken McKelvey SSCoach Points: 18853 More actions · Answer 6

This should be straight forward Powershell. There are plenty of search results like:

https://social.msdn.microsoft.com/Forums/en-US/df73b5a3-d27b-45ee-b440-c1bed99f9b4e/how-to-automate-bak-delete-from-azure-blob?forum=sqlnetfx