Study guide
17.2 SQL Server Forensics: SSMS, ApexSQL, SQLCMD Transaction-Log Collection, Plan Cache & Trace Files
Computer Hacking Forensic Investigator CHFI 312-49
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Foundations of Digital Forensics & Cybercrime Investigation
1.1 Cybercrime Classifications, Forensic Science Principles & Legal Admissibility 1.2 RFC 3227 Order of Volatility, Live vs. Dead Acquisition & Forensic Readiness 1.3 Indicators of Compromise (IoCs), Cyber Attribution & Threat Intelligence 1.4 AI-Assisted Forensics, GitOps, Automation/Orchestration & Forensics-as-a-Service (FaaS)
2 Chapter 2: Modern Forensic Environments & Emerging Threats
2.1 Fileless Malware, In-Memory Execution & Living-off-the-Land (LotL) 2.2 IoT & Operational Technology (OT/SCADA) Forensic Fundamentals 2.3 Dark Web, Tor Network, Onion Routing & Hidden Services Forensics 2.4 Blockchain Transactions, Cryptocurrency Tracing & Digital Asset Investigations
3 Chapter 3: Legal Frameworks, Search Warrants & Evidence Rules
3.1 Federal Rules of Evidence (FRE 702, 901, 902, 1001-1003) & Daubert/Frye Standards 3.2 Fourth Amendment, Warrant Exceptions, Workplace Search & Consent Protocols 3.3 International Standards: ISO/IEC 27037, ACPO Principles & ENFSI Guidelines 3.4 Cybercrime Statutes, Privacy Compliance, CAN-SPAM & International Cooperation
4 Chapter 4: eDiscovery, Chain of Custody & Professional Ethics
4.1 Electronic Discovery Reference Model (EDRM) & Legal Spoliation Holds 4.2 Chain of Custody Documentation, Evidence Packaging & Tamper-Evident Handling 4.3 Expert Witness Testimony, Report Writing Standards & EC-Council Code of Ethics
5 Chapter 5: Storage Architecture, Boot Sequences & Media Forensics
5.1 Storage Media Architecture: Sectors, Clusters, RAM/File Slack & Unallocated Space 5.2 Partition Tables & Boot Structures: MBR, GPT, VBR, BIOS vs. UEFI Secure Boot 5.3 RAID Reconstruction (RAID 0, 1, 5, 6, 10) & SSD Forensic Challenges (TRIM, Wear Leveling)
6 Chapter 6: Windows File Systems: NTFS & FAT
6.1 NTFS Architecture: Master File Table ($MFT) Record Structure & Attribute Headers 6.2 NTFS Artifacts: Alternate Data Streams (ADS), Volume Shadow Copies (VSS) & $LogFile 6.3 FAT12/16/32 and exFAT Architecture, Directory Entries & File Deletion Mechanics
7 Chapter 7: Non-Windows File Systems & File Signatures
7.1 Linux File Systems: ext2/ext3/ext4 Inode Architecture, Superblocks & Journaling Forensics 7.2 macOS File Systems: HFS+ and Apple File System (APFS) Architecture & Snapshots 7.3 File Signatures, Magic Bytes, Hex Headers/Footers & File Carving Techniques
8 Chapter 8: Investigation Lifecycle, Lab Setup & First Response
8.1 Forensic Laboratory Accreditation: ISO/IEC 17025 Standards & Hardware/Software Controls 8.2 First Responder Duties: Crime Scene Containment, Electronic Triage & Volatile Triage 8.3 Physical Evidence Packaging, Anti-Static/Faraday Protection & Transport Security
9 Chapter 9: Forensic Acquisition & Verification Methodology
9.1 Hardware and Software Write-Blockers: Operational Validation & Bridge Verification 9.2 Bit-Stream Disk Imaging, Physical vs. Logical vs. Targeted Acquisition Methodology 9.3 Forensic Image Formats (RAW/DD, E01, AFF) & Cryptographic Hashing Verification (MD5, SHA-256)
10 Chapter 10: Anti-Forensics Techniques & Countermeasures
10.1 Steganography Detection: Spatial/Frequency Domain Steganalysis & Hidden Carrier Extraction 10.2 Timestomping Countermeasures: Comparing $STANDARD_INFORMATION vs $FILE_NAME Timestamps 10.3 Secure Wiping, Degaussing, File Shredding Countermeasures & Residual Artifact Recovery
11 Chapter 11: Windows Operating System Forensics
11.1 Windows Registry Forensics: SYSTEM, SOFTWARE, SAM & NTUSER.DAT Hive Analysis 11.2 Execution Artifacts: Prefetch (.pf), Shimcache (AppCompatCache), Amcache.hve & UserAssist 11.3 User Activity Artifacts: ShellBags, LNK Shortcut Files, Jump Lists & Recycle Bin ($I/$R) 11.4 Windows Event Log Forensics: Security Audit IDs (4624, 4625, 4688, 4720, 7045) & EVTX Parsing
12 Chapter 12: Memory Forensics & Malware Analysis
12.1 Volatile Memory Acquisition: RAM Capture, Crash Dumps, Hiberfil.sys & Pagefile.sys 12.2 Memory Analysis with Volatility 3: pslist, pstree, malfind, netscan & Vadinfo 12.3 Static Malware Triage: Strings, PE Headers, Import Tables, Hashes & YARA Rule Matching 12.4 Dynamic Malware Analysis: Sandboxing, Process Monitor (Procmon), Regshot & Network Callouts 12.5 .NET Managed Malware Analysis & the GOOTLOADER Fileless Infection Chain
13 Chapter 13: Network, Web Application & Email Forensics
13.1 Network Forensics: PCAP Analysis, Protocol Dissection, Snort/Suricata & Flow Analysis 13.2 Web Server Forensics: Apache/IIS Access Logs, SQL Injection, XSS & Web Shell Detection 13.3 Email Forensics: RFC 5322 Headers, Authentication (SPF, DKIM, DMARC) & Mbox/PST Extraction 13.4 SIEM Event Correlation, Cross-Source Timeline Analysis & Super-Timeline Construction 13.5 Investigating Directory Traversal, Command Injection, Parameter Tampering, XXE, Cookie Poisoning & Brute Force 13.6 Network Device & Service Log Forensics: Firewall, IDS, Honeypot, Router, DHCP, Switch, VPN, DNS & SSH 13.7 Wireless Network Forensics: Rogue APs, Evil Twins, MAC Spoofing, Deauthentication & Jamming
14 Chapter 14: Mobile, Cloud & IoT Forensics
14.1 Mobile Device Forensics: Manual, Logical, File System & Physical Extraction (Android/iOS) 14.2 Mobile Artifact Analysis: Call Logs, SMS/Chat SQLite Databases, Location Data & Plists 14.3 Cloud Forensics: AWS CloudTrail, Azure Activity Logs & Multi-Tenant Evidence Collection 14.4 IoT Forensics: UART/JTAG Physical Interfaces, Firmware Extraction & Sensor Telemetry
15 Chapter 15: Commercial & Open-Source Forensic Suites
15.1 Commercial Forensic Platforms: EnCase Forensic, FTK (Forensic Toolkit) & FTK Imager 15.2 Open-Source Forensic Platforms: Autopsy, The Sleuth Kit (TSK) & SIFT/CAINE Workstations 15.3 Specialized DFIR Utilities: Eric Zimmerman Tools (EZ Tools) & Sysinternals Forensic Suite
16 Chapter 16: Analysis Toolkits, Python Scripting & Tool Validation
16.1 Reverse Engineering & Password Cracking: Ghidra, IDA Pro, Hashcat & John the Ripper 16.2 Python for Digital Forensics: Parsing Raw Artifacts, Custom Scapy Scripts & Evidence Automation 16.3 Forensic Tool Testing & Validation: NIST CFTT Methodology, ISO/IEC 17025 Standards & Daubert Compliance
17 Chapter 17: Database Forensics & Web Application Firewalls
17.1 MySQL Forensics: Internal Architecture, Data Directory, Information Schema & Utility Programs 17.2 SQL Server Forensics: SSMS, ApexSQL, SQLCMD Transaction-Log Collection, Plan Cache & Trace Files 17.3 Web Application Firewall (WAF) Architecture, Audit Logs, Benefits & Limitations
18 Chapter 18: Social, Virtualized, Multimedia & Browser Investigations
18.1 Social Media, Social Engineering, Insider Threat & Identity Theft Forensics 18.2 Virtualization, Container & Microservices Forensics, Bluetooth and Electron Chat Applications 18.3 Multimedia Forensics: Image, Audio & Video Authentication, EXIF, PRNU and Deepfake Detection 18.4 Browser Artifact Recovery, Private Browsing, USB Enumeration, Partition Recovery & Packer Unpacking Tools
17.2 SQL Server Forensics: SSMS, ApexSQL, SQLCMD Transaction-Log Collection, Plan Cache & Trace Files
Key Takeaways Explain This Topic AI
A SQL Server database consists of an MDF primary data file, optional NDF secondary data files, and an LDF transaction log; the LDF is the forensic equivalent of a journal because it records every modification before it is written to the data file.
The undocumented fn_dblog() function reads the active transaction log of an attached database and fn_dump_dblog() reads a log backup, exposing INSERT, UPDATE, and DELETE operations that the tables themselves no longer show.
The plan cache, queried through sys.dm_exec_cached_plans joined to sys.dm_exec_sql_text, retains the text of recently executed statements and is destroyed by a service restart or a DBCC FREEPROCCACHE, making it volatile evidence.
The default trace writes rolling log_N.trc files in the MSSQL\LOG directory and records schema changes, security changes, and object creation even when no explicit auditing was configured.
Order of volatility inside a database instance runs plan cache and active sessions first, then the active transaction log, then trace and error logs, and finally the MDF/NDF data files, which persist in the image.
Last updated: September 2026
17.2 SQL Server Forensics: SSMS, ApexSQL, SQLCMD Transaction-Log Collection, Plan Cache & Trace Files
Quick Answer: Blueprint Domain 4 requires collecting volatile database data , collecting primary data files and active transaction logs using SQLCMD , collecting active transaction logs using SQL Server Management Studio , collecting database plan cache , and collecting SQL Server trace files and error logs ; Domain 5 adds database forensics using SQL Server Management Studio and ApexSQL DBA . SQL Server stores data in MDF (primary), NDF (secondary), and LDF (transaction log) files. The LDF is the journal that proves what was deleted, and the plan cache is volatile — it dies with the service.
Physical File Architecture
File Extension Contents Forensic role
Primary data file .mdf Pages holding tables, indexes, and the database's own catalog Current state; deleted rows may persist in unallocated pages
Secondary data file .ndf Additional pages when a database spans volumes Must be collected — omitting an NDF yields an unattachable database
Transaction log .ldf Write-ahead log of every modification The journal : proves inserts, updates, and deletes including their row values
Default trace log_N.trc Rolling audit of schema/security events Present even with no configured auditing
Error log ERRORLOG , ERRORLOG.N Startup, shutdown, failed logins, backup activity Brackets the window and records authentication failures
Data files live under ...\MSSQL\DATA\ , and the trace and error logs under ...\MSSQL\LOG\ . Resolve the real paths rather than assuming: SELECT name, physical_name, type_desc, state_desc, size FROM sys.master_files;
SELECT SERVERPROPERTY('ErrorLogFileName'), SERVERPROPERTY('InstanceDefaultDataPath');
Important
Never copy a live MDF/LDF pair with a file-copy utility. Data files of a running instance are locked and internally inconsistent mid-checkpoint; a raw copy produces a database that will not attach. Either take a BACKUP DATABASE ... WITH COPY_ONLY (which does not disturb the differential backup chain), or image the volume with the instance cleanly stopped. Record which path was chosen and why.
Order of Volatility Inside a Database Instance
The RFC 3227 principle applies within the DBMS just as it does to a host:
Plan cache, active sessions, open transactions, locks — destroyed by a service restart, a memory-pressure eviction, or DBCC FREEPROCCACHE .
Active transaction log (LDF) — truncated on checkpoint in simple recovery model, or on log backup in full recovery model.
Default trace and error logs — roll over by size and count.
MDF/NDF data files — persist in the forensic image.
Collect in that order. An examiner who shuts the instance down "to image it cleanly" first has destroyed items 1 and much of 2.
Collecting Volatile Database Data with SQLCMD
sqlcmd runs from a trusted external binary on the responder's media and writes output straight to evidence storage. :: Windows-authenticated connection (-E), no headers, comma-separated, to evidence drive
sqlcmd -S TARGET\SQLPROD -E -W -s"," -h-1 ^
-Q "SELECT session_id, login_name, host_name, program_name, client_net_address, login_time, status FROM sys.dm_exec_sessions s JOIN sys.dm_exec_connections c ON s.session_id=c.session_id" ^
-o E:\evidence\sessions.csv
- :: Open transactions that have not yet committed
- sqlcmd -S TARGET\SQLPROD -E -Q "SELECT * FROM sys.dm_tran_active_transactions" -o E:\evidencective_tran.csv
:: COPY_ONLY backup capturing data and the active log together, without breaking the backup chain
sqlcmd -S TARGET\SQLPROD -E -Q "BACKUP DATABASE [AppDB] TO DISK='E:\evidence\AppDB.bak' WITH COPY_ONLY, CHECKSUM, INIT"
sqlcmd -S TARGET\SQLPROD -E -Q "BACKUP LOG [AppDB] TO DISK='E:\evidence\AppDB_log.trn' WITH COPY_ONLY, CHECKSUM, INIT"
Hash every output file at the point of creation. WITH CHECKSUM makes the backup self-verifying, which is a useful integrity statement in the report alongside the examiner's own hash.
SQL Server Management Studio (SSMS) performs the same collection through a GUI — Tasks ▸ Back Up with the Copy-only checkbox, and Reports ▸ Standard Reports for schema-change history — and additionally provides the query window used for the transaction-log reconstruction below. SSMS is convenient for analysis on a restored copy ; scripted sqlcmd is preferred on a live target because the exact commands are reproducible and logged.
Reconstructing Deleted Data from the Transaction Log
This is the heart of SQL Server forensics. The write-ahead log records each modification before the data page is updated, so the log holds operations the tables no longer reflect. -- Read the ACTIVE transaction log of an attached database
- SELECT [Current LSN], Operation, Context, AllocUnitName,
- [Transaction Name], [Transaction ID], [Begin Time], [End Time], [Transaction SID]
- FROM fn_dblog(NULL, NULL)
- WHERE Operation IN ('LOP_DELETE_ROWS','LOP_MODIFY_ROW','LOP_INSERT_ROWS')
- ORDER BY [Current LSN];
- - Read a transaction LOG BACKUP file (evidence copy, no live instance needed)
- SELECT [Current LSN], Operation, AllocUnitName, [Transaction Name], [Begin Time]
- FROM fn_dump_dblog(NULL,NULL,'DISK',1,'E:\evidence\AppDB_log.trn',
- DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
- DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
- DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
- DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
- DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
- DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
- DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT);
Operations that matter: LOP_DELETE_ROWS , LOP_INSERT_ROWS , LOP_MODIFY_ROW , LOP_BEGIN_XACT / LOP_COMMIT_XACT (bracketing a transaction with its Begin Time and the Transaction SID that identifies the security principal), and LOP_FORMAT_PAGE (page reinitialization, produced by TRUNCATE TABLE ).
The RowLog Contents 0 column holds the raw row bytes, which must be decoded against the table's column definitions to reproduce the deleted values — the step commercial tools automate.
Important
fn_dblog and fn_dump_dblog are undocumented and unsupported . That does not make them inadmissible, but it obliges the examiner to validate the output independently — typically by restoring the backup to a point in time before the deletion and comparing the reconstructed rows to the restored table. Validation against a known-good reference is exactly the Daubert answer for an unsupported technique.
Recovery-model caveat: under the simple recovery model, the log truncates at every checkpoint, so the window of recoverable history may be minutes. Under full recovery, history extends back to the last log backup, and the chain of .trn backups extends it further. Establish SELECT name, recovery_model_desc FROM sys.databases; before promising a client that deleted rows are recoverable.
Plan Cache and DMV Capture
The plan cache holds the text of recently executed statements , including ad-hoc SQL an application never stored anywhere. SELECT TOP 500
- qs.last_execution_time, qs.execution_count,
- DB_NAME(st.dbid) AS database_name,
- st.text AS statement_text
- FROM sys.dm_exec_query_stats qs
- CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
- ORDER BY qs.last_execution_time DESC;
- SELECT cp.objtype, cp.usecounts, cp.size_in_bytes, st.text
- FROM sys.dm_exec_cached_plans cp
- CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
- WHERE st.text LIKE '%xp_cmdshell%' OR st.text LIKE '%OPENROWSET%' OR st.text LIKE '%sp_OACreate%';
Those three strings are the classic SQL-Server-to-host escalation primitives: xp_cmdshell executes operating-system commands, OPENROWSET with BULK reads server files, and sp_OACreate instantiates OLE automation objects. Finding any of them in the plan cache on a server whose application never uses them is a strong compromise indicator — and finding sp_configure 'xp_cmdshell', 1 in the default trace shows the attacker enabling it.
The plan cache is evidence with a fuse. It is cleared by a service restart, by DBCC FREEPROCCACHE , by memory pressure, and by certain configuration changes. Capture it in the first minutes of live response or lose it.
Default Trace and Error Log
The default trace runs unless explicitly disabled and captures object creation and deletion, sp_configure changes, login failures, database and permission changes, and log-file growth. SELECT TE.name AS EventClass, T.DatabaseName, T.ObjectName, T.LoginName,
- T.HostName, T.ApplicationName, T.StartTime, T.TextData
- FROM sys.fn_trace_gettable(
- CONVERT(nvarchar(260), (SELECT value FROM sys.fn_trace_getinfo(1) WHERE property = 2)), DEFAULT) T
- JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
- ORDER BY T.StartTime DESC;
The .trc files sit in the MSSQL\LOG directory as log_1.trc … log_5.trc , rolling by size — another reason to preserve early.
The error log ( ERRORLOG , with archived ERRORLOG.1 … ERRORLOG.6 ) yields service start and stop times, Login failed for user '...' entries with the error state that distinguishes a bad password from a nonexistent login, backup and restore events, and evidence of a configuration change requiring restart. It is a plain text file readable directly from the image, or queryable live with EXEC sp_readerrorlog .
Extended Events (the modern replacement for SQL Trace) writes .xel files that are read with sys.fn_xe_file_target_read_file and provide far richer capture where the organization configured a session in advance.
ApexSQL and Commercial Log Readers
The blueprint names ApexSQL DBA explicitly. The relevant component is ApexSQL Log , a transaction-log reader that parses .ldf files and log backups and renders operations as readable SQL with the originating user, application, and host — and generates UNDO scripts that reverse a destructive operation and REDO scripts that replay it.
- Tool Role
- ApexSQL Log Transaction-log reading, row-level recovery, UNDO/REDO script generation
- ApexSQL Recover Recovery of deleted rows, dropped tables, and BLOB data from MDF and backups
- ApexSQL Audit Continuous compliance auditing, generating the trail a later investigation relies on
Redgate SQL Data Compare Diff a restored pre-incident copy against the current database to enumerate every changed row
Warning
UNDO generation is a remediation feature, not a forensic one. Running an UNDO script against the evidence database modifies it and destroys the state under examination. Generate UNDO only against a working copy , and keep the original restored image untouched and hashed.
Whatever tool is used, it must be validated under NIST CFTT-style methodology — demonstrated against a database with a known deletion history — before its output supports testimony.