CHFI Database and Application Forensics Practice Question

An organization uses Microsoft SQL Server 2019 with full recovery model. A database administrator accidentally executed a DROP TABLE statement. The transaction log was backed up immediately after the incident. Which forensic technique would allow the analyst to restore the dropped table?

⚠ Common exam trap

Candidates often think a simple transaction log restore (Option A) or a full backup restore (Option D) will suffice, failing to recognize that point-in-time recovery with a tail-log backup and STOPAT is required to skip the destructive DDL statement.

Answer choices

Why each option matters

Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.

Correct answer & explanation

✓

Perform a tail-log backup, then restore the full backup and all subsequent transaction log backups, stopping before the DROP TABLE.

Under the full recovery model, point-in-time recovery is required to undo the DROP TABLE. By performing a tail-log backup (to capture any transactions after the last log backup), then restoring the full backup and all subsequent transaction log backups with STOPAT or STOPBEFOREMARK to the moment just before the DROP TABLE, the analyst can recover the table without losing other transactions. This is the only method that preserves the dropped table's data while maintaining database consistency.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✗
Restore the transaction log backup taken after the DROP TABLE and apply it to the database.
Why it's wrong here
Applying a transaction log backup taken after the DROP TABLE statement re-executes that same DDL transaction during roll-forward, which deletes the table again. The log backup contains the drop as a committed operation, so restoring it moves the database forward through the drop rather than around it. A log restore alone also cannot run until a full database backup has been restored first.
✗
Use the RESTORE LOG statement with the NO_TRUNCATE option to recover the table.
Why it's wrong here
NO_TRUNCATE is a BACKUP LOG option, not a RESTORE option; it is used to create a tail-log backup when the data files are damaged or offline, preventing truncation of the log. Issuing RESTORE LOG ... WITH NO_TRUNCATE is invalid for restoring a dropped table because it neither applies backup files nor supports a stop-point. Recovery of the dropped table requires STOPAT/STOPBEFOREMARK point-in-time restore, not this flag.
✓
Perform a tail-log backup, then restore the full backup and all subsequent transaction log backups, stopping before the DROP TABLE.
Why this is correct
The correct procedure is to first back up the tail of the transaction log to capture all log records generated since the last backup, including the DROP TABLE transaction. Then restore the most recent full backup in NORECOVERY mode, followed by every subsequent transaction log backup using STOPAT (or STOPBEFOREMARK) set to a time just before the drop. This rolls the database forward to the pre-drop state while preserving all earlier committed changes.
✗
Restore the most recent full backup and ignore subsequent transaction log backups.
Why it's wrong here
Restoring only the most recent full backup and skipping the transaction log backups puts the database at the full backup's point-in-time, so every modification made after that backup is permanently lost. Although the table will still exist if the full backup predates the DROP, all later inserts, updates, and deletes are absent, and any log-backed transactions are not reapplied. To get back to the exact pre-drop database state, you must restore the log chain, not just the full backup.

Go deeper

CHFI Database and Application Forensics Practice Question

Why each option matters

Option-by-option breakdown

Related to this question