Procedures and Methodology · Topic 7 of 11

Database, Windows Log, Cloud and OT Data Acquisition

Collect SQL Server volatile data, data files, transaction logs, plan cache and error logs, plus Windows logs, and acquire from cloud and OT systems.

5 min

In a nutshell

Capture volatile database data (sessions, connections, plan cache) before files.
.mdf is the primary data file, .ndf secondary, .ldf the transaction log.
The recovery model decides how long log entries remain.

What it is

Databases hold the records attackers target and insiders misuse. A database server also keeps volatile state, such as active sessions and cached query plans, that disappears when the service restarts. Collecting it correctly means capturing the volatile part first and the files next, without changing either.

This page uses Microsoft SQL Server as the model. It also covers Windows logs, and acquisition from cloud and OT environments. The exam asks what a given artifact is, which tool collects it, and what to collect first.

How it works

Collecting volatile database data

Volatile database data lives in memory and is lost on restart. In SQL Server it is exposed through dynamic management views (DMVs).

Item	Example view
Active sessions and users	`sys.dm_exec_sessions`
Current requests and statements	`sys.dm_exec_requests`
Client connections and addresses	`sys.dm_exec_connections`
Cached query plans	`sys.dm_exec_cached_plans`

Query these first and save the output outside the server. Take care to run the minimum needed and note the time, because each query also changes server state slightly.

Collecting data files and active transaction logs with SQLCMD

sqlcmd is the command line client for SQL Server. It lets an examiner run queries without a graphical tool, which is useful on a live server or through a remote session.

sqlcmd -S localhost -E -Q "SELECT session_id, login_name, host_name, program_name FROM sys.dm_exec_sessions" -o sessions.txt

The options are server (-S), Windows authentication (-E), query (-Q) and output file (-o).

Primary data files and transaction logs

File	Extension	Contents
Primary data file	`.mdf`	Main database data and system tables
Secondary data file	`.ndf`	Optional additional data
Transaction log	`.ldf`	Record of changes made by transactions

The transaction log records inserts, updates and deletes, so it can show what changed and when. Files of a running database are locked, so the options are to stop the service (which loses volatile data), take a backup, or copy using a snapshot. Choose with the lead investigator, hash the copies and record the method.

Active transaction logs in SQL Server Management Studio

SQL Server Management Studio (SSMS) is the graphical client. It can run the same queries and views, and show database properties such as file locations and recovery model. The recovery model matters: in simple recovery the log is reused and old entries vanish, while full recovery keeps them until a log backup. Collect the active log before any backup truncates it.

Collecting the database plan cache

The plan cache holds execution plans for recently run queries. It can reveal which queries ran against sensitive tables, even if no one logged them. Query sys.dm_exec_cached_plans together with the functions that return the SQL text and the plan. It is cleared by restart or memory pressure, so it is collected early.

Collecting trace files and error logs

Source	Use
Error log	Startup, login failures, errors and server messages. Stored as the `ERRORLOG` files in the instance's `Log` folder.
Default trace	Records configuration changes and DDL events. Files end with `.trc`.
Audit logs	If auditing is enabled, records of logins and data access

Failed logins in the error log may point to brute force attempts, and DDL events in a trace can show a table dropped or a login created.

Collecting Windows logs

Windows event logs are stored as .evtx files, normally in C:\Windows\System32\winevt\Logs. The main channels are Security, System and Application.

Event ID	Meaning
4624	Successful logon
4625	Failed logon
4688	New process created (needs auditing enabled)
1102	Security audit log cleared
7045	New service installed (System log)

Export the logs (for example with wevtutil epl Security security.evtx) rather than reading them on the host, hash the export, and gather logs from related servers too.

Data acquisition in the cloud

Cloud acquisition depends on what the provider exposes. Use the provider's control plane: snapshot the virtual disk, export audit logs, and copy object storage versions. Preserve first, because instances and logs can be deleted. Capture memory only where the platform allows it, and ask the provider for data you cannot reach, using legal process. Keep a record of account, region, resource IDs and time zone.

Data acquisition on OT systems

OT systems prioritize safety, so acquisition is passive and coordinated with operators. Copy historian data, controller project files, configuration backups and network captures. Avoid interrupting a running process, and image engineering workstations rather than live controllers.

A worked example

A suspected data theft involves a customer database. The investigator connects over a remote session and runs sqlcmd to save the sessions and connections to external storage. Then the cached plan list is exported, and the transaction log and error log are copied after the DBA takes a snapshot.

Windows Security logs are exported, hashed and compared. An event 4625 spike precedes a 4624 logon from an unusual host.

How the exam asks about this

"Which file holds the SQL Server transaction log?" → The .ldf file.
"Which command line tool queries SQL Server on a live server?" → sqlcmd.
"Which event ID shows the Security log was cleared?" → 1102.

What to remember for the exam

Capture volatile database data (sessions, connections, plan cache) before files.
.mdf is the primary data file, .ndf secondary, .ldf the transaction log.
The recovery model decides how long log entries remain.
Event 4624 is a successful logon, 4625 a failed one, 1102 a cleared log.
Cloud and OT acquisition are done through provider tools or passive sources.

Watch out

Stopping the database service to copy files loses the plan cache and session data. Decide the order with the lead investigator before any restart.

Common mistakes

Copying .mdf without the log. The transaction log is needed to understand changes and to recover consistently.

Running heavy queries on the live server. They change state and can disturb the production system.

Confusing 4624 and 4625. Success versus failure is a frequent exam trap.

Check yourself

What does the transaction log contain?
A record of the changes made by transactions, which can show what changed and when.
Why collect the plan cache early?
It is held in memory, and a restart or memory pressure clears it.
Where are Windows event logs stored?
As .evtx files in C:\Windows\System32\winevt\Logs.

Topic 7 of 11 in Procedures and Methodology

Test yourself on Procedures and Methodology

The full EC-Council 312-49 question bank has 900 questions, with timed practice exams and an explanation for every answer.

Subscribe Try Sample Exam

This study guide is independent and is not affiliated with, endorsed by, or sponsored by EC-Council. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.