HOTSPOT
You manage a sales database that uses the full recovery model. You use the following backup schedule:
The date is April 15, 2018. A developer creates a transaction named modificationtest and runs the transaction at 9:20 AM.
You determine that the change implemented by the transaction must be undone. You immediately back up the database log to L:LogbackupSalestaillog.bak using the NORECOVERY and INIT options. Then, you restore the full database backup from the previous night using the NORECOVERY option.
You need to recover as much data as possible without recovering the change implemented by the transaction.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
The question is totally wrong anyway. There’s 7 hourly transaction logs that would need to be applied after the full backup before you could apply that taillog
Restore LOG Sales FROM DISK =’L:\Logbackup\Saleslog.bak
WITH FILE=1 RECOVERY STOPATMARK Modificationtest
We should restore the normal transaction log (L:\Logbackup\Saleslog.bak) and not the tail-log backups since the question states that: You need to recover as much data as possible without recovering the change implemented by the transaction, as we know a Tail-Log Backup is backing up the transactions that have occurred since the last transaction log backup (thus will include the change implemented by the transaction)
sorry I missed a point….we should restore tail-log backup with file=1
restore log sales from disk = ‘L:\Logbackup\sales\taillog.bak’
with file=1 , recovery, stopbeforemark = ‘modificationtest’
we use stopbeforemark because of the statement….without recovering the change implemented by the transaction.
When you need to restore a database to a marked transaction you have to restore the last full backup and the transaction log backups until the marked transaction. You have the chance to recover the database to the mark including the marked transaction by using the WITH STOPATMARK clause. On the other hand, if you need to recover to the moment previous to the mark you use the WITH STOPBEFOREMARK clause. In order to recover to a transaction mark, the databases must be in the Full Recovery mode.
The answer is wrong
restore log sales from disk = ‘L:\Logbackup\sales\taillog.bak’
with file=1 , recovery, stopbeforemark = ‘modificationtest’
Agreed, using stopatmark would include the named transaction