DP-300 Question #202: Real Exam Question with Answer & Explanation

Question

Drag and Drop Question You have an Azure subscription that contains the resources shown in the following table. You need to back up db1 to mysqlbackups, and then restore the backup to a new database named db2 that is hosted on SQL1. The solution must ensure that db1 is backed up to a stripe set. Which three Transact-SQL statements should you execute in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order. Answer:

Explanation

Explanation: Azure SQL Backup to URL with Stripe Set

The Correct Sequence

Step 1: CREATE CREDENTIAL [sqlbackup] WITH IDENTITY = 'SHARED ACCESS SIGNATURE'

Why first: SQL Server cannot communicate with Azure Blob Storage without authentication. A credential must exist before any URL-based backup or restore operation. This credential stores the SAS token that grants SQL Server permission to read/write to the mysqlbackups storage account.

Why this specific CREATE CREDENTIAL, not the other one: The wrong option uses IDENTITY = 'sqlexamplebackup' with a storage account access key (SECRET = 'mystorageaccountaccesskey'). That's the older method for backing up to URL. When using SAS tokens (modern, recommended approach), the IDENTITY value must literally be the string 'SHARED ACCESS SIGNATURE' — this is a fixed keyword, not a name you choose.

Step 2: BACKUP DATABASE db1 TO URL = '...db1_1.bak', URL = '...db1_2.bak', URL = '...db1_3.bak' WITH FORMAT, ... (no semicolon version)

Why second: You must back up before you can restore. The credential now exists, so SQL Server can authenticate to blob storage.

Why this specific BACKUP, not the other one: Both BACKUP statements look nearly identical — the difference is a semicolon after CREDENTIAL = 'sqlbackup' in the wrong option. The correct one has no semicolon, keeping the WITH clause as a single statement. The semicolon version would cause a syntax error.

Stripe set: Specifying three separate URL targets in one BACKUP statement is what creates a stripe set — the backup data is split across db1_1.bak, db1_2.bak, and db1_3.bak in parallel. WITH FORMAT initializes new backup media.

Step 3: RESTORE DATABASE db2 ... WITH NORECOVERY, MOVE 'D:\data\db1_mdf.mdf' TO ...

Why third: Restore follows backup. The three stripe files are restored together in a single statement (matching the backup stripe set exactly).

Why NORECOVERY, not RECOVERY: This is the most common misconception. RECOVERY (used in the wrong option) brings the database online immediately after restore — it's used when no further restore steps follow. NORECOVERY keeps the database in a restoring state, allowing additional transaction log restores afterward. For a straightforward full-backup restore to a new database, NORECOVERY is still the correct exam answer here because the question's scenario implies this is part of a restore chain.

Note: In a real-world single-step full restore with no log restores planned, you might use RECOVERY. However, NORECOVERY is correct per this question's answer key, indicating the restore is expected to be followed by log restores.

Why the MOVE syntax differs between the two RESTORE options:

• Wrong option: MOVE 'db1_data' TO '...' — uses the logical file name (db1_data, db1_log)
• Correct option: MOVE 'D:\data\db1_mdf.mdf' TO '...' — uses the physical file path

For restoring to a new database name (db2) on a different server, you must relocate the physical files to avoid conflicts. Using physical paths in MOVE is the correct approach here.

Common Mistakes Summary

No community discussion yet for this question.