How to Retrieve Deleted Rows in SQL Server 2014, 2012, 2017 & other Edition
Explore this write up to solve how to get back deleted rows in SQL Server 2014, 2012, 2017 & its other editions. We can retrieve deleted rows data and know about how to retrieve deleted rows in SQL Server, if we have idea that when the data is deleted. We can do this task using Log Sequence Numbers (LSNs) process. According to Microsoft, “Each data record in the Server transaction log is ultimately recognized by a LSNs (log sequence number)”. We will utilize these LSNs to retrieve our deleted rows data.
How to Restore Deleted Rows in SQL Server Table Data
To restore / retrieve deleted rows database table, SQL database should be FULL Recovery / BULK-LOGGED Recover model.
SIMPLE database recovery doesn’t support transaction logs backup therefore, it is not easy SIMPLE Recovery model. Let’s set up environment for retrieving deleted rows in step by step process.
Step 1 –Generate a database name “RecoverDeletedData” & set it on recovery model as FULL if it is not as FULL. Then use the provided commanded to generate SQL database.
Step 2 –Now we will generate a database table for our environment.
Step 3 – After generating a database table “friends”, let’s add some value in it.
Step 4 – After adding records verify the added records with provided query –
Step 5 – Now we will get FULL backup of SQL database “RecoverDeletedData”
Step 6 – Now we will delete some rows and then we recover them using LSNs.
Step 7 – Then verify the “friends” again from provided query
Oh! Only 5 data record missed as we deleted the records from 6th rows to 10the rows.
Step 8 – Now you can get transaction log backup of the SQL database and use “RecoverDeletedData”
Step 9 – Now to retrieve deleted rows, you should gather information to deleted rows. To gather information about deleted rows, we can use provided query –
Using the above query, you will achieve Transaction ID of deleted rows, now you have to search at what time rows got deleted.
Step 10 – Now, you will find accurate time when rows got deleted with provided query by Transaction ID “000:000002f1”
After performing the above mentioned query, you will get the current LSNs “00000025:000001d0:0001”
Step 11 – Now you will go on to restore step to retrieve deleted rows with provided query
Step 12 – now we will use transaction log to restore deleted rows in SQL, using LSN “00000025:000001d0:0001”
Now you need to make sure that the LSN values should be in hexadecimal from and to restore with LSN value, you required it in decimal form. To convert it into decimal form just add 0x before LSN such as provided below in stopbeforemark clause
STOPBEFOREMARK = ‘lsn:0x00000025:000001d0:0001′
Step 13 – As shown in above screenshot, retrieve processing is successfully completed. Therefore, now you can check that our deleted records are back in RecoverDeletedData_Copy Database:
Using this process, you can retrieve deleted records for any SQL database, provided it is in FULL or BULK-LOGGED recovery model. Therefore, you can know about how recover deleted records in SQL Server 2014, 2012, 2017, etc. using the above mentioned steps but it has some limitation which you should know about them.
Limitation of LSNs Process
- Technical skills required for running the process.
- Any missed step will not recover the deleted rows data.
- Sometimes, it fails due to technical issues.
- If your deletion time of SQL database is over limit then it may fail.
How to Recover Deleted Records in SQL Server 2014 & Other
If you are normal user and don’t have technical knowledge or any any idea about this then you can also use the SQL Database Recovery Tool that is created for resolving your entire issues regarding how to retrieve deleted rows in SQL Server. It supports simple GUI and is capable to easily restore deleted rows in SQL Server database. It also provides free demo facility which can recover all deleted rows data and also provide a preview of all recovered SQL database then export 25 items to SQL Server without any cost or charges. Therefore, no need to worry and you can go with effective SQL Server recovery utility that can resolve all issues about how to recover deleted rows in SQL Server 2014, 2012 & 2017.
