MS® SQL Server is the most important need in a relational database management system. It systematically handles a huge database.
However, Digital Crimes Can Happen In SQL Server As Well!
These days, hackers with evil intentions modify the values of databases to damage the reputation of an organization. In such a situation, it becomes difficult to make out who is the culprit. This brings big trouble in front of organization runners.
Wait a Minute! Here is something good for you!
The SQL Server comprises the transaction Log file that maintains the records of all transactions as well as modifications in the database implemented on a database in MS SQL Server. By reading the log file, it becomes easy to make out who deleted data from the table in the SQL Server database. In addition to this, forensic investigators use it to examine the SQL Server Transaction Log to view & check the log detail comprehensively. In brief, the SQL Log file makes it easy to find out which query performed when and at what time.
Here, we will discuss how to view the log file of SQL Server via several workarounds. Go through this article and understand the way to open and read transaction log files in MS SQL Server 2017 / 2016 / 2014 / 2012 / 2008 / 2008 R2 / 2005.
How to View Transaction Log File of SQL Server
This section will help you to understand how to open, check and read transaction file to recover info about the data which had been changed. Let’s start!
First Approach: Utilize Log File Viewer in SQL Server Management Studio
This method works when it is to open and view the information about the given below logs in SSMS:
- Audit Collection
- Database Mail
- SQL Server Agent
- Windows Events
- Job History
- Data Collection
- SQL Server
The main motive of Log File Viewer is to provide a report about the activities that taken place in SQL Server Management Studio. One can easily open Log File Viewer wizard in several ways based on the information which you want to check. Check the instructions to view log details in SQL Server.
Steps to View Log File of SQL Server Via Log File Viewer
- Open SQL Server Management Studio application. Here, SQL Server 2014 environment is used to read SQL Server Error Log.
2. Connect to Server windows pops up and choose the Server Name and Type of Authentication. Click on Connect.
3. In the Object Explorer, go to Management to examine or read the log file of SQL Server 2014.
4. Move to SQL Server Logs option.
5. Right-click on SQL Server Logs and choose View << SQL Server Log sequentially.
6. You will find the Log summary on Log File Viewer window. Choose other logs such as SQL Server Agent, Database Mail from the left panel to get the view of the information.
Second Approach: View Log File of SQL Server Via. Undocumented fn_dblog()
The function fn_dblog() is used to data from the Transaction file of SQL Server for fulfilling the forensic purposes to analyze the log event performed on the table. Let us know how to read transaction log file in MS SQL Server 2017 / 2016 / 2014 / 2012 / 2008 / 2008 R2 / 2005 versions.
Step-by-Step Process to View Log File in SQL Server Using Fn_dblog()
Here is a table named ‘Employee’. Let us view the values of the table using the following T-SQL.
- In the next step, change the table data with the help of the update command. In this regard, execute the query;
Update employee set department =‘IT’ where emp_name = ‘jeevan’
- Take a glimpse of the table values using the Select Query, check the modified table.
4. Run the fn_dblog function as per the need. Here, the query is implemented to check the time when the update operation was implemented
Select [Begin Time], [Transaction Name] from fn_dblog(null , null) where [Transaction Name] = ‘Update’
- If you want to analyze all the logs like Delete etc. run the following T-SQL query.
Select [Begin Time], [Transaction Name] from fn_dblog(null, null)
Some of the consequences are related to the fn_dblog(). This function will provide the time of the query when it was committed rather than which data entry is affected. Due to this, it becomes awkward to find out which table data is changed.