Update Without Where Clause

Update Without Where Clause

Update Without Where Clause

Running Update Without Where Clause might causes a data damage when it is with the critical table that creates a lot of problems and escalations. Recently while working with one of the customer we had a situation where a DBA executed the release script on production and the script contains an update statement without where clause. He immediately realized that he executed an update statement without where clause and he reported us explaining the situation. There were few things we found:

  • The change request implemented on a premium database
  • Update statement provided from Pre-Prod team which is not having any control on that:
    No Where clause
    No Transaction control – Commit / Rollback
    No Validation on update – Validating and Reporting
  • As per the business requirement single record should be updated but in real it updates all records in that table.
  • Usually when a DBA implements a CR on prod server a backup or snapshot has to be taken prior to implementing the change. Since it was a small database (6 GB) he did take a full backup before implementing the CR.

Solution we have provided:

Let’s say db name is: EcomPro & Table Name “Customers”
Update Statement ran: UPDATE Customers SET Last_Name = ‘Role’

 Made database “EcomPro” access to single user mode
 Identified the transaction ID which was wrongly updated the table without where clause
 Identified the transaction begin time (2016-21-07 16:30:15)
 Performed a latest database transaction log backup on “EcomPro”
 Created a test database and named it as “Test”
 Restored “EcomPro” full backup to “Test” WITH NO RECOVERY
 Restored all available transaction log backups except the last WITH NO RECOVERY
 Restored “EcomPro” latest transaction log backup with point in time recovery (We have given the time just a second before the transaction begin time which is identified in step 3 i.e. 2016-21-07 16:30:14) and restore it WITH RECOVERY.
 We could be able to recover the Customer table in “Test” database. Now we could see the correct data for the column name “Last_Name” for all customers.
 We have written a inter database query between “EcomPro” and “Test” to update the customers table on “EcomPro” database.
 UPDATE EC.Last_Name = T.Last_Name
 FROM EcomPro.dbo.Customer EC
 INNER JOIN Test.dbo.Customer T ON EC.ID = T.ID
 Then we have got our data back.
 Made database “EcomPro” into multi user mode.

Note: We can directly restore the database backup onto the same database instead of creating a new database when you identify the issue and you started taking database in single user mode with in or less than 5 sec of incident happened. But in our case we had a 12 min delay in getting onto the solution so we are not really sure what are all the data modifications happened on these 12 min thereof we have created a separate database and only recovered the lost data.

Third Party Tool to read Log File:

There are tools available to read log file and rollback the required transactions. ApexSQL is one of the famous tools which can recover data from accidental update / delete.

Prevention:

Humans do mistakes but we can handle these mistakes if we can respond and take the required action on-time. Train your production team, developers and release team to handle risk factors:
 DML should always be protected with transactions
 Properly validate data after DELETE and UPDATE statements. Ex: Row counts
 Proper error handling should be in place
 They should report the issue as early as possible once it is identified. Time delays increases the risk factor by losing more data.
 Once you identify a data loss on production system make your database in single user mode
 Identify the required information and see if we can rollback the operation
 Perform a Log backup immediately
 For databases design the recovery model based on SLA
 Always plan transaction log backups for critical databases

Common Mistakes:

Below are the various cases for which this solution can be worked out:
 DELETE without where clause
 UPDATE without where clause
 Mistakenly dropped a table
 Etc…..

Data Recovery – Update Without Where Clause:

Here is an example for recovering the data loss from accidental data update without where clause

First let us create a table and insert data.

Here is the customer table looks like:

Update Without Where Clause_1

Now we’ll try to apply UPDATE Without Where Clause:

Update Without Where Clause 2

Now we can see the last_name is updated as “Role” for all customers. Now we will look into the solution for how to recover this lost data? 

From the below picture we can observe there are multiple operations happened on the table customer table. But the transaction ID “0000:00000339” is the latest update transaction on that table and it has 11 entries (total 11 rows in customer table) in log. This is the transaction that the DBA accidentally executed. If you find any confusion between these transactions you can play with the other options in above query.

Update Without Where Clause 4

Now we’ll find out the transaction begin time:

Update Without Where Clause 5

 

From the above picture if you see that the transaction start time is: “2016/07/21 13:50:20:627”

 Now we’ll perform the latest transaction backup on database “Test”
 Then Restore the database with the latest full backup
 Restore the database with the latest log backup we performed just now with point in time recovery. That means we’ll restore the database just to a second before that transaction begin time:
 Transaction Begin Time: “2016/07/21 13:50:20:627”
 We’ll restore the log file to the time: “2016/07/21 13:50:19:999”

Update Without Where Clause 6

If you observe the last_column the data is back and the database recovered with the given point of time.

Summary:
It’s not always easy to deal with the accidental mistakes databases. But we should expect these and be ready with the recovery plans. Also prevention is always better than cure thereof It’s always suggested to maintain a certain standards and policies while dealing with the critical database systems. This post will help you in understanding the recovering data when we accidentally executed an update without where clause.

 

Posted in SQL Development, SQL Server DBA | Tagged , , , , , , , , , , , , | Leave a comment

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz