Database Forensics

Question 1

SQL Server Data Storage has three components

Answer 1

Primary Data Files (MDF)
Secondary Data Files (NDF)
Transactional LOG Data Files (LDF)

Question 2

Primary Data File (MDF)

Answer 2

The primary data file contains the startup information for the database and points to the other files in the database. User data and objects can be stored in this file or in secondary data files. Every database has one primary data file. The recommended file name extension for primary data files is .mdf

Question 3

Secondary Data Files (NDF)

Answer 3

Secondary data files are optional, are user-defined, and store user data. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive. The recommended file name extension for secondary data files is .ndf

Question 4

Transaction LOG Data Files (LDF)

Answer 4

The transaction log files hold the log information that is used to recover the database. There must be at least one log file for each database. The recommended file name extension for transaction logs is .ldf

Question 5

SQL Database File Locations

Answer 5

Database & logs files:
\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA*.MDF | *.LDF

Trace files:
\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\LOG_#.TRC

SQL Server error logs:
\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\ERRORLOG

Question 6

What command will help in collecting the active transaction log?

Answer 6

sp_helpdb

Question 7

What function allows you to retrieve the active portion of the transaction log file

Answer 7

The fn_dblog() function

Question 8

fn_dblog () function filters transactions by:

Answer 8

Target database object
Specific columns
SPID and/or date/time range

Question 9

What is the Database Consistancy Checker (DBCC)?

Answer 9

The DBCC LOG command allows you to view and retrieve the active transaction log files for a specific database. Following are the other DBCC commands that allow you to obtain additional information related to the specified database.
DBCC DBTABLE: Returns the structure of the selected database table
DBCC DBINFO: Returns information related to the database metadata
DBCC PROCBUF: Returns the contents of the SQL Server Procedure Buffer.

Question 10

DBCC BUFFER:

Answer 10

Returns the buffer headers and pages from SQL Server’s buffer cache, where SQL Server stores results.

Question 11

DBCC SHOWFILESTATS:

Answer 11

Returns information related to the space occupied by the data files in the active database.

Question 12

DBCC PAGE:

Answer 12

Returns the data page structure of the selected database

Question 13

To collect the database plan cache, the following query is used in SSMS:

Answer 13

select * from sys.dm_exec_cached_plans cross apply sys.dm_exec_sql_text(plan_handle)

Question 14

Issuing sys.dm_exec_cached_plans

Answer 14

returns a row for each query plan that the SQL server had cached to speed up query execution. This dynamic management view will help users to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

Question 15

To collect the trace files (.trc) navigate to:

Answer 15

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG

Question 16

To collect the SQL Server error logs navigate to:

Answer 16

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG

Question 17

Methodology for Database Forensics Using SQL Server Management Studio (SSMS)

Answer 17

1. Examine Windows Logs
2. Examine Error Logs
3. Examine Trace Files
4. Examine Active Transaction Logs
5. Examine Data Page
6. View the Object(s) that have been modified
7. Gather the Object Schema
8. View the Modified Record
9. Identify the Data Type
10. Compare the Row Logs

Question 18

What is MySQL?

Answer 18

MySQL is an open source relational database. Data stored in a MySQL database is duplicated and stored in multiple locations. Therefore, any users deleting data in the database either accidentally or intentionally will not completely delete the data. You can examine all the files containing a copy of the deleted data and recover it.

Question 19

By default, the data directory is located at

Answer 19

C:\ProgramData\MySQL\MySQL Server 5.n\ in Windows based machines.

Question 20

The InnoDB storage engine contains two types of logs:

Answer 20

Undo logs, help you to roll back the transactions

Redo logs, help you to re-execute the transactions (ib_logfile0 and ib_logfile1)

Question 21

Mysqldump

Answer 21

The utility allows you to dump a database or a collection of databases for backup purposes

Question 22

mysqlaccess

Answer 22

Checks the access privileges defined for host name, user name, etc.
Validates access using the user, db, and host tables

Question 23

myisamlog

Answer 23

Processes the contents of MyISAM log file and perform recovery operation, display version information, etc., depending on the situation

Question 24

myusamchk

Answer 24

Views the status of the MyISAM table or checks, repairs, or optimizes them.

Question 25

mysqlbinlog

Answer 25

Reads the binary log files directly and displays them in text format
Displays the content of bin logs (mysql-bin.nnnnnn) in text format

Question 26

mysqldbexport

Answer 26

Export metadata/data definitions