Even if you use SqlBak to backup everything unauthorized data deletion from your database is one of the worst nightmares in terms of database administration. If you know when your data was deleted then the challenge to recover the deleted data is not as difficult. But, if you do not know the date and time when the data was deleted, then we will have to find a different way of identifying the data.
How to recover deleted data in SQL Server
Here are the steps we are going to use in recovering our data.
- For testing and example purposes we will create a database and a table schema.
USE [master]; GO CREATE DATABASE RecoverTestDatabase GO USE [RecoverTestDatabase]; GO CREATE TABLE [BlogArticles]( [Article_ID] INTEGER IDENTITY, [Artilce_Date] DATETIME DEFAULT GETDATE(), [Article_Creator] NVARCHAR(50) DEFAULT 'Admin');
- Populate the table with data using this script.
INSERT INTO BlogArticles DEFAULT VALUES; GO 30
- Continue by deleting some records from the table in order to recover them through the help of LSNs in the SQL Server Transaction Log.
DELETE FROM BlogArticles WHERE Article_ID < 15; GO SELECT * FROM BlogArticles;
You can see the results returned by the query above that the records are actually missing from the table.
- Now, let’s go and review the Transaction Log of SQL Server to get the deleted records. Run the next query in order to get basic information about the deleted records.
SELECT [Current LSN], [Transaction ID], [Operation], [Context], [AllocUnitName] FROM fn_dbLog(NULL, NULL) WHERE [Operation] = 'LOP_DELETE_ROWS'
- If you’ve gone along using all of the queries in this example, then after running the query above you should have the following result set returned.
- The Transaction ID column will show us what DELETE statement was run, but in case you didn’t notice the AllocUnitName column stores the table from which the transactions have been deleted. You can additionally filter the results to display only those of this table with the query below.
SELECT [Current LSN], [Transaction ID], [Operation], [Context], [AllocUnitName] FROM fn_dblog(NULL, NULL) WHERE [Operation] = 'LOP_DELETE_ROWS' AND [AllocUnitName] = 'dbo.BlogArticles'
- Other columns give us information about the table type, in this case, it’s a HEAP type of table and the transaction that the DELETE statement was performed through transaction 0000:0000022c, and as we can see there are multiple records with the same transaction ID.
- This type of information is quite important as it shows us that all of the records from the BlogArticles table were deleted with one transaction, which doesn’t necessarily mean one query.
- In order to prove this run the next batch of queries
BEGIN TRANSACTION DELETE FROM BlogArticles WHERE Article_ID % 2 = 0 DELETE FROM BlogArticles WHERE Article_ID % 3 = 0 COMMIT
- If now if we are to go and run the query that displays data from fn_dblog, we will see that both queries have run in the same transaction, thus proving that this is not sufficient evidence to identify the number of queries.
- Now, we are going to take the transaction ID from the queries above. We will continue with the transaction ID returned by our second query, the one in the image above highlighted with blue, 0000:00000248.
Please be aware that from this point on you will have to use the LSN you have in your result set. We will continue this example with the LSN we were provided with by the query.
- Using this transaction ID we will use it in the next statement to retrieve the LSN (Log Sequence Number) of the LOP_BEGIN_XACT operation.
- This means we will need the exact LSN when the DELETE query began its transaction.
SELECT [Current LSN], [Operation], [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID] FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] = '0000:00000248' AND [Operation] = 'LOP_BEGIN_XACT'
- The above query should give you similar output to the one in the image below.
- Now we know the time the DELETE statement was executed, what is its LSN and what transaction it was executed under.
- The next step involves changing the hexadecimal value of the LSN number into a decimal so we will be able to retrieve our data.
- As you might see, our LSN number has 3 sections, each separated by a colon. We will use a query on each section of the LSN in order to get its decimal value.
- Run the below query once for each part of the LSN and specify that part as a parameter to get the decimal number. In our LSN’s case the queries that we need to run are:
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x00000014', 1)) SELECT CONVERT(INT, CONVERT(VARBINARY, '0x000000ee', 1)) SELECT CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1))
- Now that we have the decimal values of the sections of the LSN we will be using the STOPBEFOREMARK operation in order to recover our data.
- We will use the decimal returned by the first query as it is, in this case, it’s 20.
- But the next decimals have to be preceded by leading zeros. The second decimal has to be preceded by zeros until it has a length of 10 characters, while the third should also be preceded by zeros until it has a length of 5.
- Thus, the outputs that you are going to need in use are returned by the following queries
-- Outputs the decimal for the second section SELECT RIGHT('0000000000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x000000ee', 1)) AS NVARCHAR), 10) -- Outputs the decimal for the third section SELECT RIGHT('00000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1)) AS NVARCHAR), 5)
- Now that we have the three decimal parts we can put the numbers together in order to get the LSN number. In our case, the LSN is 20000000023800001.
- The next step is to run a Transaction Log Backup on the database from where data was deleted if there was no previous transaction log backup done since the data was removed.
- Now, we will do a restore of this database at a different location or on the same server with a different name up until the point of the LSN number we calculated above.
- After that, we will import the missing data from the newly restored database to the main database.
-- Starting first with restoring the FULL BACKUP with NORECOVERY RESTORE DATABSE RecoverTestDatabase_COPY FROM DISK = 'C:\RecoverTestDatabase_FULL.bak' WITH MOVE 'RecoverTestDatabase' TO 'C:\RecoverTestDatabase.mdf' ,MOVE 'RecoverTestDatabase_log' TO 'C:\RecoverTestDatabase_log.mdf' ,REPLACE ,NORECOVERY; GO -- Restore LOG backup with STOBEFOREMARK option to recover exact LSN RESTORE LOG RecoverTestDatabase_COPY FROM DISK = 'C:\RecoverTestDatabase_log.trn' WITH STOPBEFOREMARK = 'lsn:20000000023800001'
- At this point your restored database is ready and you can check if the deleted data is recovered or not. Run the below SELECT to see the first rows which were deleted.
USE RecoverTestDatabase_COPY SELECT * FROM BlogArticles
- You can see in the above screenshot that all rows were recovered. Now, this data can be used. You can move the data from this database restore to your main database through SSIS‘s Import and Export feature.
25 thoughts on “Recover deleted data in SQL Server”
Hey as per your instruction i followed all of your instructions at the last i am getting following error:
Processed 24528 pages for database ‘SalesDBOriginal_new’, file ‘SalesDBData’ on file 1.
Processed 3 pages for database ‘SalesDBOriginal_new’, file ‘SalesDBLog’ on file 1.
RESTORE DATABASE successfully processed 24531 pages in 9.358 seconds (20.479 MB/sec).
Msg 4335, Level 16, State 2, Line 3
The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point.
Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
plz tell me what should i do now
Can you give a few details about the time you are trying to restore to? And what backups are you using and if you know the times/dates when they were created?
This is very helpful blog but I have a question. Can I do all these actions using Differential backup.
You are constrained by using the STOPAT command, which generally is used only when restoring logs, but it is not necessarily used only for log backups/restores.
If you are restoring a FULL or DIFFERENTIAL backup, then it is possible that these backups contain a part of the log which also has the changes you want to revert to.
Thus, when you do a restore of these backups, the records that were captured in the log can be applied and in this way you can bring back the database to a consistent state and possibly have the records you want.
In case there are no log records captured and the transactions were occuring while the FULL backup was initially being done, then the database would be inconsistent.
An example situation would be backing up a FULL database, process which would take 2 hours to complete (from 3 AM to 5 AM). You can in this situation restore the backup file and also use STOPAT 4 AM so you would capture just the completed transactions that occurred up until half point.
I hope my answer is clear and it also offered a more detailed view about the process.
If you have any other questions please post and I will try to answer as soon as possible.
hey can you tell me exact way to take transaction log bak up
Please follow the steps in this article: https://blog2.sqlbak.com/backup-the-tail-of-the-transaction-log/
Hi there, I enjoy reading all of your article post.
I like to write a little comment to support you.
My weblog … seo
I was not able to execute the below query –
RESTORE DATABASE pund330_copy
FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL10.EVAL1\MSSQL\Backup\pund330.bak’
MOVE ‘pund330’ TO ‘C:\pund330.mdf’,
MOVE ‘pund330_log’ TO ‘C:\pund330_log.ldf’,
It gave me error – Logical file ‘pund330’ is not part of database ‘pund330_copy’. Use RESTORE FILELISTONLY to list the logical file names.
am facing the same issue, have you got any sollutions?
same problem here….
I followed everything as you said.I was able to restore my previous database to new one.But after executing restore query,database is being in restoring status.And it is taking long time to complete restore operation even though i have only 5 records in my previous table.
Hi and thank you for browsing our blog.
Please take a look at this article on our blog which shows you what to do in the situation where your database is stuck in RESTORING state.
If you still encounter issues, please post a reply to this message.
Amazing! Its really remarkable piece of writing, I have got much clear idea
on the topic of from this article.
Thank you Alvin! You’re invited to browse other articles as well.
Thanks for that, you saved me hours of time trying to figure out where exactly the problem happened!
You are welcome! I hope you find other posts here helpful.
Here is the perfect solution.
Thank you for posting this alternative. As I can see your solution helps you identify the user who removed the records from the database, while this post you commented on will help you recover the missing records and restore the database to a state which you want.
Thank you once again for posting a comment and also please feel free to browse through other articles on this blog.
i must say this is a helpful post for the database lovers. I saw you finished this article with SSIS import export feature. I have written tutorial on Import Export feature. This will be helpful for the readers too.
You can visit – http://www.phpring.com/import-export-wizard-in-ssis-part1
A Ring of People Helping People
Did you take an initial full backup of the database after the data was created?
Please mention, when should I take a full backup and when should I take a log backup to get the perfect results like you from the above task.
Thanks in advance.
Hi Srikar, first you should take the full backup and then log backups.
So, after taking the full and log backups I should go through the process mentioned in this blog post right?
thanks for the response.
I recommend that you try this process on a test database first, in order to make sure that the process works correctly and you will be able to recover the data.
ok. Thank You.