MS SQL Server: How to Perform Transaction log recovery
Data on MS SQL server can be easily recovered using the transaction log. There are some circumstances when you can use the transaction logs. So, in this article, you’ll get to know the method to recover the data, situations when it can be recovered using the log and what benefits this method offers.
Circumstances in favor of process
Circumstances in favor of Transaction Log Recovery in Microsoft SQL Server
If your SQL Server database is using the full recovery model then only you can recover data using the transaction logs. There are a lot of methods available to look for the used recovery model of a database. SQL Server Management itself can be used to check the SQL Server database recovery model. All you need to do is to go to the SQL Server management studio and right-click on the database, click on the properties and choose options on the property window. You will find the recovery model in the drop-down menu located in the upper part.
Recovery process to Recover Log
How to Recover Data from Microsoft SQL Server Transaction Logs
To perform the Transaction Log Recovery in Microsoft SQL Server, you need a backup of the transaction log and a backup of the database as well. The SQL database backup file will have the bak extension and the backup file of the Transaction logs will have .trn as the extension. You can identify them by looking at their extension.
You have to perform the full database backup restore and then only you can restore the transaction log backup to get back the MSSQL data back.
Transaction Log backup strategies
Perform Full Database Backup Restore:
You can do this step by using the RESTORE DATABASE is a SQL server command that can be used to restore the data back using the SQL Server database backup. If your SQL database has recovered using the NORECOVERY option then the restoration of the databases can also be performed using the SQL Server Management Studio functionality.
Perform Restoration of the Transaction Log Backups:
After restoring the full database backup, Transaction Logs backup can be restored. It is possible to have a backup chain if there are multiple transaction log backup. The chain will get restored in order of their creation. RESTORE LOG command can be used to restore a transaction log backup.
You can go through the illustrated example of a database restore using a SQL Server database backup and multiple transaction logs:
DECLARE @dbName varchar(50)
SET @dbName = 'Test'
RESTORE DATABASE @dbName
FROM DISK = 'C:\Temp\test.bak'
WITH NORECOVERY
RESTORE LOG @dbName
FROM DISK = 'C:\Temp\testLog1.trn'
WITH NORECOVERY
RESTORE LOG @dbName
FROM DISK = 'C:\Temp\testLog2.trn'
WITH NORECOVERY
................................................
RESTORE LOG @dbName
FROM DISK = 'C:\Temp\testLogn.trn'
WITH RECOVERY, STOPAT = '2010-09-18 12:00:00 PM'
test.bak is the full database backup and there are no transaction log backups present. Keep in mind that all the restore operations are created using the NORECOVERY option except the last one that is created using the Recovery option. One more thing to remember is that restoration can be done until a certain point in time. Apply STOPAT to the last log backup in the chain to get this done. This will help you to restore the database if it’s corrupted at any step or operation.
Note:
To get good performance as well as reliable backups, you can use full and transaction log backups because it is possible that full backup was last performed one day ago and the transaction log was backed up an hour ago. Besides using the above mentioned recovery process, you can also prefer using SQL Log Transaction recovery solution with embedded benefits that fully scans the SQL log files and repair and recover data of LDF file.
Difference between LDF & TRN File
To manage all the transactions, a log file is used by the Microsoft SQL Server. It has the .ldf extension at the end. And when we try to back up the transaction log file, the backup file holds the .trn file extension.
To perform the Transaction log recovery, you do need to convert the df file to trn file. You can’t perform this conversion outside a database. So to perform the conversion all you need to do is to attach the ldf file to a database including the structure of the real database and then perform a transaction log backup. This will generate a trn file that can be used for the restoration.
Truncation of the Transaction log
Since exchanges of databases performed simultaneously, the transaction log can develop uncertainty. So, to prevent that, truncate operations are performed on the transaction log. A truncate will erase all the submitted transactions from the log.
As a matter of fact, a truncate will happen on any transaction log backup activity. Along with these lines, two consecutive transaction log backups won't contain similar activities. If one has performed many transaction logs with truncate since the last full backup and losses some of the transaction log, then he is not able to perform the restoration.
Log sequence number and backup sd
What are the Log sequence number and backup start date
A log sequence number is a unique number that each transaction from the transaction log has. It is also called as Shorthanded LSN. This number also tells about the order of the transactions performed over the database. The elements in the chain need to be successive whenever you are trying to perform the restore operation. Every transaction log backup file has a FirstLSN and LastLSN that can be known by the following command:
RESTORE HEADERONLY FROM DISK = 'C:\Temp\testLog1.trn'
This command also shows the time of the backup taken. If the LastLSN in the first transaction log backup file is less than the FirstLSN in the third backup then the restoration of the third backup will fail. RESTORE HEADERONLY command can be used when the order of backups taken is unknown and if the backups are subsequent or missing in the chain.