How to identify when data is deleted from the table and how to recover deleted record form table in SQL Server?

If you are DBA or want to become DBA then you must know this solution.

Scenario: - Someone has deleted the few record form the table from production. Now client is asking to recover that data from database backup file. DBA have to find out when and who deleted the data.

Below are the simple steps to recover the data-

1.       Created database <i.e. Recover_Data_DB >-

USE [master]

GO

CREATE DATABASE [Recover_Data_DB]

 CONTAINMENT = NONE

 ON  PRIMARY

( NAME = N'Recever_Data_DB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL_INSTANCE3\MSSQL\DATA\Recever_Data_DB.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'Recever_Data_DB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL_INSTANCE3\MSSQL\DATA\Recever_Data_DB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

2.       Create Table <i.e. Table_1>

USE [Recover_Data_DB]

GO

  

CREATE TABLE [dbo].[Table_1](  [Name] [varchar](50) NULL) 

  

GO

  

3.       Insert value and verify the record.

SELECT  *  FROM [Recover_Data_DB].[dbo].[Table_1]

4.       After Inserting record take Full back up and Take T. Log backup.

5.       Now delete the record from the table

Delete [Recover_Data_DB].[dbo].[Table_1] where name like '%Verma%'

  

6.       Using below query can find out the deleted transaction log ID <i.e. 0000:00000313>  Here you will know all deleted transaction. 

Use [Recover_Data_DB]

SELECT

 [Current LSN],   

 [Transaction ID],

     Operation,

     Context,

     AllocUnitName

   

FROM

    fn_dblog(NULL, NULL)

WHERE

    Operation = 'LOP_DELETE_ROWS'

7.       Now using below query we can know the beginning  for the delete transaction. Just use the transaction ID (above query will give you the transaction ID <'0000:00000313'>). 

Use [Recover_Data_DB]

go

SELECT

 [Current LSN],   

 Operation,

     [Transaction ID],

     [Begin Time],

     [Transaction Name],

     [Transaction SID]

FROM

    fn_dblog(NULL, NULL)

WHERE

    [Transaction ID] = '0000:00000313'

AND

    [Operation] = 'LOP_BEGIN_XACT'

8.       Now you will get the below result (LSN number, delete time etc..)

8.1 Who deleted the data(UserName). In 8th step you will get Transaction SID. Run the below query with Transaction SID to know who delete the data.

USE MASTER

GO  

SELECT SUSER_SNAME(0x01050000000000051500000010290E9237E7FD9BEAF0DB39F4010000)

9.   Now, you have to convert the above got Current LSN Number <i.e 00000023:000000c8:0002> (it is in hexadecimal format) in Decimal format.

Divide this value in 3 parts from “:” -

Hexadecimal value - 00000023:000000c8:0002

Hexadecimal value	Converted Decimal Value	Have to Add Zero	Converted value	Now Merge converted value
00000023	35		35	35000000020000002
00000090	200	7 zero	0000000144
0002	2	4 Zero	00001

10.   Now restore database in test Server or change the name of DB while restoring it, Use below query-

Full Back-up Restore with No recovery option

USE [master]

RESTORE DATABASE [Recover_Data_DB_Copy]

FROM  DISK = N'D:\Recover_Data_DB_Full.bak' WITH  FILE = 1,

MOVE N'Recever_Data_DB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL_INSTANCE3\MSSQL\DATA\Recever_Data_DB_Copy.mdf',

MOVE N'Recever_Data_DB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL_INSTANCE3\MSSQL\DATA\Recever_Data_DB_log_copy.ldf',

NORECOVERY,  NOUNLOAD,  STATS = 5

GO

T Log back restore with STOPBEFOREMARK option

RESTORE LOG [Recover_Data_DB_Copy] FROM

DISK = N'D:\Recover_Data_DB_TLog.trn' WITH  FILE = 1,

STOPBEFOREMARK = 'lsn:35000000020000002'

GO

11.   Change the database from No Recovery mode to Recover mode

RESTORE DATABASE [RECOVER_DATA_DB_COPY] WITH  RECOVERY

12.   Verify recovered data

SELECT * FROM [Recover_Data_DB].[dbo].[Table_1]

Wow - We recovered the deleted data.

Please let me know if you know any other method to do recover the deleted record from table Jainendra Verma

How to identify longest running queries in SQL Server?

There are two ways to identify slow running queries

1)      Profiler (By using duration of the query)

2)      sys.dm_exec_query_stats, sys.dm_exec_sql_text and sys.dm_exec_requests DMVs etc

3)      DBCC OPENTRAN

Example 1-

SELECT  creation_time

        ,last_execution_time

        ,total_physical_reads

        ,total_logical_reads

        ,total_logical_writes

        , execution_count

        , total_worker_time

        , total_elapsed_time

        , total_elapsed_time / execution_count avg_elapsed_time

        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

         ((CASE statement_end_offset

          WHEN -1 THEN DATALENGTH(st.text)

          ELSE qs.statement_end_offset END

            - qs.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

ORDER BY total_elapsed_time / execution_count DESC;

Example 2-

                        select *, sql_handle from sys.dm_exec_requests

                 select  * from sys.dm_exec_sql_text (0x020000004D4F6005A3E8119F3DD3297095832ABE63E312F2)

Example 3

SELECT

    [ds_tst].[session_id],

    [ds_es].[login_name] AS [Login Name],

    DB_NAME (ds_tdt.database_id) AS [Database],

    [ds_tdt].[database_transaction_begin_time] AS [Begin Time],

    [ds_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],

    [ds_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],

    [ds_est].text AS [Last T-SQL Text],

    [ds_eqp].[query_plan] AS [Last Plan]

FROM

    sys.dm_tran_database_transactions [ds_tdt]

JOIN

    sys.dm_tran_session_transactions [ds_tst]

ON

    [ds_tst].[transaction_id] = [ds_tdt].[transaction_id]

JOIN

    sys.[dm_exec_sessions] [ds_es]

ON

    [ds_es].[session_id] = [ds_tst].[session_id]

JOIN

    sys.dm_exec_connections [ds_ec]

ON

    [ds_ec].[session_id] = [ds_tst].[session_id]

LEFT OUTER JOIN

    sys.dm_exec_requests [ds_er]

ON

    [ds_er].[session_id] = [ds_tst].[session_id]

CROSS APPLY

    sys.dm_exec_sql_text ([ds_ec].[most_recent_sql_handle]) AS [ds_est]

OUTER APPLY

    sys.dm_exec_query_plan ([ds_er].[plan_handle]) AS [ds_eqp]

ORDER BY     [Begin Time] ASC;

4)      DBCC OPENTRAN

Use this to clean buffer data for testing purpose

Dbcc Dropcleanbuffers

BEGIN TRAN
UPDATE Person.Address SET AddressLine2 = '' WHERE AddressID = 112

Now open another connection in SQL and execute following script.

DBCC OPENTRAN

Result: - Using SPID can get Query-

Transaction information for database 'AdventureWorks2012'.

Oldest active transaction:

 SPID (server process ID): 57

 UID (user ID) : -1

 Name : user_transaction

 LSN : (163:324:1)

 Start time : Jan 16 2014 4:34:58:813PM

 SID : 0x0105000000000005150000001650720d0cca921474e68766e8030000

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Please commnet if you know any other way to get or  identify longest running queries in SQL Server.- Jainendra Verma