View Full Microsoft DP-800 Exam Dumps and Practice Test Dumps.

 

Question 61

Which SQL Server tool is primarily used to schedule automated jobs such as backups and maintenance tasks?

1. SQL Server Agent
2. SQL Server Browser
3. SQL Server Profiler
4. Database Engine Tuning Advisor

Correct Answer: 1

Explanation

SQL Server Agent is used to automate scheduled tasks on SQL Server. Administrators can create jobs that run tasks such as backups, index maintenance, data imports, and monitoring operations. Jobs can be scheduled to run at specific times or in response to certain conditions. SQL Server Agent can also send notifications when jobs succeed or fail. Proper scheduling helps reduce manual administration and ensures important maintenance operations happen consistently.

Question 62

Which SQL Server Agent object defines a sequence of actions that SQL Server Agent should execute?

1. Alert
2. Job
3. Operator
4. Schedule

Correct Answer: 2

Explanation

A SQL Server Agent job defines one or more tasks that the Agent should execute. A job can contain multiple steps, use schedules, and respond to success or failure conditions. For example, one job might run a backup command followed by a verification step. Jobs can also be configured to send notifications through operators. Administrators should monitor job history regularly because a scheduled job is useful only when it executes successfully and produces the expected result.

Question 63

Which SQL Server Agent object determines when a job should run?

1. Schedule
2. Alert
3. Step
4. Operator

Correct Answer: 1

Explanation

A SQL Server Agent schedule defines when and how frequently a job runs. A schedule can specify recurring times, dates, and frequency patterns. For example, a backup job might be scheduled to run every night. Separating schedules from job steps allows administrators to control execution timing without changing the actual job logic. Scheduling should consider system workload so that resource-intensive maintenance tasks do not unnecessarily interfere with business operations.

Question 64

Which SQL Server Agent feature can notify an administrator when a job fails?

1. Operator
2. Table
3. View
4. Schema

Correct Answer: 1

Explanation

An SQL Server Agent operator represents a person or group that can receive notifications about jobs and alerts. Administrators can configure a job to notify an operator when it succeeds, fails, or completes. This helps ensure that important automation problems are noticed quickly. Notifications are especially useful for backup jobs and other critical maintenance tasks. Operators and notification settings should be tested so that administrators know alerts will actually reach the intended recipients.

Question 65

Which SQL Server backup type contains all data required to restore the database to the point when the backup was created?

1. Differential backup
2. Full backup
3. Transaction log backup
4. Partial transaction backup

Correct Answer: 2

Explanation

A full database backup contains the database data needed to restore the database to the point when the backup was created. It provides the foundation for many restore strategies. Differential and transaction log backups can then be used to restore changes made after the full backup. Administrators should design backup schedules according to recovery requirements, available storage, and acceptable recovery time. A backup strategy should also include regular restore testing rather than simply assuming that backups are valid.

Question 66

What does a differential database backup contain?

1. Only system tables
2. Changes since the most recent full backup
3. Changes since the previous differential backup
4. Only transaction log records

Correct Answer: 2

Explanation

A differential backup contains changes made since the most recent full database backup. As more changes occur, differential backups can become larger because they continue to include all changed data since that full backup. During a restore, the latest full backup and the appropriate differential backup are generally required, followed by any required transaction log backups. Differential backups can reduce restore complexity compared with applying many smaller incremental-style backups.

Question 67

Which SQL Server backup type records transaction log information for point-in-time recovery?

1. Full backup
2. Differential backup
3. Transaction log backup
4. Snapshot backup

Correct Answer: 3

Explanation

A transaction log backup captures transaction log records that have not already been backed up. It supports point-in-time recovery for databases using a recovery model that supports log backups. Administrators can use a sequence of log backups to restore a database to a specific point before a failure or accidental change. Log backups must be managed as a chain, so missing or corrupted log backups can affect the ability to perform the desired recovery.

Question 68

Which recovery model supports transaction log backups and point-in-time recovery in SQL Server?

1. Simple
2. Full
3. Basic
4. Temporary

Correct Answer: 2

Explanation

The Full recovery model supports transaction log backups and point-in-time recovery. This allows administrators to restore a database to a specific moment, assuming the required backup chain is available. The Simple recovery model does not support regular transaction log backups for point-in-time recovery. The appropriate recovery model depends on business requirements, such as how much data loss can be tolerated and how quickly the database must be recovered. Recovery planning should be based on actual business needs.

Question 69

Which term describes the maximum amount of data that an organization is willing to lose after a failure?

1. RTO
2. RPO
3. SLA
4. MTTR

Correct Answer: 2

Explanation

RPO, or Recovery Point Objective, defines the maximum acceptable amount of data loss measured in time. For example, an RPO of 15 minutes means the organization aims to lose no more than approximately 15 minutes of data after a failure. RPO affects backup frequency, replication, and other data-protection strategies. It is different from RTO, which focuses on how quickly a service must be restored. Both values should be defined before designing a recovery solution.

Question 70

What does RTO represent in a disaster recovery plan?

1. Maximum acceptable recovery time
2. Maximum amount of data loss
3. Backup storage size
4. Database transaction count

Correct Answer: 1

Explanation

RTO, or Recovery Time Objective, defines how quickly a service or system should be restored after an outage. For example, an RTO of one hour means the organization expects the service to be available again within approximately one hour. RTO affects recovery architecture, automation, staffing, and infrastructure choices. It works together with RPO: RPO determines acceptable data loss, while RTO determines acceptable downtime. Both should be tested through recovery exercises.

Question 71

Which Azure service provides a managed relational database platform based on SQL Server?

1. Azure SQL Database
2. Azure Blob Storage
3. Azure Files
4. Azure Data Lake Storage

Correct Answer: 1

Explanation

Azure SQL Database is a fully managed relational database service based on the SQL Server database engine. Microsoft manages many infrastructure tasks such as hardware, operating system maintenance, and much of the platform administration. This allows database professionals to focus more on database configuration, security, performance, and application requirements. Azure SQL Database supports features such as automated backups, scaling options, monitoring, and high availability capabilities depending on the selected service tier.

Question 72

Which Azure service provides SQL Server database capabilities on a managed virtual machine?

1. Azure SQL Managed Instance
2. Azure SQL Database
3. Azure Synapse serverless SQL
4. Azure Table Storage

Correct Answer: 1

Explanation

Azure SQL Managed Instance is a managed database service designed to provide a high level of compatibility with SQL Server while reducing infrastructure administration. It supports many SQL Server features that may not be available in Azure SQL Database. This can make it useful when migrating existing SQL Server workloads that require broader instance-level capabilities. Microsoft manages the underlying infrastructure, while administrators remain responsible for database configuration, security, performance, and workload design.

Question 73

Which Azure service is designed for enterprise data warehousing and large-scale analytics?

1. Azure Synapse Analytics
2. Azure Files
3. Azure App Service
4. Azure DNS

Correct Answer: 1

Explanation

Azure Synapse Analytics is an analytics platform designed for large-scale data warehousing and analytics workloads. It brings together technologies for querying and analyzing large amounts of data and can integrate with various Azure data services. Synapse is useful when workloads require analytical processing rather than simply serving transactional application requests. Database professionals should understand the difference between transactional and analytical workloads when selecting an appropriate platform.

Question 74

Which SQL Server index type determines the physical or logical organization of table data around its key?

1. Clustered index
2. Hash table
3. Bitmap-only index
4. Text index

Correct Answer: 1

Explanation

A clustered index organizes the table’s data around the clustered index key. Because a table can have only one clustered index, choosing the key requires careful consideration. Clustered indexes are often useful for columns frequently used for range queries or other access patterns that benefit from ordered data. They also affect how nonclustered indexes reference the underlying rows. Index design should be based on actual query workloads rather than simply adding indexes to every column.

Question 75

How many clustered indexes can a standard SQL Server table have?

1. Unlimited
2. Two
3. One
4. One per column

Correct Answer: 3

Explanation

A standard SQL Server table can have only one clustered index because the clustered index determines the table’s data organization. The table cannot be physically organized according to two different clustered keys at the same time. However, a table can have multiple nonclustered indexes. Choosing the clustered index carefully is important because it can affect range queries, joins, inserts, and the storage behavior of other indexes.

Question 76

Which index type stores a separate index structure from the underlying table data?

1. Nonclustered index
2. Clustered index
3. Table partition
4. Heap index

Correct Answer: 1

Explanation

A nonclustered index is stored separately from the underlying table data and contains indexed key values along with information that helps SQL Server locate the corresponding rows. A table can have multiple nonclustered indexes. They can greatly improve query performance when designed around common search, join, and sorting patterns. However, each additional index requires storage and maintenance during data modifications. Administrators should monitor index usage before creating many indexes.

Question 77

Which SQL Server feature divides a large table or index into smaller logical sections based on a partitioning key?

1. Replication
2. Partitioning
3. Compression
4. Mirroring

Correct Answer: 2

Explanation

Table and index partitioning divides large data structures into smaller partitions based on a partitioning function and scheme. This can improve manageability and may improve performance for queries that access only specific partitions. Partitioning is particularly useful for large tables with natural ranges, such as dates. It does not automatically make every query faster. Proper partition design requires understanding data distribution, query patterns, maintenance requirements, and storage architecture.

Question 78

Which SQL Server feature can reduce storage requirements by storing data in a more compact form?

1. Compression
2. Replication
3. Mirroring
4. Index fragmentation

Correct Answer: 1

Explanation

SQL Server data compression reduces the storage space required for certain data structures. It can also reduce I/O because fewer pages may need to be read from storage, although compression and decompression consume CPU resources. SQL Server supports row and page compression for appropriate workloads. Compression should be evaluated using actual workload characteristics because the performance impact depends on the hardware, data, and queries involved.

Question 79

Which SQL Server feature can identify unused or missing index information to help with performance analysis?

1. Dynamic management views
2. Database diagrams
3. Stored procedures only
4. SQL Server Browser

Correct Answer: 1

Explanation

Dynamic management views can provide information about index usage, missing index recommendations, query activity, and other database performance details. Administrators can use these views as one source of evidence when deciding whether index changes are needed. Missing-index recommendations should not be applied blindly because adding an index can increase storage and write overhead. Index decisions should consider existing indexes, workload patterns, query plans, and the overall database design.

Question 80

Which database maintenance activity can help reduce fragmentation in appropriate SQL Server indexes?

1. Rebuild or reorganize indexes
2. Delete all indexes
3. Restart SQL Server
4. Drop and recreate the database

Correct Answer: 1

Explanation

Index fragmentation can sometimes be addressed by reorganizing or rebuilding indexes. Reorganizing is generally a lighter operation, while rebuilding creates a new index structure and can require more resources. The correct choice depends on fragmentation level, workload, edition, and maintenance requirements. Administrators should not rebuild every index automatically without checking whether maintenance is actually needed. A good maintenance strategy uses monitoring and appropriate thresholds instead of performing expensive operations unnecessarily.