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.
USE Test;
GO
-- Remember when we suggested this method for one of our client
-- he asked me if the database is in simple recovery mode and not possible for log backup
-- then how can I get my data back?
-- I answered him if the data is not so important and data loss is acceptable then no issues
-- If the db is a critical db then your db should have a proper SLA and a recovery plan accordingly
-- Create Table Customer
CREATE TABLE Customer (
ID INT IDENTITY NOT NULL,
First_Name VARCHAR(50) NOT NULL,
Middle_Name VARCHAR(50),
Last_Name VARCHAR(50),
Age TINYINT NOT NULL,
Location VARCHAR(50) NOT NULL,
PhoneNo VARCHAR(15) NOT NULL,
Email VARCHAR(50) NOT NULL,
CreatedDate SMALLDATETIME NOT NULL DEFAULT(GETDATE())
);
GO
-- Data Insertion into Customer table
INSERT INTO Customer (First_Name, Middle_Name, Last_Name, Age, Location, PhoneNo, Email)
VALUES
('Hevan','K','Mayo',38,'NY','6115657','hmayo@gmail.com'),
('Tang','Zoho','Wy',38,'NY','6115654','tang@gmail.com'),
('Chris','D','Lott',42,'WDC','5664431','clott@kbank.com'),
('Haris','K','Mano',27,'AU','767574732','haris@lavo.com'),
('Hedly','M','Doven',49,'NZ','5544672109','hdven@nzs.com'),
('Tim','K','Roll',54,'NY','63356768','tim@appmake.com'),
('Shane','S','Wang',32,'CHI','392798911','shane@chimark.com'),
('Groven','VK','Wen',39,'CHI','392798912','groven@chimark.com'),
('Joel','M','Mark',27,'AU','778656779','joel@walmart.com'),
('Henan','N','L',45,'NZ','392798922','henan@nstic.com'),
('Larven','T','Me',41,'IND','9879876628','LARVEN@adps.com');
GO
---Take the Full backup of Test DB
BACKUP DATABASE [Test]
TO DISK = N'C:\temp\SQL_Backup\Test_Full.bak'
WITH NOFORMAT, INIT,
NAME = N'Test-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
SELECT * FROM Customer;
Here is the customer table looks like:
Now we’ll try to apply UPDATE Without Where Clause:
-- Now we'll try to Update the Customer Table
-- For the CustomerID 5 last name is feeded wrongly as Roll now we need to update it as Role
-- We didn't give where clause here
GO
UPDATE Customer SET Last_Name = 'Role';
-- SQL DBA executed the above statement without where condition
-- Very soon he realized that what he did to the customer table
SELECT * FROM CUSTOMER;
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?
/************************************************************************/
/****************************** Solution ********************************/
/************************************************************************/
-- As soon as you realized that there is a wrong update immediately close all user connections
-- to that database to prevent more data loss
-- First make your database into single user mode
-- Step 1
ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- Try to find the transaction details that updated the table customer
-- Identify the transaction ID
-- Step 2
SELECT
[Transaction ID] AS 'Transaction_ID',
[Operation] AS 'Operation',
[AllocUnitName] AS 'AllocUnitName'
FROM sys.fn_dblog(NULL,NULL)
WHERE [AllocUnitName] LIKE '%customer%'
ORDER BY [Transaction ID];
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.
Now we’ll find out the transaction begin time:
-- Get the transaction begin time and other info
-- Step 3
SELECT
[Current LSN] AS 'Current_LSN',
[Transaction ID] AS 'Transaction_ID',
[Operation] AS 'Operation',
[Transaction Name] AS 'Transaction_Name',
[CONTEXT] AS 'Context',
[AllocUnitName] AS 'AllocUnitName',
[Page ID] AS 'Page_ID',
[Slot ID] AS 'Slot_ID',
[Begin Time] AS 'Begin_Time',
[End Time] AS 'End_Time',
[Number of Locks] AS 'No_Locks',
[Lock Information] AS 'Lock_Info'
FROM sys.fn_dblog(NULL,NULL)
WHERE [Transaction ID] = '0000:00000339';
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”
-- Immediately perform a log backup, if it is in full recovery mode and full backup and all following log -- backups available.
-- Step 4
BACKUP LOG [Test] TO
DISK = N'C:\temp\SQL_Backup\Test_TranBkp_1.trn' WITH NOFORMAT, INIT,
NAME = N'Test-Tran Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- Now restore Full backup with no recovery
-- Step 5
USE [master]
GO
ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [Test] FROM
DISK = N'C:\temp\SQL_Backup\Test_Full.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
GO
-- Now restore all available log backups with NO RECOVERY - In our example we have only one latest log bkp
-- Now restore Transaction log backup with point intime
-- Step 6
RESTORE LOG [Test] FROM
DISK = N'C:\temp\SQL_Backup\Test_TranBkp_1.trn'
WITH FILE = 1, NOUNLOAD, STATS = 10,
STOPAT = N'2016/07/21 13:50:19:999'
GO
--After successful restore now we’ll check the customer data:
-- Now we'll check the customer table
USE Test
GO
SELECT * FROM customer
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.
