0

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Dorf
  • 229
  • 6
  • 15
  • 1
    Interesting question, how would we know how to access your blob store? We don't even know what you are using let alone how you are using it. – Dale K Oct 24 '19 at 03:41
  • A backup is made from the production server 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. – Dorf Oct 24 '19 at 04:14

2 Answers2

1

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
Dorf
  • 229
  • 6
  • 15
0

you could start with the following to get the name\location of the file, if was backed up using native backup (or something that integrates with the native backup)

select  top 1 
        bs.database_name, backup_finish_date
       ,bmf.physical_device_name
from msdb.dbo.backupset bs 
Inner join msdb.dbo.backupmediafamily bmf on bs.media_set_id = bmf.media_set_id
where bs.database_name Like 'master%'
and bs.type = 'D'  -- full backup
order by bs.backup_finish_date desc

But if your physical_device_name comes up with a guid, you're likely using some third party backup tool, which you'll have to manage another way...

Trubs
  • 2,829
  • 1
  • 24
  • 33
  • Unfortunately the backup is from another server so i do not know the name of the latest file unless i look it up manually. If i can load a list of all the blobs in the container to a sql table i could then figure out which is the latest, but i am not sure how to go about that. – Dorf Oct 24 '19 at 04:17
  • So you can run the above query on another server to get the correct information? Have you tried using a linked server? eg ..from **servername**.msdb.dbo.backupset.. – Trubs Oct 24 '19 at 04:38
  • I dont have access to the other server only the backups in the blob store – Dorf Oct 24 '19 at 05:20