1

I am trying to have a copy of a transactional database on another server restored once a day for reporting purposes. SQL Server is the instance.

Original database is a member of an Availability Group. We can't extend the Availability Group. Full backups are taken once a day using Ola Hallengren scripts. I've tried using Log Shipping from Primary but it breaks Ola's backup job, so we can't use it.

Backups are sent to a blob container which has subfolders reflecting Ola's directory structure.

How to download just the latest .bak file from the directory in the blob and restore it over existing database? That process should be running in a SQL job?

Thank you for your help!

Improve this question

3 Answers 3

Reset to default
3

you can just use dbatool's (Powershell cmdlets) Restore-DbaDatabase to do the automation that you want. This - Restoring Backups from Azure Blob with dbatools will get you started.

Alternatively, you can also use tsql based solution sp_restoregene

Improve this answer
1
  • Thank you for your help. Getting more focused on dbatools helps :-)
    – TheNixon
    Feb 14, 2022 at 9:43
2

You should still be able to do Log Shipping, and it's the route I'd probably still try to go if I were you and I was unable to extend the Availability Group. All log shipping is at its simplest from is a Log backup that was taken on one instance and restored to another. Remember that Log backups are basically incremental backups, so you'll need every backup sequentially in the correct order to continue restoring on the target. Where people often run into issues is that they turn on Log Shipping, but keep taking backups with their original backup jobs. Thus, breaking their log chain.

You have a few options...

  1. Stop taking Log Backups with Ola's scripts on the specified database.
  2. Continue using Ola's script, and use those Logs backups for your restores on the target.
  3. Review this post which suggest Ola's script are smart enough to exclude Log Shipped databases. I have not needed to validate if this works, but it's worth a read.

I've probably missed a few options. However, I see no reason that Log Shipping should not still be possible.

Improve this answer
1
  • Thank you for your help, it did confirm something I was suspecting but didn't know, that Ola's scripts would be somehow stop taking tran backups.
    – TheNixon
    Feb 14, 2022 at 9:42
1

Deadline made me go with a solution that is not perfect but it works for now.

Two step job.

Step one:
Type, Powershell Powershell.exe -executionpolicy ByPass -File G:\Installations\AZCopy_Files.ps1

AZCopy_Files.ps1: azcopy.exe sync 'https://[account].blob.core.windows.net/[container]/[path/to/blob]?[SAS]' '/path/to/dir' --delete-destination=true

Step two: Type, Powershell.exe Powershell.exe -File G:\Installations\Restore_Database.ps1

Restore_Database.ps1: Restore-DbaDatabase -SqlInstance localhost -Path 'G:\SQLBackups' -MaintenanceSolutionBackup -UseDestinationDefaultDirectories -DatabaseName "Destination_DB_Name" –ReplaceDbNameInFile -WithReplace

I didn't want to mess up Ola's scripts since even I would disable log shipping job and not run it, Ola's scripts would stop. Bunch of powershell installations and upgrades completed during these couple of days. Extra characters forgotten or added where not needed, rights, modules, etc. However, it works, step two is restoring the latest backup these last three nights, checked in the job's outcome.

Thank you both on your help.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.