DP-300 Configure and manage automation of tasks Practice Test 6 — 15 Questions

Question 1

You are designing an automated backup strategy for Azure SQL Database. The compliance policy requires point-in-time restore (PITR) for the last 35 days, and long-term retention (LTR) for 7 years. What is the minimal number of LTR policies needed?

Accepted Answer

One LTR policy per database.. Option C is correct because LTR policies are per database, so you need one policy for each database. Option A is wrong because one policy per server is not possible; policies are database-level. Option B is wrong because two policies per server is incorrect. Option D is wrong because one policy per elastic pool is incorrect.

Answer

One LTR policy per elastic pool.

Answer

Two LTR policies per server (weekly and yearly).

Answer

One LTR policy per server.

Question 2

Your team uses GitHub Actions for CI/CD. You need to automatically deploy schema changes to an Azure SQL Database after a pull request merge. The solution must use a service principal with minimal permissions. Which authentication method should the workflow use?

Accepted Answer

Azure CLI action using a user-assigned managed identity for the GitHub runner.. Option B is correct because Azure CLI with az sql db deploy action and managed identity integrates securely with GitHub Actions. Option A is wrong because SQL authentication requires credentials in secrets, which is less secure. Option C is wrong because connection strings in variables are not recommended. Option D is wrong because service principal with secret is acceptable but managed identity is more secure.

Answer

Service principal with a client secret stored in GitHub Secrets.

Answer

Environment variables containing the connection string.

Answer

SQL authentication using a user name and password stored in GitHub Secrets.

Question 3

You need to run a complex T-SQL script on an Azure SQL Database every hour. The script performs data transformations that must be logged for auditing. Which native Azure service should you use?

Accepted Answer

Elastic Database Jobs with a T-SQL job step.. Option D is correct because Elastic Database Jobs are designed for scheduled T-SQL script execution across databases with logging. Option A is wrong because SQL Agent is not available in Azure SQL Database single database. Option B is wrong because Data Factory is for ETL, not simple script execution. Option C is wrong because Automation Runbooks are for PowerShell, not T-SQL directly.

Answer

SQL Agent job on the Azure SQL Database.

Answer

Azure Data Factory with a stored procedure activity.

Answer

Azure Automation Account with a PowerShell runbook that uses Invoke-SqlCmd.

Question 4

You have an Azure SQL Database that needs to be automatically scaled up during peak hours and scaled down during off-peak. The solution must use native Azure capabilities without custom code. What should you use?

Accepted Answer

Create Elastic Database Jobs that run ALTER DATABASE to change the service objective at scheduled times.. Option B is correct because Elastic Database Jobs can be used to run ALTER DATABASE statements on a schedule. Option A is wrong because Autoscale is not supported for Azure SQL Database. Option C is wrong because Azure Functions would require custom code. Option D is wrong because Elastic Pool autoscale only adjusts per-database DTUs, not the pool size itself.

Answer

Use Azure Functions with timer trigger and PowerShell to change the pricing tier.

Answer

Configure autoscale for the elastic pool that contains the database.

Answer

Configure autoscale settings on the Azure SQL Database server.

Question 5

You are responsible for automating database maintenance on multiple Azure SQL Databases in an elastic pool. You need to rebuild fragmented indexes weekly. The solution should minimize resource contention. What should you implement?

Accepted Answer

Use Elastic Database Jobs with a T-SQL script that rebuilds indexes, and schedule it weekly.. Option C is correct because Elastic Database Jobs can run T-SQL scripts across databases in a pool with concurrency control. Option A is wrong because SQL Agent is not available in Azure SQL Database. Option B is wrong because manual scripts are not automated. Option D is wrong because automatic tuning does not handle index rebuilds; it manages index creation/drop.

Answer

Enable automatic tuning and set 'INDEX_REBUILD' option.

Answer

Create a SQL Agent job on each database to rebuild indexes during off-peak.

Answer

Write a PowerShell script that connects to each database sequentially and rebuilds indexes.

