SQL Server Backups and Azure Backup for Azure SQL (DP-300 Focus)

SQL Server Backups and Azure Backup for Azure SQL

Why Backup Strategy Matters

A database backup is the last line of defense against data loss caused by hardware failure, human error, ransomware, or software bugs. For the DP-300 exam and in real production work, you must understand the three core SQL Server backup types, how they interact, and how Azure SQL Database automates much of this process.

The Three Core Backup Types

Full backup captures the entire database — every data page and enough of the transaction log to make the backup consistent. It is the foundation of every recovery strategy. Without a recent full backup, differential and log backups are useless.

Differential backup captures only the data pages that changed since the most recent full backup. Differentials grow over time until the next full backup resets the baseline. They speed up restore time compared to replaying many log backups.

Transaction-log backup captures the log records written since the last log backup (or full/differential backup). Log backups are only possible when the database uses the Full or Bulk-Logged recovery model. They enable point-in-time recovery (PITR) — restoring a database to a precise moment rather than just the end of the last backup.

The Simple recovery model does not support log backups and therefore does not support PITR. Always confirm the recovery model before designing a backup schedule.

T-SQL Backup Syntax

Backup commands follow a consistent pattern:

```sql

-- Full backup

BACKUP DATABASE [AdventureWorks]

TO DISK = 'C:\Backups\AW_Full.bak'

WITH NAME = 'AW-Full-20250101', STATS = 10;

-- Differential backup

BACKUP DATABASE [AdventureWorks]

TO DISK = 'C:\Backups\AW_Diff.bak'

WITH DIFFERENTIAL, NAME = 'AW-Diff-20250101', STATS = 10;

-- Transaction-log backup

BACKUP LOG [AdventureWorks]

TO DISK = 'C:\Backups\AW_Log.bak'

WITH NAME = 'AW-Log-20250101', STATS = 10;

```

The `STATS = 10` option prints a progress message every 10 percent — useful for large databases. Always record the backup file path, the backup set name, and the completion time in your lab log.

Verifying a Backup

A backup file that cannot be read is worthless. SQL Server provides `RESTORE VERIFYONLY` to check that a backup set is readable and internally consistent without actually restoring data:

```sql

RESTORE VERIFYONLY

FROM DISK = 'C:\Backups\AW_Full.bak';

```

If the command returns "The backup set on file 1 is valid," the file is structurally sound. This step maps directly to the DP-300 objective area "Monitor and optimize operational resources." Make it a habit after every backup.

Point-in-Time Restore

To restore a database to a specific moment, you apply a full backup, optionally a differential, and then one or more log backups, using `WITH NORECOVERY` on all steps except the last:

```sql

-- Step 1: Restore full backup, leave DB in restoring state

RESTORE DATABASE [AW_Restore]

FROM DISK = 'C:\Backups\AW_Full.bak'

WITH NORECOVERY, MOVE 'AdventureWorks_Data'

TO 'C:\Data\AW_Restore.mdf',

MOVE 'AdventureWorks_Log'

TO 'C:\Data\AW_Restore_log.ldf';

-- Step 2: Apply log backup, stop at target time

RESTORE LOG [AW_Restore]

FROM DISK = 'C:\Backups\AW_Log.bak'

WITH STOPAT = '2025-01-01T14:30:00', RECOVERY;

```

After the final step the database comes online. A `SELECT` query should confirm that rows inserted after the target timestamp do not appear.

Azure SQL Database Automated Backups

Azure SQL Database handles backups automatically, removing the need for manual T-SQL scheduling. The platform takes full, differential, and transaction-log backups on your behalf and stores them in geo-redundant Azure Blob Storage. Key settings you can review and configure include:

• Short-term retention (STR): Controls how many days of backups are kept for PITR. The default is 7 days; it can be extended.
• Long-term retention (LTR): Allows weekly, monthly, or yearly full backups to be kept for compliance purposes, potentially for years.
• Backup storage redundancy: Zone-redundant, geo-redundant, or locally redundant storage.

These settings are visible in the Azure portal under your SQL Database resource, or configurable through Azure CLI and PowerShell. The DP-300 exam tests your ability to interpret and adjust these settings.

Safety and Good Practices

Backup files can contain sensitive personal and financial data. Treat them as carefully as the live database:

• Store backups on a separate physical or logical volume from the database files so a single drive failure does not destroy both.
• Encrypt backups using SQL Server's backup encryption option or Azure Storage encryption at rest.
• Restrict file-system access to backup directories to authorized service accounts only.
• Never store credentials (passwords, SAS tokens) in plain text inside scripts — use SQL Server credentials objects or environment variables.
• Verify backups regularly; do not wait for a disaster to discover a corrupt file.
• In a shared lab environment, confirm you have permission to write to the backup path before running commands; improperly scoped write access is a common exam scenario and a real-world risk.