Exam 70-764 topic 1 question 302 discussion

DRAG DROP -
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You maintain a Microsoft SQL Server instance that contains the following databases SalesDb1, SalesDb2, and SalesDb3. Each database has tabled named
Products and Sales. The following table shows the configuration of each database.

The backup strategies for each database are described in the following table.

Each full or differential backup operation writes into a new file and uses a different sequence number. You observe the following database corruption issues.

SalesDb3 reports a number of database corruption issues related to error 823 and 824 when reading data pages. You must display the following information about the corrupted pages:
✑ database name
✑ impacted file id
✑ impacted file physical name
✑ impacted page id
✑ event type that identifies the error type
✑ error count
Users report performance issues when they run queries against SalesDb2. You plan to monitor query statistics and execution plans for SalesDb2 by using Query
Store. The monitoring strategy must meet the following requirements:
✑ Perform automatic data cleanup when query store disk usage reaches 500 megabyte (MB).
Capture queries based on resource consumption.

✑ Use a stale query threshold value of 60 days.
The query optimizer generates suboptimal execution plans for a number of queries on the Sales table in SalesDb2. You will create a maintenance plan that updates statistics for the table. The plan should only update statistics that were automatically created and have not been updated for 30 days. The update should be based on all data in the table.
Users reports that they encounter the following error when they query SalesDb1: "SQL Server detected a logical consistency-based I/O error: incorrect checksum
(expected: 0x5d672d9b; actual: 0xdd672d98). It occurred during a read of page (1.232) in database ID 12 at offset 0x000000001d0000 in file F:\Databases
\MSSQLServer Databases\MSSQL13.MSSQL2016\MSSQL\DATA\SalesDb1.mdt."
You must restore the impacted page from SalesDb1Full_1.bak. A single backup set named SalesDb1Log.bak was created since the latest full backup operation.
You need to restore the impacted page.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-
SQL segments to the answer area and arrange them in the correct order.
Select and Place:

Suggested Answer:
Step 1:

Restore page -
Start a page restore with a full database, file, or filegroup backup that contains the page. In the RESTORE DATABASE statement, use the PAGE clause to list the page IDs of all of the pages to be restored.
Step 2:
Restore log file with norecovery. Use the first file (FILE = 1).
Step 3:
Backup the tail-end of the log.
Create a new log backup of the database that includes the final LSN of the restored pages, that is, the point at which the last restored page is taken offline.
Step 4:
Restore database with recovery. Use second file (FILE = 2).
Restore the new log backup. After this new log backup is applied, the page restore is completed and the pages are now usable.
Example:
The following example restores four damaged pages of file B with NORECOVERY. Next, two log backups are applied with NORECOVERY, followed with the tail- log backup, which is restored with RECOVERY. This example performs an online restore. In the example, the file ID of file B is 1, and the page IDs of the damaged pages are 57, 202, 916, and 1016.
RESTORE DATABASE <database> PAGE='1:57, 1:202, 1:916, 1:1016'
FROM <file_backup_of_file_B>
WITH NORECOVERY;
RESTORE LOG <database> FROM <log_backup>
WITH NORECOVERY;
RESTORE LOG <database> FROM <log_backup>
WITH NORECOVERY;
BACKUP LOG <database> TO <new_log_backup>;
RESTORE LOG <database> FROM <new_log_backup> WITH RECOVERY;
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-pages-sql-server