Question 6

Your organization uses Azure SQL Database and needs to automate email notifications when a database reaches 80% storage usage. Which native Azure feature can you use?

Accepted Answer

Create an Azure Monitor alert rule on the 'storage_percent' metric with an email action group.. Option A is correct because Azure Alert Rules can monitor metrics like storage percent and trigger email actions. Option B is wrong because SQL Agent is not available in Azure SQL Database. Option C is wrong because Elastic Jobs execute T-SQL, not send email directly. Option D is wrong because Database Mail is not available in Azure SQL Database.

Answer

Create a SQL Agent alert that fires when the storage is above 80% and sends an email.

Answer

Configure Database Mail to send alerts automatically.

Answer

Create an Elastic Database Job that checks storage and sends email via sp_send_dbmail.

Question 7

Which TWO of the following are valid methods to automate backups for Azure SQL Managed Instance? (Select exactly two.)

Accepted Answer

Schedule T-SQL BACKUP DATABASE TO URL statements via SQL Agent jobs.. Options A and D are correct. Azure SQL Managed Instance supports automated backups via the service (PITR and LTR) and also allows manual copy-only backups to Azure Blob Storage via T-SQL. Option B is wrong because Azure Backup does not natively back up SQL Managed Instance. Option C is wrong because Azure Site Recovery is for disaster recovery, not backups. Option E is wrong because VM backup is for IaaS VMs, not Managed Instance.

Answer

Use Azure Backup to schedule full backups.

Answer

Use Azure Site Recovery to replicate the instance.

Answer

Use Azure VM backup by installing the backup extension.

Question 8

Which THREE of the following are required to automate schema deployments to Azure SQL Database using Azure DevOps? (Select exactly three.)

Accepted Answer

A release pipeline with a 'Azure SQL Database deployment' task.. Options A, C, and D are correct. A service connection is needed to authenticate to Azure; a database project is the source of schema; a release pipeline automates deployment. Option B is wrong because a self-hosted agent is optional; Microsoft-hosted agents can be used. Option E is wrong because schema compare is a tool, not a requirement.

Answer

A schema compare tool to generate deployment scripts.

Answer

A self-hosted build agent with SQL tools installed.

Question 9

Which TWO of the following are native options to automate index maintenance on Azure SQL Database? (Select exactly two.)

Accepted Answer

Create Elastic Database Jobs that run index maintenance T-SQL scripts.. Options A and D are correct. Automatic tuning can automatically create/drop indexes. Elastic Database Jobs can run custom index maintenance scripts. Option B is wrong because SQL Agent is not available in Azure SQL Database. Option C is wrong because Automation Runbooks are for PowerShell, not T-SQL directly. Option E is wrong because Data Factory is for data movement.

Answer

Use Azure Automation PowerShell runbooks to invoke index rebuilds.

Answer

Schedule SQL Agent jobs with ALTER INDEX statements.

Answer

Use Azure Data Factory to copy data and rebuild indexes.

Question 10

Refer to the exhibit. You are reviewing an Elastic Database Job definition for Azure SQL Database. The job is scheduled to run once on January 15, 2026. Which statement about the job's target is correct?

Accepted Answer

The job will target all databases in the server except 'db1'.. Option D is correct because the target group uses membershipType 'Exclude' with members list containing one database, meaning all databases in the logical server except 'db1' will be targeted. Option A is wrong because the membership type is 'Exclude', not 'Include'. Option B is wrong because the target group is for a single database, but it excludes that database, so it targets all others. Option C is wrong because the target group is at the server level, not database level.

Answer

The job will target only the database named 'db1'.

Answer

The job will target the entire logical server.

Answer

The job will target the server and all its databases.

Question 11

Refer to the exhibit. You are reviewing an Azure Automation runbook configuration that performs backups. What is the most likely issue with this configuration?

Accepted Answer

