In this piece, we'll go over LSN, the Retrieved function fn_dblog(), and the Recovery Models that go with it in further depth.

Log of SQL Server Transactions
Every SQL Server database has a transaction log that records all transactions as well as the database changes made by each transaction. The transaction log is a crucial component of the database, and if there is a system failure, the transaction log may be necessary to restore consistency to your database.

The SQL Server Transaction Log is useful for recovering deleted or updated data if you run a DELETE or UPDATE operation with the incorrect condition or without filters. This is accomplished by listening to the records included within the SQL Server Transaction Log file. DML operations such as INSERT, UPDATE, and DELETE statements, as well as DDL operations such as CREATE and DROP statements, are among the events that are written to the SQL Server Transaction Log file without any additional configuration from the database administrator.

LSN Function Retrieving from Transaction Log
fn_dblog() is a function.

The fn_dblog() function is one of the SQL Server undocumented functions; it allows you to see transaction log records in the active section of the transaction log file.
fn_dblog() Arguments

The fn_dblog() function takes two arguments.

• The first is the initial log sequence number, abbreviated as LSN. You can alternatively use NULL to return everything from the beginning of the log.
• The second is the LSN at the conclusion. You can alternatively specify NULL, which indicates that you wish to return everything to the log's conclusion.

fn_db_log()
SELECT * FROM fn_dblog (
              NULL, -- Start LSN nvarchar(25)
              NULL  -- End LSN nvarchar(25)
       )

We will not discuss the details of the function but list the possibly useful columns below:

Operation Clumn in fn_dblog()
The ‘Operation’ column indicates the type of operation that has been performed and logged in the transaction log file.

Such as test.

LOP_INSERT_ROWS

LOP_MODIFY_ROW

LOP_DELETE_ROWS

LOP_BEGIN_XACT

Recovery Models
SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available.

Three recovery models exist:
    Simple --- Operations that require transaction log backups are not supported by the simple recovery model.
    Full, and
    Bulk-logged.

Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time. By default, when a new database is created within Microsoft SQL, the recovery model option is set to full.

Recovery Option
Restoring the db with the RECOVERY option is the default option for users with FULL backup. However, if you have different types of backups (differential, transactional, etc.), you may need to use the NORECOVERY option to combine the backups.

Even Recover database without Restore
RESTORE DATABASE *database_name* WITH RECOVERY

HostForLIFEASP.NET SQL Server 2022 Hosting