Introduction
If you are reading this article is because you read about the SQL Server transaction log and you want to go to the LSN to restore information. This article will explain what is the LSN and show some tables, views, and system functions used to retrieve LSN information. We will also learn how to restore information using the Transaction Log information. We will also find out if there is a Go LSN command. So, if you want to learn more about the transaction log and want to recover some information using the logs, you are in the right place.
What is the LSN?
Let’s talk about LSN first. LSN stands for Log Sequence Number. The transaction log stores the database activity (the transactions). For example, if we insert, delete or update data we will have a transaction registered in the Transaction log.
The Transaction log is a physical file which by default has the ldf extension. By viewing the properties of the database, you can check the location of your transaction log file in SSMS:
If you prefer to use T-SQL, the following sentences will be useful to get the path of the log file of the current database:
|
1 2 3 |
select * from sys.database_files where type_desc='LOG' |
How can I do a go LSN?
It is a common question if we can go to a specific LSN. Another frequent question is if there is a go LSN command.
Well, the answer is no, there is no go LSN command. However, there are functions to go to check the LSN. Let’s look at the Transaction Log File first. To do it, we will use an undocumented function named the fn_dblog. As the name says, it is a function to read the database transaction log.
- Note: If you want to read more about this function, we strongly recommend reading this article: Reading the transaction log in SQL Server – from hacks to solutions
Let’s look at the query:
|
1 |
Select * FROM sys.fn_dblog(NULL,NULL) |
The results displayed will show the Current LSN, Operation (insert, update, commit, checkpoint, begin time, end time, etc.), transaction ID and several different columns:
It is very important to know the LSN number when you are restoring data. The backup set is a system table that is stored in the system MSDB database and is used to get the information about the backup sets including information about the backup, password protection, recovery information, collation, the server used, compression and encryption information and more.
|
1 2 |
select * FROM msdb.dbo.backupset s |
It will also include important information related to the first LSN and last LSN. The first LSN contains the oldest LSN of the backup log record. The last_LSN will show the next LSN after the backup.
- Note: For more detailed information about the dbo.backupset, please refer to the following reference: backupset (Transact-SQL)
As you can see, there is no go LSN, but we have some functions to view it.
Restore data using a specific LSN
As we said before, there is no way to do a go LSN, but we can restore data using the LSN. The following example will show how to restore data in a specific LSN:
|
1 2 3 |
RESTORE LOG customer FROM DISK = 'c:\backup\customer.bak' WITH STOPATMARK = 'lsn:12000000050000037' GO |
The following command will restore the transaction log backup from a specified LSN. The WITH STOPATMARK allows writing the transaction log LSN.
There must be another way
If you want to restore your data, but you want to check an easier way to recover information using the log, but you think it is hard to read the log using system tables or system non-documented functions, there is another way to do it. In fact, you do not need to know anything about LSN to recover your data using the transaction log.
I found on the web a nice tool to read the log, recover data, export information Undo and Redo changes. You can detect who did the transaction and recover data using the log information.
The tool name is the ApexSQL Transaction Log Reading. For more information about this tool, please check the following link: Transaction log
Go to the LSN for the master database and other data files
The master database file is one of the main system databases containing important information about SQL Server including logons, linked server, server configuration information, system stored procedures, and more. We can use the sys.master_files to get the LSN of the database creation. Also, we can get the LSN of the differential backup. Backup_lsn shows the LSN of the last backup.
|
1 2 |
select * FROM sys.master_files |
A similar system view, is the sys.database_files that shows similar information about the data files.
For more information about the sys.master_files and the sys.database_files, refer to the following links:
Conclusion
In this article, we learned what the transaction log is and how it can be used to recover information using the transaction information. We learned how to go to a specified LSN. We also show different ways to read the transaction log and different ways to recover information from a selected LSN. We also, show some system views and functions used to retrieve LSN information in the database.
If you have questions, feel free to write your comments and we will gladly help you.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023