Tips & Tricks #4: Monitoring Backup History for Azure SQL Database & Azure SQL Managed Instance

Posted by

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

Introduction

Database backups are an essential part of any business continuity and disaster recovery strategy, because they help protect your data from corruption or deletion. These backups enable database restore to a point in time within the configured retention period. By default, Azure SQL Database & Azure SQL Managed Instance stores data in geo-redundant storage blobs that are replicated to a paired region. Geo-redundancy helps protect against outages that affect backup storage in the primary region. It also allows you to restore your databases/instance to a different region in the event of a regional outage/disaster.

 

Backup frequency

Azure SQL Managed Instance creates:

The frequency of transaction log backups is based on the compute size and the amount of database activity. When you restore a database, the service determines which full, differential, and transaction log backups need to be restored.

 

How to monitor Azure SQL Database History backups

Azure SQL Database Backup History introduced a new Dynamic Management View(DMV) called Sys.dm_database_backups, that contains metadata information on all the active backups that are needed for enabling point-in-time restore within configured retention. Metadata information includes:

  • Backup_file_id – Backup file ID
  • Database_guid –Logical Database ID
  • Physical_Database_name – Physical Database Name
  • Server_name – Physical Server Name
  • Backup_start_date – Backup Start Timestamp
  • Backup_finish_date – Backup End Timestamp
  • Backup_Type – Type of Backup. D stands for Full Database Backup, L – Stands for Log Backup and I – Stands for differential backup
  • In_Retention – Whether backup is within retention period or not. 1 stands for within retention period and 0 stands for out of retention

Examples

To query all the history backups for all the databases in Azure SQL Database, use the below T-SQL:

 

SELECT db.name
    , backup_start_date
    , backup_finish_date
    , CASE backup_type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction Log'
    END AS BackupType
    , CASE in_retention
        WHEN 1 THEN 'In Retention'
        WHEN 0 THEN 'Out of Retention'
        END AS is_Backup_Available
FROM sys.dm_database_backups AS ddb
INNER JOIN sys.databases AS db
    ON ddb.physical_database_name = db.physical_database_name
ORDER BY backup_start_date DESC;

 

 

To query all the history backups for a specific database in Azure SQL Database, use the below T-SQL:

 

SELECT db.name
    , backup_start_date
    , backup_finish_date
    , CASE backup_type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction Log'
    END AS BackupType
    , CASE in_retention
        WHEN 1 THEN 'In Retention'
        WHEN 0 THEN 'Out of Retention'
        END AS is_Backup_Available
FROM sys.dm_database_backups AS ddb
INNER JOIN sys.databases AS db
    ON ddb.physical_database_name = db.physical_database_name
WHERE db.name = 'SampleDB' 
ORDER BY backup_start_date DESC;

 

 

How to monitor Azure SQL Managed Instance History backups

Currently there is no DMVs to query all the history backups just like Azure SQL Database. So, you need to use Extended Events or XEvents sessions to track backups. Azure SQL Managed Instance offers two types of xEvents sessions to track backups:


Storing data in the ring buffer:

By using this option; you have a limit of about 1000 messages so should only be used to track recent activity. Additionally, ring buffer data is lost upon failover.

Examples

To query the Simple xEvent session for all the databases in Azure SQL Managed Instance, use the below T-SQL:

 

WITH
a AS (
		SELECT xed = CAST(xet.target_data AS xml)
		FROM sys.dm_xe_session_targets AS xet
		JOIN sys.dm_xe_sessions AS xe
			ON (xe.address = xet.event_session_address)
		WHERE xe.name = 'Backup trace'
	),
b AS(
		SELECT	d.n.value('(@timestamp)[1]', 'datetime2') AS [timestamp],
				ISNULL(db.name, d.n.value('(data[@name="database_name"]/value)[1]', 'varchar(200)')) AS database_name,
				d.n.value('(data[@name="trace_message"]/value)[1]', 'varchar(4000)') AS trace_message
		FROM a
		CROSS APPLY  xed.nodes('/RingBufferTarget/event') d(n)
		INNER JOIN master.sys.databases db
			ON db.physical_database_name = d.n.value('(data[@name="database_name"]/value)[1]', 'varchar(200)')
	)
SELECT * FROM b

 

 

Results sample:

img3.png

 

To query the Verbose xEvent session for all the databases in Azure SQL Managed Instance, use the below T-SQL:

 

WITH
a AS (
		SELECT xed = CAST(xet.target_data AS xml)
		FROM sys.dm_xe_session_targets AS xet
		JOIN sys.dm_xe_sessions AS xe
			ON (xe.address = xet.event_session_address)
		WHERE xe.name = 'Verbose backup trace'
	),
b AS(
		SELECT	d.n.value('(@timestamp)[1]', 'datetime2') AS [timestamp],
				ISNULL(db.name, d.n.value('(data[@name="database_name"]/value)[1]', 'varchar(200)')) AS database_name,
				d.n.value('(data[@name="trace_message"]/value)[1]', 'varchar(4000)') AS trace_message
		FROM a
		CROSS APPLY  xed.nodes('/RingBufferTarget/event') d(n)
		LEFT JOIN master.sys.databases db
			ON db.physical_database_name = d.n.value('(data[@name="database_name"]/value)[1]', 'varchar(200)')
	)
SELECT * FROM b
WHERE trace_message LIKE 'BACKUP DATABASE%'

 

 

Results sample for differential and full backups:

img1.png

 

Results sample for log backups:

img2.png


Storing data in the event file:

By using this option; you have the below advantages:

  • Can store more than 1000 message.
  • Track recent and old activities.
  • Your event file(s) will not be affected by any failover.
  • You can view history backup results for any period you want.