The retention period (7 days) equals the backup interval (7 days), which could cause data loss if a backup fails.. Option B is correct because the backup schedule frequency is set to every 7 days, but the retentionDays is only 7, meaning each backup will be retained exactly until the next backup, leaving no overlap and potentially causing gaps. Option A is wrong because Standard_LRS is fine for backups. Option C is wrong because isEnabled is true. Option D is wrong because the description is not an issue.

Answer

The runbook is disabled (isEnabled: false).

Answer

The storage account type Standard_LRS is not suitable for backups.

Answer

The backup description is missing required information.

Question 12

Refer to the exhibit. An administrator runs this Azure CLI command. What is the immediate effect?

Accepted Answer

The OrdersDB database's service objective is updated to S2.. Option C is correct because the command changes the service objective to S2, which updates the performance level of the database. Option A is wrong because the command only updates the current database, not the server. Option B is wrong because it's an update, not a creation. Option D is wrong because S2 is a higher tier than S0? Actually S2 is higher than S0, but the command does not specify previous tier; it sets to S2.

Answer

The server's performance tier is changed to S2.

Answer

The database is scaled down from a higher tier to S2.

Answer

A new database named OrdersDB is created with S2 tier.

Question 13

You manage a critical Azure SQL Database that requires automated index maintenance. You need to ensure that index rebuilds only occur during low-usage periods defined in a schedule, while index reorganizations run more frequently. What is the most efficient way to implement this using Azure Automation?

Accepted Answer

Create an Elastic Database Job that runs a T-SQL script to rebuild and reorganize indexes based on fragmentation thresholds, scheduled via job schedule.. Azure Automation runbooks can execute T-SQL scripts, but they require maintaining a schedule and handling errors manually. Elastic Database Jobs are purpose-built for scheduling T-SQL against Azure SQL Databases, providing simple scheduling, retry logic, and built-in monitoring. Option A is correct. Option B is less efficient because it requires more manual setup. Option C is not designed for this. Option D lacks scheduling.

Answer

Configure SQL Agent on the Azure SQL Database logical server to run index maintenance jobs.

Answer

Use Azure Automation runbooks with the Invoke-Sqlcmd cmdlet to run index maintenance scripts on a schedule.

Answer

Deploy an Azure Function with a timer trigger that connects to the database and runs index maintenance commands.

Question 14

You are designing an automated backup retention policy for an Azure SQL Database. The business requirement is to retain daily backups for 30 days, weekly backups for 12 weeks, monthly backups for 12 months, and yearly backups for 7 years. Which backup retention type should you configure?

Accepted Answer

Long-term retention (LTR) policy. Long-term retention (LTR) is specifically designed to retain backups beyond the point-in-time restore (PITR) window, supporting daily, weekly, monthly, and yearly retention periods. Option B is correct. Option A is too short. Option C is not a feature. Option D is a feature of Azure SQL Managed Instance, not Azure SQL Database.

Answer

Point-in-time restore (PITR) retention

Answer

Backup vault with Azure Backup

Answer

Automated backup policy

Question 15

Your company uses Azure SQL Managed Instance and wants to automate the creation of new databases for each development sprint. Each database must be a copy of a template database with specific schema and data. You need to recommend an automation solution that minimizes manual steps and integrates with your CI/CD pipeline. What should you use?

Accepted Answer

Use an Azure DevOps pipeline with a PowerShell task that runs the 'az sql db copy' command or the Restore-AzSqlDatabase cmdlet.. Azure SQL Managed Instance supports database copy via T-SQL, which can be automated with Elastic Jobs or DevOps pipelines. However, for CI/CD integration, Azure DevOps with the SQL Server database project (DACPAC) and Azure CLI is the most standard approach. Option C is correct. Option A is unsupported. Option B is for Azure SQL Database. Option D requires more custom scripting.

Answer

Deploy an Azure Logic App with a SQL connector that triggers on a schedule.

Answer

Use the Azure portal's 'Create database from backup' feature.

Answer

Create an Elastic Database Job that runs CREATE DATABASE AS COPY OF...