locked
Get most recent Last Modified file name from containers subfolder RRS feed

  • Question

  • Hey Team,

    I have some sql server backup files amongst other files being delivered into Azure Blob Storage.  The container I want has some additional sub folders.

    The code I have will get me the most recent file but it is looking across all subfolders in the container where as I will need to direct it to the specific subfolder and also include some Date parameters for year and month.

    The code I have

    # Connect to Azure with an interactive dialog for sign-in
    #Connect-AzAccount
    $container_name = 'containername'
    $connection_string = 'DefaultEndpointsProtocol=https;AccountName=AccName;AccountKey=AccKey'
    $storage_account = New-AzStorageContext -ConnectionString $connection_string
    # Get the blobs list and then sort them by last modified date descending
    $blobs = Get-AzStorageBlob -Container $container_name -Context $storage_account -Blob *.bak | sort @{expression="LastModified";Descending=$true}
    $latestBlob = $blobs[0]
    write $latestBlob.Name
    

     which returns this latest blobname which is the latest blob at the container level

    MSSQL/Backup/NODE1/msdb/FULL/2019/2019-05/node1_msdb_FULL_20190528_084000.bak

    I need it to look in the sub folderof the same container MSSQL/Backup/bluecluster$blue_ag/AO/FULL/2019/2019-05.

    I have attempted to add this to the container name eg containername/MSSQL/Backup/bluecluster$blue_ag/AO/FULL/2019/2019-05 which errors out. As it is too long and is invalid.

    There must be a switch or -Name that I can add in but can't find anything.

    Thanks

    Binway


    Binway

    Tuesday, May 28, 2019 2:25 AM

Answers

  • @ Binway

    Thank you for posting here! 

    Try the PS cmdlets mentioned below you will be able to view the last modified file name from container and sub folder.

     
    $blobs = Get-AzStorageBlob -Container $container_name -Context $storage_account
    This provides the Name of the Folder, Block type, Last Modified and more
    $blobLastModified = $blobs.ICloudBlob.Properties.LastModified
    $blobLastModified
    

    Kindly let us know if the above helps or you need further assistance on this issue.

    ------------------------------------------------------------------------------------------

    Do click on "Mark as Answer" on the post that helps you, this can be beneficial to other community members.

    Tuesday, May 28, 2019 11:01 AM
  • This seems to be working ok - but I get an error - "Cannot index into a null array" $latestBlob = blobs[0] - but it seems to be getting the most recent backup and restoring to the SQL Server Managed Instance.

    # Connect to Azure with an interactive dialog for sign-in
    #Connect-AzAccount - to be set up for SQL Server Agent
    $container_name = 'sqlbackups'
    $connection_string = 'DefaultEndpointsProtocol=https;AccountName=<acctname>storage;AccountKey=<acckey>'
    $storage_account = New-AzStorageContext -ConnectionString $connection_string
    #set blob variables
    $blbstrt = 'MSSQL/Backup/bluecluster$blue_ag/ABC/FULL/'
    $year = Get-Date -format yyyy
    $yearmth = Get-Date -format yyyy-MM
    $blobpath = $filename + $year + '/' + $yearmth + '/' +'*.bak'
    
    # Get the blobs list and then sort them by last modified date descending
    $blobs = Get-AzStorageBlob -Container $container_name -Context $storage_account -Blob $blobpath | sort @{expression="LastModified";Descending=$true}
    $latestBlob = $blobs[0] 
    write $latestBlob.Name
    $findblb = $latestBlob.Name
    
    # from https://social.technet.microsoft.com/wiki/contents/articles/52402.azure-sql-how-to-restore-database-in-sql-managed-instance.aspx
    #Type the Managed instance admin login
    $username =  "sqladmin"
    
    #Type the Managed instance admin password
    $password =  '<strong>'
    
    #Type the Full Managed instance name
    $managedInstance =  "<name>.<someaz#>.database.windows.net"
    
    #Leave this parameter as is
    $database =  "master"
    
    #Before execute the Invoke-Sqlcmd, type the address with the full database backup path
    $url1 = 'https://<strge>storage.blob.core.windows.net/sqlbackups/'
    
    $url2 = $url1 + $findblb
    write $url2
    $qto = 0
     
    Invoke-Sqlcmd -ServerInstance $managedInstance -Database $database -Username $username -Password $password -QueryTimeout $qto -Query "RESTORE DATABASE [DB_NAME] FROM URL = '$url2'"

    So the sub folders are actually ALL Part of the blob.

    This may help opthers.

    Thanks


    Binway

    • Marked as answer by Binway Wednesday, May 29, 2019 12:55 AM
    Wednesday, May 29, 2019 12:54 AM

