en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
מרץ7

Written by: ronen ariely
07/03/2019 21:05 RssIcon

Hi guys,

I saw this question in the MSDN forum, and it seems like there is a confusion regarding backups of SQL Server Databases in virtual machine. Since I am lazy like always, I search google for answered that people gave for the same question, but I did not find something that I liked. I found the same question more or less asked multiple times in  Different forums. Therefore, I will try to explain the issue mentioned in the forum's thread and give some more info to clarify what we see and we don't not see. I hope that this will solve some misunderstanding regarding the information that we get in the backup log

If you have any question or comment,you can contact me on Facebook.

The Question(s)

The table msdb.dbo.backupmediafamily includes information regarding the media, which used for database's backups.  The values in the physical_device_name column shows the physical name of the backup device like backup destination path or the restore source path.

When we query the table backupmediafamily in SQL Server intance on Virtual Machine then we can notice rows which has GUID value in the physical_device_name column.

Q1: Why do we get this GUID instead of a clear path to the backup, and where is the backups?

My two cent

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. 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).

When you execute the following query on SQL Server on Azure Virtual Machine 

SELECT
    database_name,
    backup_finish_date,
    CASE msdb..backupset.type
        WHEN 'D' THEN 'Database'
        WHEN 'L' THEN 'Log'
    END AS backup_type,
    physical_device_name
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset
    ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
--WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1)
ORDER BY database_name,backup_finish_date

you might see result like bellow - check the column physical_device_name:

You should notice that we have a lot of rows in the table which have a GUID value in the physical_device_name column. These rows also have value 7 in the column "device_type". The value 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 our backup in the level of the instance.

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

CREATE CREDENTIAL mycredential  
    WITH
        -- this is the name of the storage account you specified when creating a storage account  
        IDENTITY= 'YourStorageName',
        -- this should be either the Primary or Secondary Access Key for the storage account
        SECRET = '<storage account access key>'
GO
 
BACKUP DATABASE [TWIG]
    -- URL includes the endpoint for the BLOB service, followed by the container name, and the name of the backup file
    -- 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" :-)