Every night a backup is made from a production server (which I cannot access) to an azure blob store each night, I am using SQL Server 2016 in an Azure VM to restore that .bak to extract data for reporting purposes.
How do I identify the name of the latest .bak file from my blob store to automatically restore?
One way is I can use the rest API to get a list of blobs in a container but how can I get the results of that in SQL without 3rd party plugins.
I solved this issue by creating a powershell script which gets a list of blobs and saves them to a csv
$ctx = New-AzureStorageContext -StorageAccountName "" -SASToken ""
Get-AzureStorageBlob -Context $ctx -Container "" | Select-Object Name,LastModified,{""}, {""}, {""} | Export-Csv "C:\bloblist.csv"
I then load the csv into an azure sql database with ssis and retrieve the latest record
SELECT TOP 1 URI + [Name] as bak
FROM [stg].[ext_BlobList]
ORDER BY cast(lastmodified as datetime2) DESC
which is then used as a variable in ssis tsql script to restore the database
USE [master]
ALTER DATABASE [pc] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [pc] FROM URL = ? WITH FILE = 1, MOVE N'pc' TO N'F:\Data\pc.mdf', MOVE N'pc_log' TO N'F:\Log\pc_log.ldf', NOUNLOAD, REPLACE, STATS = 5
ALTER DATABASE [pc] SET MULTI_USER
GO