Tuesday, August 8, 2017

Learn How to Recover Deleted Records from SQL Server 2014


Sometimes users may have numerous of databases in their SQL Server. However, while managing their database sometimes by mistake their records are deleted due to which they face a lot of issues. To maintain the workflow they need to recover deleted records from SQL Server 2014. This could be possible only with successful SQL LOG Recovery

How to Get Back Deleted Records in SQL Server: Solution

Mainly Transaction log is responsible for all database-logging activities. Similarly, in this discussion, we will utilize the usage of similar undocumented function "fn_dblog" to get any unauthorized deletion of data as well as to retrieve deleted data in SQL server.

If you are aware that when your data is removed then, you can easily solve your problem on how to recover deleted data in SQL server database. However, if you do not know the data as well as the time when the data is erased then, it is important to understand first who has deleted the data. In this segment, we will first find the particular LSN under which deleted statement is run after that we will rollback deleted records in SQL until that LSN.

Each record in MS SQL Server transaction log is distinctively recognized by LSN (Log Sequence Number). LSN of log record at which particular event happened can be beneficial for making precise restore sequences and to retrieve deleted data. LSNs are utilized internally at the time of RESTORE sequence to path the point in time at which the data has been reinstated. When data backup is restored, data is reinstated to LSN corresponding to point in time at which backup was taken. Differential, as well as log backups, advance restored the database to later time that corresponds to higher LSN.

Important: Do not do any modifications in production without testing it properly in lower-life cycle environments

Recover Deleted Records from SQL Server with LSN

Follow these steps to recover deleted records in SQL Server with successful SQL Log Recovery:

Step 1

Before moving further, we will make the database as well as the table on which one will run DELETE statement. Then, we will get the data deletion and then rollback deleted records in SQL. Implement the T-SQL code given below to generate database and table and retrieve deleted data.

Step 2

We have made a database with table 'Location' of three columns. Now, we will put few rows in table.

Step 3

We will go further to erase some rows and recover deleted records from SQL Server table by using LSNs from the SQL Server Transaction Log.

Step 4


Now, find the SQL Server Transaction Log and retrieve deleted records in SQL. Run the mentioned code below to get data information about all removed transactions with SQL Log recovery.

You can see the transaction IDs under which DELETE statement was executed. As we know that data is removed from Location table, we will focus on only those transaction IDs under which data is erased from location table to retrieve deleted data. We can get table name from "AllocUnitName" column. It states DELETE statement has been executed on HEAP table 'dbo.Location' under transaction ID 0000:0000055e. There is only one transaction ID under which we can view deleted records SQL Server, which means this action has been implemented in a single batch. Now, capture transaction ID from next command.

Step 5


We initiate that transaction ID from above script that we will utilize in below statement to find the LSN of LOP_BEGIN_XACT operation and recover deleted records from SQL Server. It means that we want an exact LSN when DELETE statement started its transaction to retrieve deleted data.

Step 6


We will utilize STOPBEFOREMARK operation to get back deleted records in SQL Server. We cannot utilize this hexadecimal value to implement the operation of STOPBEFOREMARK. Therefore, we want alteration this value into decimal format. Then put all these together as single number.

Step 7

Now implement transaction log backup on database where data was removed if transaction log backup has not competed since data deletion. Then, we will restore the database to other location or on a similar server with the variant name until above LSN. Then, we will import and recover deleted data in SQL Server from newly reinstated database to production database.

Step 8


Now, the database is restored, One can check whether you could recover and find deleted records SQL Server or not. Execute the below SELECT statement to preview the first ten rows of location table that was deleted and then retrieve deleted data.

Now you know how to retrieve deleted records in SQL Server with SQL Log Recovery. You can utilize this erased data as per your need. If you need to export this data to live database you can utilize the SQL Server Integration Services, i.e. import and export wizard.

SQL Log Recovery become Hard

Above steps can be useful in most of the cases. But several situation may occur when it becomes difficult to restore deleted data with SQL Log recovery. Lets take a look on these situation:

  • Changes made after the DELETE, TRUNCATE or DROP statments
  • Unable to take the SQL Server offline
  • New Transactions performed after the data was lost
  • No, recent Full Database backup available.

In such situation user can go with third party SQL Log Recovery to successfully restore deleted data records in SQL Server.


After understanding, the users problem how to recover deleted records from SQL Server 2014. In the above discussion, we have discussed the ways to retrieve deleted data from the SQL Server database by Successful SQL LOG Recovery that makes easy for users in restoring SQL Server deleted records in SQL version 2016 / 2014/ 2012 / 2008r2 / 2005 / 2002 and all below version.

Thursday, July 27, 2017

Two Ways to Restore Database from MDF File in SQL Server


In todays decade Relational database Management system became the necessity of the organizations whether they are small level or large level. In such context SQL Server is the best and successful RDBMS which provides highly integrated services to manipulate the SQL database over the Server. As we talk about the SQL Server database the Server provides the primary storage call MDF File, NDF file also known as secondary file, LDF log transaction file. Well, in this section we will discuss about how to restore LDF and MDF files to a database so lets make some prerequisites perform tasks without any trouble.

  1. First of all detach the database. If you are trying to attach the database which is not detach you will get an error
  2. Maintain MDF File and LDF file in a specific folder before going to attach
  3. Locate MDF File and LDF File in the same location

There are two ways to restore database from MDF file in SQL Server:

  1. With the help of SQL Server Management Studio
  2. Using T-SQL

1. Through SQL Server Management Studio follow some steps:

  • Open SSMS and go to object explorer of the database engine instance
  • Then make a right click on the database and select Attach option of the list button
  • Windows appers, then click on Add button
  • Select the .mdf file and click on OK Button
  • Validate the selected files and go to OK Button

Now check the attached database

2. On using T-SQL

  1. Login to SQL Server database engine
  2. Go to New Query Window and use Create database with Attach close
    ON (FILENAME = 'C:\SQLServer\MyDatabase_Data.mdf'), 
    (FILENAME = 'C:\SQLServer\ MyDatabase _Log.ldf') 


In the above section, we learned how to restore LDF and MDF files to a database. It is highly recommended that you have to detach the MDF file before attaching a new one. This is a very helpful method to restore database from MDF file in SQL Server. The second method performs same task using T- SQL, for the users who needs to attach MDF file through SQL script. In case, if the transaction log file is missing then you can still attach MDF file in SQL Server without LDF file and if the MDF file is not accessible or corrupted then you can repair MDF file using third party SQL Recovery tools.