How To Recover Deleted Data In Azure SQL Database

This article demonstrates how to recover accidentally deleted records from Azure SQL Database.

The recovery approach discussed here will be helpful when,

1. Due to incorrect where clause conditions, a good amount of the data deleted from table.
2. Executed DELETE statement without putting where clause (mistakenly done by user), all the data deleted from table.
3. Huge amount of data UPDATED mistakenly.

For demonstration purpose, we created an Azure SQL Server and database from Azure. Also, I created a table and inserted some data into that table. Then, we will delete those data using DELETE statement. We will use Point in Time restore to recover deleted data.

Let’s see a detailed step-by-step approach for better understanding…

STEP 1 - DELETE data from Azure SQL DB without using WHERE clause.

Here, we have CustomerInfo table with some data. And we executed delete statement mistakenly. This is important to know the exact time when data is deleted from table or approx. time is also fine.

STEP 2 - Perform Point in Time restore in Azure SQL Database

Now go to Azure SQL DB and click on restore.

It’s basically created a new database and snapshot of data will be as on restore point. Remember, restore point is a UTC date time. If you are not sure about UTC time when your data is being deleted, just convert your local time to UTC time.

Here, I’m keeping all default configurations and only changing the Restore point date and time.

NOTE: Generally, deployment of restored DB would take time depending upon your existing db load. No worries, be patient with it.

STEP 3 - Check deleted data in restored database

Once restored database is created, we can directly go to SQL server and check the new database. Here, we can see deleted data. Now we can easily copy these data into excel and write and insert statement to make entry into the original DB.

STEP 4 (OPTIONAL) – Make an INSERT into original DB

While inserting data into original db, be cautious with primary key if it is an IDENTITY column. If you insert new records, Id column will have different values. To overcome this, we need to SET IDENTITY_INSERT <Table_Name> ON, Insert the data with Identity column and then SET IDENTITY_INSERT <Table_Name> OFF.

We need to explicitly specify the column name while inserting the data, otherwise we will get below error.

An explicit value for the identity column in table <table_name> can only be specified when a column list is used and IDENTITY_INSERT is ON.

IMPORTANT: Based on your scenario, you can deal with recovered data. It does not always be insert, there could be possibility of UPDATE in case you mistakenly update some data without using where clause. That’s why step 4 is mentioned as optional.

STEP 5 - Delete the Restored database from SSMS

Additional cost associated with new DB, it is recommended to clean-up/delete your restored DB from SSMS. It will automatically delete from Azure.

Again, the approach discussed in this article is a generic one or a glance at how to handle such a scenario and it may not be suitable for all the use cases.

Hope this article helps you. Happy Learning!