🔒 Guided

Pre-launch preview. Authorised access only.

Incorrect code

Guided by A Guide to Cloud
Explore AB-900 AI-901
Guided DP-300 Domain 5
Domain 5 — Module 2 of 6 33%
24 of 28 overall

DP-300 Study Guide

Domain 1: Plan and Implement Data Platform Resources

  • Choose Your Azure SQL Platform Free
  • Deploy and Configure Azure SQL Free
  • Scale, Performance, and Compression Free
  • Migration Planning: Online vs Offline Free
  • Execute and Troubleshoot Migrations Free

Domain 2: Implement a Secure Environment

  • Entra Authentication for Azure SQL
  • Security Principals, Permissions, and T-SQL
  • Encryption: TDE, Always Encrypted, and VBS Enclaves
  • Network Security: Firewalls, Private Links, and Endpoints
  • Data Classification and Auditing
  • Data Masking, Ledger, and Row-Level Security

Domain 3: Monitor, Configure, and Optimize Database Resources

  • Performance Baselines and Monitoring Tools
  • Database Watcher and Extended Events
  • Query Store: Configure and Monitor
  • Blocking, DMVs, and Execution Plans
  • Index and Query Optimization
  • Database Maintenance: Indexes, Statistics, and Integrity
  • Automatic Tuning and Performance Settings

Domain 4: Configure and Manage Automation of Tasks

  • Automation Landscape: What Runs Where
  • SQL Server Agent Jobs
  • Deploy with ARM, Bicep, PowerShell, and CLI
  • Elastic Jobs and Azure Automation

Domain 5: Plan and Configure an HA/DR Environment

  • HA/DR Strategy: RPO, RTO, and Architecture
  • Backup and Restore: Strategy and Native Tools
  • Point-in-Time Restore, LTR, and Cloud Backup
  • Geo-Replication and Failover Groups
  • Always On: Availability Groups and FCIs
  • Log Shipping and HA/DR Operations

DP-300 Study Guide

Domain 1: Plan and Implement Data Platform Resources

  • Choose Your Azure SQL Platform Free
  • Deploy and Configure Azure SQL Free
  • Scale, Performance, and Compression Free
  • Migration Planning: Online vs Offline Free
  • Execute and Troubleshoot Migrations Free

Domain 2: Implement a Secure Environment

  • Entra Authentication for Azure SQL
  • Security Principals, Permissions, and T-SQL
  • Encryption: TDE, Always Encrypted, and VBS Enclaves
  • Network Security: Firewalls, Private Links, and Endpoints
  • Data Classification and Auditing
  • Data Masking, Ledger, and Row-Level Security

Domain 3: Monitor, Configure, and Optimize Database Resources

  • Performance Baselines and Monitoring Tools
  • Database Watcher and Extended Events
  • Query Store: Configure and Monitor
  • Blocking, DMVs, and Execution Plans
  • Index and Query Optimization
  • Database Maintenance: Indexes, Statistics, and Integrity
  • Automatic Tuning and Performance Settings

Domain 4: Configure and Manage Automation of Tasks

  • Automation Landscape: What Runs Where
  • SQL Server Agent Jobs
  • Deploy with ARM, Bicep, PowerShell, and CLI
  • Elastic Jobs and Azure Automation

Domain 5: Plan and Configure an HA/DR Environment

  • HA/DR Strategy: RPO, RTO, and Architecture
  • Backup and Restore: Strategy and Native Tools
  • Point-in-Time Restore, LTR, and Cloud Backup
  • Geo-Replication and Failover Groups
  • Always On: Availability Groups and FCIs
  • Log Shipping and HA/DR Operations
Domain 5: Plan and Configure an HA/DR Environment Premium ⏱ ~13 min read

Backup and Restore: Strategy and Native Tools

Recommend database backup strategies. Perform backup and restore using native tools and T-SQL for Azure SQL Database, Managed Instance, and SQL on VMs.

Backup fundamentals

☕ Simple explanation

Backups are your safety net.

A full backup copies the entire database — like photocopying every page of a book.

A differential backup copies only what changed since the last full backup — like noting which pages were edited.

A log backup copies the transaction log — like saving a recording of every edit as it happened. This enables point-in-time recovery.

SQL Server backup types: full (complete database copy), differential (changes since last full), and transaction log (changes since last log backup). Together they form a backup chain that enables point-in-time recovery.

Backup strategy by platform

Who Manages Backups?
AspectSQL DatabaseManaged InstanceSQL on VMs
Automated backupsYes (Azure managed)Yes (Azure managed)Optional (via SQL IaaS extension)
Full backup frequencyWeeklyWeeklyYou schedule
Differential frequencyEvery 12-24 hoursEvery 12-24 hoursYou schedule
Log backup frequencyEvery 5-10 minutesEvery 5-10 minutesYou schedule
Retention (PITR)1-35 days (default 7)1-35 days (default 7)N/A (manual)
Long-term retentionLTR policy (up to 10 years)LTR policy (up to 10 years)Manual (backup to Blob Storage)
Custom backups (BACKUP TO URL)No (only automated)Yes (copy-only to Blob)Yes (full/diff/log to Blob)
Backup storageAzure managed (GRS/LRS/ZRS)Azure managed (GRS/LRS/ZRS)You manage (local/Blob)

Automated backups (SQL DB and MI)

Azure automatically manages the backup chain:

  • Full backups: Weekly
  • Differential backups: Every 12-24 hours
  • Log backups: Every 5-10 minutes
  • Storage redundancy: LRS, ZRS, or GRS (configurable at database creation)
  • Retention: 1-35 days for PITR (default 7 days)