All replies

  • @ Binway

    Thank you for posting here! 

    Try the PS cmdlets mentioned below you will be able to view the last modified file name from container and sub folder.

     
    $blobs = Get-AzStorageBlob -Container $container_name -Context $storage_account
    This provides the Name of the Folder, Block type, Last Modified and more
    $blobLastModified = $blobs.ICloudBlob.Properties.LastModified
    $blobLastModified
    

    Kindly let us know if the above helps or you need further assistance on this issue.

    ------------------------------------------------------------------------------------------

    Do click on "Mark as Answer" on the post that helps you, this can be beneficial to other community members.

    Tuesday, May 28, 2019 11:01 AM
  • Thanks for looking at this Sumanth.  With your code I get this result.

    What I need is to tell the code to only look in a specific sub folder of the container - eg In "container_name" find the most recent file in this directory - MSSQL/Backup/NODE1/msdb/FULL/2019/2019-05/. Which should then have an output like this - node1_msdb_FULL_20190528_084000.bak or it can have the full directory path.

    Thanks


    Binway

    Tuesday, May 28, 2019 11:02 PM
  • This seems to be working ok - but I get an error - "Cannot index into a null array" $latestBlob = blobs[0] - but it seems to be getting the most recent backup and restoring to the SQL Server Managed Instance.

    # Connect to Azure with an interactive dialog for sign-in
    #Connect-AzAccount - to be set up for SQL Server Agent
    $container_name = 'sqlbackups'
    $connection_string = 'DefaultEndpointsProtocol=https;AccountName=<acctname>storage;AccountKey=<acckey>'
    $storage_account = New-AzStorageContext -ConnectionString $connection_string
    #set blob variables
    $blbstrt = 'MSSQL/Backup/bluecluster$blue_ag/ABC/FULL/'
    $year = Get-Date -format yyyy
    $yearmth = Get-Date -format yyyy-MM
    $blobpath = $filename + $year + '/' + $yearmth + '/' +'*.bak'
    
    # Get the blobs list and then sort them by last modified date descending
    $blobs = Get-AzStorageBlob -Container $container_name -Context $storage_account -Blob $blobpath | sort @{expression="LastModified";Descending=$true}
    $latestBlob = $blobs[0] 
    write $latestBlob.Name
    $findblb = $latestBlob.Name
    
    # from https://social.technet.microsoft.com/wiki/contents/articles/52402.azure-sql-how-to-restore-database-in-sql-managed-instance.aspx
    #Type the Managed instance admin login
    $username =  "sqladmin"
    
    #Type the Managed instance admin password
    $password =  '<strong>'
    
    #Type the Full Managed instance name
    $managedInstance =  "<name>.<someaz#>.database.windows.net"
    
    #Leave this parameter as is
    $database =  "master"
    
    #Before execute the Invoke-Sqlcmd, type the address with the full database backup path
    $url1 = 'https://<strge>storage.blob.core.windows.net/sqlbackups/'
    
    $url2 = $url1 + $findblb
    write $url2
    $qto = 0
     
    Invoke-Sqlcmd -ServerInstance $managedInstance -Database $database -Username $username -Password $password -QueryTimeout $qto -Query "RESTORE DATABASE [DB_NAME] FROM URL = '$url2'"

    So the sub folders are actually ALL Part of the blob.

    This may help opthers.

    Thanks


    Binway

    • Marked as answer by Binway Wednesday, May 29, 2019 12:55 AM
    Wednesday, May 29, 2019 12:54 AM