none
Unable to find DB backup in Azure Storage container RRS feed

  • Question

  • Hello all,

    We have couple of Azure windows 2016 VMs running with SQL DB.  DB backup is getting stored in Azure storage account  blob container. From the DB management studio, we can see that last backup was completed on March 3rd,However, we are unable to find any backup related to those two servers in designated storage blob container. Even from DB management studio, when we try to pull the list of available backup through SAS token, it is getting failed and not able to find anything.

    Can anyone please help to fix this.

    Thanks,



    suhag

    Wednesday, March 6, 2019 12:25 PM

Answers

  • Good day Suhag,

    SQL Server supports virtualization-aware backup solutions that use volume shadow copy (VSS) also named volume snapshots. For example, SQL Server supports Hyper-V and VMware backup. For more information check this document.

    When the host backups your system "SQL Server VSS Writer" service is used (It should be running when SQL Server is installed on virtual machine).

    You should notice that these backups have value 7 in the column "device_type" in the table backupmediafamily (7 means "Virtual device"). These rows are actually very useful for monitoring, for example it help to know that the Virtual Machine backups are running full database backups on a SQL Server instance. there's no trace of these backup files since they are above the scope of the virtual machine (above the level of your control) - these are triggered by the host,meaning azure in this case (Hyper-V or VMware for example triggers these).

    In conclusion: These backups are not related to your backup to the blob

    When you backup to the blob using query like bellow for example:

    BACKUP DATABASE [TWIG] 
    	-- URL includes the endpoint for the BLOB service, followed by the container name, and the name of the backup file 
    	TO URL = 'https://YourStorageName.blob.core.windows.net/FolderName/DatabaseName.bak' 
    	-- name of the credential you created in the previous step
    	WITH CREDENTIAL = 'mycredential';
    GO

    Then the physical_device_name value is the URL: https://YourStorageName.blob.core.windows.net/FolderName/DatabaseName.bak

    I hope that this explain the"issue" :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    • Marked as answer by Suhag Desai Thursday, March 7, 2019 7:57 PM
    Thursday, March 7, 2019 7:04 PM
    Moderator

All replies

  • Hello,

    Try to download and install Azure Storage Explorer. Use it to explore the storage account. Explore the statistics of the account to see if it is receiving data.

    https://azure.microsoft.com/en-us/features/storage-explorer/?wt.mc_id=MVP


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com


    Wednesday, March 6, 2019 12:42 PM
  • Thanks Alberto ,

    I have tried but no luck  :( 

    Unable to find where the DB backup is going....


    suhag

    Wednesday, March 6, 2019 1:07 PM
  • Hello,

    It seems you are using a SQL Server Agent job to schedule the backup. Could you please run manually the backup statement contained on that job? You may find the culprit doing that.


    You see the completes successful every time but if the duration of the job in seconds is zero then backup is not configured correctly.


    The following query may help you confirm the location of the backups.

    DECLARE @dbname     sysname

    SET @dbname = ''

    SELECT 

         @@servername [ServerName]

        ,master.sys.sysdatabases.name [DatabaseName]

        ,msdb.dbo.backupset.backup_start_date [Backup Date]

        ,msdb.dbo.backupset.user_name

        ,datediff(second, msdb.dbo.backupset.backup_start_date,

        msdb.dbo.backupset.backup_finish_date) [Duration-seconds]

        ,msdb.dbo.backupmediafamily.physical_device_name [File Location]   

        ,msdb.dbo.backupset.type

    FROM

        msdb.dbo.backupmediafamily,

        master.sys.sysdatabases

        LEFT OUTER JOIN

        msdb.dbo.backupset

        ON master.sys.sysdatabases.name = msdb.dbo.backupset.database_name 

    WHERE

        msdb.dbo.backupset.type in( 'D', 'I', 'L')

    AND msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

    and msdb.dbo.backupset.backup_start_date > getdate() - 2

    AND master.sys.sysdatabases.name not in ('pubs','northwind', 'tempdb','adventureworks')

    AND master.sys.sysdatabases.name like '%' + @dbname + '%'

    ORDER BY


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, March 6, 2019 2:45 PM
  • Thanks, i will check this and let you know how it goes

    suhag

    Wednesday, March 6, 2019 5:27 PM
  • Hi  Alberto,

    Please see below output received from DB team. Physical device name is in binary/encrypted form. Normally, for other DB servers, we can see the URI path with storage container.

    


    suhag

    Wednesday, March 6, 2019 6:53 PM
  • Good day Suhag,

    SQL Server supports virtualization-aware backup solutions that use volume shadow copy (VSS) also named volume snapshots. For example, SQL Server supports Hyper-V and VMware backup. For more information check this document.

    When the host backups your system "SQL Server VSS Writer" service is used (It should be running when SQL Server is installed on virtual machine).

    You should notice that these backups have value 7 in the column "device_type" in the table backupmediafamily (7 means "Virtual device"). These rows are actually very useful for monitoring, for example it help to know that the Virtual Machine backups are running full database backups on a SQL Server instance. there's no trace of these backup files since they are above the scope of the virtual machine (above the level of your control) - these are triggered by the host,meaning azure in this case (Hyper-V or VMware for example triggers these).

    In conclusion: These backups are not related to your backup to the blob

    When you backup to the blob using query like bellow for example:

    BACKUP DATABASE [TWIG] 
    	-- URL includes the endpoint for the BLOB service, followed by the container name, and the name of the backup file 
    	TO URL = 'https://YourStorageName.blob.core.windows.net/FolderName/DatabaseName.bak' 
    	-- name of the credential you created in the previous step
    	WITH CREDENTIAL = 'mycredential';
    GO

    Then the physical_device_name value is the URL: https://YourStorageName.blob.core.windows.net/FolderName/DatabaseName.bak

    I hope that this explain the"issue" :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    • Marked as answer by Suhag Desai Thursday, March 7, 2019 7:57 PM
    Thursday, March 7, 2019 7:04 PM
    Moderator
  • Thanks for the details.  

    suhag

    Thursday, March 7, 2019 7:57 PM
  • You are most welcome :-)

    I found out that there is no good post on the topic online and there are lot of related questions - by "good" i mean in my opinion at least, and by "found out" I mean searching using Google. Therefore, I added a post about this topic in my blog for the sake of other people. You can always say that your led to the post :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Thursday, March 7, 2019 8:37 PM
    Moderator