As for the disadvantages, I believe consuming the available space of your blob storage is the major one. So, you need to keep deleting unwanted event file(s) from your blob (ex., older than 3 or 6-months).


Configure XEvent session to store data in an event file:

 

------  Step 1.  Create key, and  ------------
------  Create credential (your Azure Storage container must already exist).
IF NOT EXISTS
    (SELECT * FROM sys.symmetric_keys
        WHERE symmetric_key_id = 101)
BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '0C34C960-6621-4682-A123-C7EA08E3FC46' -- Or any newid().
END
GO

IF EXISTS
    (SELECT * FROM sys.credentials
        -- TODO: Assign AzureStorageAccount name, and the associated Container name.
        WHERE name = 'https://<StorageAccountName>.blob.core.windows.net/<StorageBlobName>')
BEGIN
    DROP CREDENTIAL
        -- TODO: Assign AzureStorageAccount name, and the associated Container name.
        [https://<StorageAccountName>.blob.core.windows.net/<StorageBlobName>] ;
END
GO

CREATE CREDENTIAL
        -- use '.blob.',   and not '.queue.' or '.table.' etc.
        -- TODO: Assign AzureStorageAccount name, and the associated Container name.
        [https://<StorageAccountName>.blob.core.windows.net/<StorageBlobName>]
    WITH
        IDENTITY = 'SHARED ACCESS SIGNATURE',  -- "SAS" token.
        -- TODO: Paste in the long SasToken string here for Secret, but exclude any leading '?'.
        SECRET = '<SAS TOKEN HERE>'
    ;
GO

 

 

For configuring Simple backup trace xEvent session use:

 

------  Step 2.  Create (define) an event session.  --------
------  The event session has an event with an action,
------  and a has a target.

IF EXISTS
    (SELECT * from sys.database_event_sessions
        WHERE name = 'Simple backup trace')
BEGIN
    DROP
        EVENT SESSION
            [Simple backup trace]
        ON SERVER;
END
GO

-- Create the session
CREATE EVENT SESSION [Simple backup trace] ON SERVER 
ADD EVENT sqlserver.backup_restore_progress_trace(
    WHERE (
              [operation_type]=(0) AND 
              [trace_message] like '%100 percent%'
       )

 ADD TARGET package0.event_file
            (
            -- TODO: Assign AzureStorageAccount name, and the associated Container name.
            -- Also, tweak the .xel file name at end, if you like.
            SET filename =
                'https://<StorageAccountName>.blob.core.windows.net/<StorageBlobName>/myserver_simple_backup_trace.xel'
            )
	WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
       MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
       TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

-- Start the session
ALTER EVENT SESSION [Simple backup trace] ON SERVER
STATE = start;

 

 

For configuring Verbose backup trace xEvent session use:

 

------  Step 2.  Create (define) an event session.  --------
------  The event session has an event with an action,
------  and a has a target.

IF EXISTS
    (SELECT * from sys.database_event_sessions
        WHERE name = 'Verbose backup trace')
BEGIN
    DROP
        EVENT SESSION
            [Verbose backup trace]
        ON SERVER;
END
GO

-- Create the session
CREATE EVENT SESSION [Verbose backup trace] ON SERVER 
ADD EVENT sqlserver.backup_restore_progress_trace(
    WHERE (
              [operation_type]=(0) AND (
              [trace_message] like '%100 percent%' OR 
              [trace_message] like '%BACKUP DATABASE%' OR [trace_message] like '%BACKUP LOG%'))
       )

 ADD TARGET package0.event_file
            (
            -- TODO: Assign AzureStorageAccount name, and the associated Container name.
            -- Also, tweak the .xel file name at end, if you like.
            SET filename =
                'https://<StorageAccountName>.blob.core.windows.net/<StorageBlobName>/myserver_verbose_backup_trace.xel'
            )
	WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
       MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
       TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

-- Start the session
ALTER EVENT SESSION [Verbose backup trace] ON SERVER
STATE = start;

 

 

How the event file(s) looks like in blob container (Portal):

img4.png


Examples

To query the Simple or Verbose xEvent session for all the databases in Azure SQL Managed Instance, enter the xel-file name from the above storage blob event files as shown in the below T-SQL:

 

WITH dxml AS
(
	SELECT event_data_XML = CAST(event_data AS XML)  
	FROM
		sys.fn_xe_file_target_read_file
			(
				-- TODO: Fill in Storage Account name, and the associated Container name.
				-- TODO: The name of the .xel file needs to be an exact match to the files in the storage account Container (You can use Storage Account explorer from the portal to find out the exact file names or you can retrieve the name using the    following DMV-query: select target_data from sys.dm_xe_database_session_targets. The 3rd xml-node, "File name", contains the name of the file currently written to.)
				'https://<StorageAccountName>.blob.core.windows.net/<StorageBlobName>/myserver_simple_backup_trace_0_133093902657060000.xel',
				null, null, null
			)
), bkp AS
(	
	SELECT	d.n.value('(@timestamp)[1]', 'datetime2') AS [timestamp],
				ISNULL(db.name, d.n.value('(@name)[1]', 'varchar(200)')) AS database_name,
				d.n.value('(data[@name="trace_message"]/value)[1]', 'varchar(4000)') AS trace_message
	FROM dxml
	CROSS APPLY  event_data_XML.nodes('/event') d(n)
	LEFT JOIN master.sys.databases db
		ON db.physical_database_name = d.n.value('(data[@name="database_name"]/value)[1]', 'varchar(200)')
)

SELECT * FROM bkp 

 

 

 

For more information about monitoring history backups, please refer to the below articles:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.