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:
- Full backups every week.
- Differential backups every 12 to 24 hours.
- Transaction log backups every 5-10 minutes.
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:
- Simple xEvent session which tracks the full backups
- Verbose xEvent session which tracks all backups types (Full, Differential and Log)
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:
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:
Results sample for log backups:
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):
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: