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

Question

Hotspot Question You have SQL Server on an Azure virtual machine that contains a database named DB1. The database reports a CHECKSUM error. You need to recover the database. How should you complete the statements? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Answer:

Options

• __typehotspot
• variantdropdown

Explanation

This question tests knowledge of SQL Server database recovery from a CHECKSUM error using DBCC CHECKDB and RESTORE commands with appropriate repair options.

Approach. To recover from a CHECKSUM error in SQL Server, you first run DBCC CHECKDB('DB1') to assess the damage and determine the minimum repair level needed. If repair is possible without data loss, you use ALTER DATABASE DB1 SET SINGLE_USER to put the database in single-user mode, then run DBCC CHECKDB('DB1', REPAIR_REBUILD) for non-data-loss repairs or DBCC CHECKDB('DB1', REPAIR_ALLOW_DATA_LOSS) if data loss is acceptable. After repair, you restore multi-user access with ALTER DATABASE DB1 SET MULTI_USER. If backup restoration is preferred, you use RESTORE DATABASE DB1 FROM DISK with the WITH RECOVERY option to bring the database online after restoring the most recent clean backup.

Concept tested. SQL Server database integrity, CHECKSUM error recovery using DBCC CHECKDB with repair options (REPAIR_REBUILD or REPAIR_ALLOW_DATA_LOSS), and proper database mode management (SINGLE_USER/MULTI_USER) during repair operations on Azure VMs.

Reference. https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql

No community discussion yet for this question.