DP-300 practice question 32 of 35

You deploy an instance of SQL Server on Azure Virtual Machines named SQL1 that hosts multiple databases. You configure the full recovery model for…

Full set: 407 questions

Domain Plan and configure a high availability and disaster recovery solution

You deploy an instance of SQL Server on Azure Virtual Machines named SQL1 that hosts multiple databases. You configure the full recovery model for all the databases. You perform a full backup of the master database on SQL1. You need to a perform an additional backup of the master database on SQL1. The solution must minimize how long it takes to perform the backup. Which type of backup should you perform?
Answer options
A: tail-log
B: differential Correct answer
C: full
D: log
Correct Answer:
B. differential
Explanation

The goal is to perform an additional backup of the master database that minimizes backup time. The master database does not support transaction log backups, regardless of the recovery model settings for other user databases; its recovery model is permanently set to SIMPLE. This immediately eliminates log and tail-log backups as options. Between a full and a differential backup, a differential backup is the most time-efficient choice. It only captures the data extents that have changed since the last full backup was performed. As a full backup already exists, a differential backup will be smaller and faster to complete than taking another complete full backup, directly addressing the requirement to minimize backup time.

Why Incorrect

A. tail-log: A tail-log backup is a type of transaction log backup. The master database does not support transaction log backups.
C. full: While a full backup is possible, it backs up the entire database, which would take longer than a differential backup that only captures changes.
D. log: The master database does not support transaction log backups because its recovery model is always SIMPLE.

References

1. Microsoft SQL Docs

"Back Up and Restore of System Databases (SQL Server)": This document explicitly states the limitations for backing up the master database. Under the section "master Database Backups

" it says

"You cannot back up the transaction log of the master database." This invalidates options A and D. It also implicitly supports differential backups as a valid

faster alternative to a full backup.

Source: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-system-databases-sql-server?view=sql-server-ver16#master-database-backups

2. Microsoft SQL Docs

"Differential Backups (SQL Server)": This document explains the mechanism of differential backups. It states

"A differential backup captures only the data that has changed since that full backup... Therefore

a differential backup is typically quicker to create and smaller than a full backup." This supports option B as the correct choice for minimizing backup time.

Source: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/differential-backups-sql-server?view=sql-server-ver16#overview-of-differential-backups

3. Microsoft SQL Docs

"master Database": This document confirms the recovery model for the master database. It states

"The recovery model of the master database is always SIMPLE." This is a critical piece of information that overrides the question's premise that "all" databases were set to the full recovery model

reinforcing why log backups are not possible.

Source: https://learn.microsoft.com/en-us/sql/relational-databases/databases/master-database?view=sql-server-ver16#remarks

Scroll to Top