Summary:- A transaction log is an integral part of every SQL Server database. It includes log records produced during the logging process in a SQL Server database. But in the same process, there should be chances of Log files corruption too. This article will describe the best possible methods to retrieve data from log files in SQL Server. You can also use the Aryson SQL Log Analyzer to retrieve the SQL Database Transaction Logs File in the SQL server.
What is the Transaction Log file in SQL Server?
The transaction log file in every SQL Server database can store all the transaction records and the database modifications performed within the specific transaction. It is an essential component of a SQL Server database when we talk about disaster recovery. If you perform the DELETE or UPDATE operation within the inappropriate or without filtration conditions, the SQL Server Transaction Log files can retrieve it. Mainly, the Log files accomplished the records inside this black box, formerly known as SQL Server Transaction Log file.
After each data modification- a log record will be self updated to the transaction log in a proper sequence. However, if there is a system failure, there should be the chances of data loss, but the Log can bring your database back to a consistent state.
Why Need to Retrieve Data From Log Files in SQL Server?
Transaction log files in SQL Server database files become corrupt or due to several reasons, including the unclean shutdown of the database, hardware fault, large-sized LDF file, and virus attack. Further, a corrupt log file can also make the database inaccessible and need to view SQL Server transaction log because too large a database size may get corrupted. Let us check the working steps to overcome the SQL Server logs all queries and retrieve Data From Log Files in SQL Server.
How to Recover Data from SQL Server Transaction Log File?
To perform the workaround for recovering data from the SQL Server Transaction Log file, we will have to generate another database with a specific table. To-do-so, use the script below:
| CREATE DATABASE RecoverFromTransactionLogGOUSE RecoverFromTransactionLogGOCREATE TABLE [dbo].[Employee_Main]( [Emp_ID] [int] IDENTITY(1,1) NOT NULL, [EMP_FirsrName] [varchar](50) NULL, [EMP_LastName] [varchar](50) NULL, [EMP_BirthDate] [datetime] NULL, [EMP_PhoneNumber] [varchar](50) NULL, [EMP_Address] [varchar](max) NULL,PRIMARY KEY CLUSTERED ( [Emp_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO |
Once you create a database and the table, you will have to enter the table with 30 records by using the INSERT INTO T-SQL(table name):
| USE [RecoverFromTransactionLog]GOINSERT INTO [dbo].[Employee_Main] VALUES (‘AAA’,’BBB’,’1980-10-15′,’98542224′,’ABCDE’)GO 10INSERT INTO [dbo].[Employee_Main] VALUES (‘CCC’,’DDD’,’1985-08-07′,’98543334′,’FGHIJ’)GO 10INSERT INTO [dbo].[Employee_Main] VALUES (‘EEE’,’FFF’,’1989-04-06′,’98544454′,’KLMNO’)GO 10 |
If we have not taken the backup of the database yet, we have to save the tables data before starting the recovery procedure.
Restore Multiple Transaction Logs Using Built-in Functions
1. fn_dblog
You can easily read the content of the SQL Server Transaction Log by using several built-in functions. The first function that we will use to retrieve the lost data is the fn_dblog. It is a type function that is undocumented system table-valued and used to make the SQL Server Transaction Log and to view the content of the active portion of that file.
To use the fn_dblog() function, you need to provide two parameters, the start and the end LSN of the log. To view the active portion of the SQL Transaction Log file, you can also pass NULL values from these two parameters. The outcome comes from the fn_dblog() function consisting of 129 columns. Thus, it is better to select only the columns that provide data and show only the operation you plan to recover.
For example:-
- The LOP_INSERT_ROWS operation is logged when a new record is present.
- LOP_DELETE_ROWS operation is logged when an existing record is deleted.
- LOP_MODIFY_ROW operation is logged when an existing record is updated.
Perform the below script to check the update statements self presented on the database:
| SELECT Operation, [rowlog contents 0], [rowlog contents 1], [rowlog contents 2], [rowlog contents 3]FROM sys.fn_dblog(NULL,NULL) WHERE operation = (‘LOP_MODIFY_ROW’) ORDER BY Operation ASC |
Finally, the result in our example will be such as:
2. fn_dump_dblog()
Another inbuilt function is fn_dump_dblog(), and in comparison to fn_dblog(), it can read the SQL Server Transaction Log backup and read the online Transaction Log file. In addition, to use the fn_dump_dblog() function, you have to provide 68 mandatory parameters, as displayed below:
3. DBCC Page Command
SQL Server includes several numbers of DBCC commands which one can use to execute the different types of SQL Server administration tasks. One of the specific undocumented commands is the DBCC Page that you can use to read the content of the database data and log online files. You only need to enter the database name and the ID of the Transaction Log file to view the data of the database log files. You can easily retrieve the ID of the Transaction Log file by performing the below-mentioned query:
Finally, you will view the Transaction Log file after turning on the Trace Flag 3604. To print the output of the DBCC command, as displayed below:
| DBCC TRACEON (3604, -1)GODBCC PAGE (RecoverFromTransactionLog, 2, 0, 2)GO |
The final result of the DBCC command will be displayed in a hexadecimal format which requires a significant process to turn it out on the meaningful shape for further use. Use the correct data type, as shown below:
Drawbacks of Manual Methods to Retrieve Data From Log Files in SQL Server
- The process should not be reliable to perform.
- It requires some advanced technical knowledge.
- There is a high risk of data loss.
An Expert Solution to Retrieve Data From Log Files in SQL Server
Aryson SQL Log Analyzer is a powerful utility to repair the LDF files and extract the transaction logs. The software is efficient enough to fix the corrupt transaction log of the SQL Database and allows users to view SQL Server transaction log. You can use the free version of the software to evaluate the performance and working of the software.
Steps of SQL Server Transaction Log Reader Tool Free
- Open the Aryson Log Analyzer on your system and browse the LDF and MDF files.
- Now, preview all the log file data and choose the folders from which you want to recover. Click on the Save button.
- Choose the saving option either save in SQL server or save as SQL script. Note:- If you select save in SQL server database, then follow the below instructions.
- In the SQL Server database, select either Windows Authentication or SQL Server Authentication.
- If you will select the SQL Server Authentication, enter the user name, password, server name, database name, and click on the Test Database Connection.
- If you need to save the records in a SQL script, then choose the Save as SQL script and click on the Browse button to select the saving location.
- Now, opt for the save Table constraints and operation to be saved.
- Date filter to save operation in a specific date range.
- After completion of the saving process, click Ok.
Conclusion
Even though the manual method can retrieve data from log files in SQL Server, but the fact, it is not a perfect option for SQL users due to its inaccuracy and complexity. Further, you can also use an expert solution to fix SQL Server logs all queries.