You don’t run BACKUP commands on Azure SQL Database — Azure handles it. For MI, you can take additional copy-only backups to Blob Storage.

Native backup with T-SQL

For MI and SQL on VMs:

-- Full backup to Azure Blob Storage
BACKUP DATABASE NorthStarERP
TO URL = 'https://northstarstorage.blob.core.windows.net/backups/NorthStarERP_Full.bak'
WITH CREDENTIAL = 'AzureStorageCredential',
     COMPRESSION, CHECKSUM, INIT;

-- Differential backup
BACKUP DATABASE NorthStarERP
TO URL = 'https://northstarstorage.blob.core.windows.net/backups/NorthStarERP_Diff.bak'
WITH CREDENTIAL = 'AzureStorageCredential',
     DIFFERENTIAL, COMPRESSION, CHECKSUM;

-- Transaction log backup
BACKUP LOG NorthStarERP
TO URL = 'https://northstarstorage.blob.core.windows.net/backups/NorthStarERP_Log.trn'
WITH CREDENTIAL = 'AzureStorageCredential',
     COMPRESSION, CHECKSUM;

-- Copy-only backup (doesn't break the backup chain)
BACKUP DATABASE NorthStarERP
TO URL = 'https://northstarstorage.blob.core.windows.net/backups/NorthStarERP_CopyOnly.bak'
WITH CREDENTIAL = 'AzureStorageCredential',
     COPY_ONLY, COMPRESSION;
ℹ️ COPY_ONLY backups — when and why

A COPY_ONLY backup doesn’t affect the backup chain (doesn’t reset the differential base or break log chain):

  • Use for ad-hoc copies (dev refresh, migration, archiving)
  • MI: only COPY_ONLY backups are allowed to Blob Storage (Azure manages the primary chain)
  • Always use COPY_ONLY when taking manual backups alongside automated Azure backups

Native restore

Restore on SQL Server (VMs)

-- Restore a full backup
RESTORE DATABASE NorthStarERP
FROM URL = 'https://northstarstorage.blob.core.windows.net/backups/NorthStarERP_Full.bak'
WITH CREDENTIAL = 'AzureStorageCredential',
     NORECOVERY;  -- keep restoring diff + logs

-- Restore differential
RESTORE DATABASE NorthStarERP
FROM URL = 'https://northstarstorage.blob.core.windows.net/backups/NorthStarERP_Diff.bak'
WITH CREDENTIAL = 'AzureStorageCredential',
     NORECOVERY;

-- Restore log
RESTORE LOG NorthStarERP
FROM URL = 'https://northstarstorage.blob.core.windows.net/backups/NorthStarERP_Log.trn'
WITH CREDENTIAL = 'AzureStorageCredential',
     RECOVERY;  -- bring database online

Restore on Managed Instance

-- Restore from Blob Storage to MI
RESTORE DATABASE NorthStarERP
FROM URL = 'https://northstarstorage.blob.core.windows.net/backups/NorthStarERP_Full.bak'
WITH REPLACE;  -- overwrite if exists

Azure SQL Database restore

SQL Database doesn’t use T-SQL RESTORE. Instead:

  • Point-in-time restore: Azure Portal, PowerShell, or CLI (creates a new database)
  • Geo-restore: Restore from geo-redundant backup to a different region
  • LTR restore: Restore from long-term retention backup

Backup strategy recommendations

Workload TypeFullDifferentialLogRetention
Mission-critical OLTPDailyEvery 6 hoursEvery 5-15 min35 days PITR + 1 year LTR
Standard business appWeeklyDailyEvery 15-30 min14 days PITR + 3 months LTR
Dev/testWeeklyNoneNone (Simple recovery)7 days PITR
Compliance/regulatoryWeeklyDailyEvery 15 min35 days PITR + 7-10 years LTR
Question

Can you run BACKUP DATABASE on Azure SQL Database?

Click or press Enter to reveal answer

Answer

No. Azure SQL Database manages backups automatically. You cannot take manual backups. For MI, you can take COPY_ONLY backups to Blob Storage in addition to the automated ones.

Click to flip back
Question

What is the difference between NORECOVERY and RECOVERY in a RESTORE statement?

Click or press Enter to reveal answer

Answer

NORECOVERY: keeps the database in restoring state so you can apply more differentials and logs. RECOVERY: brings the database online (no more restores can be applied). Use NORECOVERY for all but the last restore.

Click to flip back
Question

What is a COPY_ONLY backup?

Click or press Enter to reveal answer

Answer

A backup that doesn't affect the backup chain — doesn't reset the differential base or break the log chain. Use for ad-hoc copies, dev refreshes, and migration scenarios.

Click to flip back
Knowledge Check

Amara needs to take a manual backup of a database on Azure SQL Managed Instance for a dev team refresh. She doesn't want to affect the automated backup chain. What type of backup should she take?

🎬 Video coming soon

Next up: Point-in-Time Restore, LTR, and Cloud Backup — recover to any second within your retention window and configure long-term retention.

← Previous

HA/DR Strategy: RPO, RTO, and Architecture

Next →

Point-in-Time Restore, LTR, and Cloud Backup

Guided

I learn, I simplify, I share.

A Guide to Cloud YouTube Feedback

© 2026 Sutheesh. All rights reserved.

Guided is an independent study resource and is not affiliated with, endorsed by, or officially connected to Microsoft. Microsoft, Azure, and related trademarks are property of Microsoft Corporation. Always verify information against Microsoft Learn.