Recover Deleted Data

Recover Deleted Data

Today one of my local development database data deleted mistakenly and create same data again little bit painfully because not only the amount of data, it’s has very time taken logic behind it. 
Unluckily I haven’t backup of that database. Normally when I’m doing some critical development test in my local database, I was creating database snapshot. But I forget it :(.

I was bit lucky my database was full recovery mode. By the way I have taken full backup(luckily not copy only backup this time :), normally I was taking copy only backup every time as best practices) few days back.

Well… with in few minute I have manage to recover my deleted table and deleted data.After finish this I was thinking to publish my T-SQL script in my web page with step by step explains.

As a starting we need to create database with full recovery mode and need to take full backup(not copy only backup) as follows,

USE master
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DATABASEUSERGROUP') BEGIN
    ALTER DATABASE [DATABASEUSERGROUP] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [DATABASEUSERGROUP];
END
GO
CREATE DATABASE DATABASEUSERGROUP;
GO
ALTER DATABASE [DATABASEUSERGROUP] SET RECOVERY FULL WITH NO_WAIT;
GO

--you need to change backup file path for you own
BACKUP DATABASE [DATABASEUSERGROUP] TO  DISK = N'E:\Backups\MSSQL\DATABASEUSERGROUP_FULL_04NOV2014.bak'
WITH    NOFORMAT, NOINIT, 
        NAME = N'DATABASEUSERGROUP-Full Database Backup',
        SKIP, NOREWIND, NOUNLOAD,  STATS = 10;
GO
DECLARE @backupSetId AS int;
SELECT @backupSetId = position FROM msdb..backupset
WHERE (database_name=N'DATABASEUSERGROUP')
AND (backup_set_id=(SELECT max(backup_set_id) FROM msdb..backupset WHERE database_name=N'DATABASEUSERGROUP'))

IF @backupSetId is null BEGIN
    RAISERROR(N'Verify failed. Backup information for database ''DATABASEUSERGROUP'' not found.', 16, 1);
END

--you need to change backup file path for you own
RESTORE VERIFYONLY FROM  DISK = N'E:\Backups\MSSQL\DATABASEUSERGROUP_FULL_04NOV2014.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND;
GO

Next I’m going to create some data, above created database as follows,

USE DATABASEUSERGROUP
GO
CREATE TABLE tblDeleteRecords(col1 int,col2 varchar(128),col3 datetime);
CREATE TABLE tblDropTable(col1 int,col2 varchar(128),col3 datetime);
GO
--INSERT DATA
INSERT INTO tblDeleteRecords(col1,col2,col3)
SELECT id,name,GETDATE() FROM sys.sysobjects;
GO
INSERT INTO tblDropTable(col1,col2,col3)
SELECT id,name,GETDATE() FROM sys.sysobjects;
GO
SELECT * FROM tblDeleteRecords;
SELECT * FROM tblDropTable;
GO
CHECKPOINT;
SELECT GETDATE()
--2014-11-05 11:41:06.323
GO

OK! Let’s delete and see!!!

--DELETE DATA
DELETE FROM tblDeleteRecords WHERE col1<20;
SELECT GETDATE()
--2014-11-05 11:42:07.487
GO
--DROP TABLE
DROP TABLE tblDropTable;
SELECT GETDATE()
--2014-11-05 11:43:32.153
GO

With this article I was thinking to discuss about SQL Server undocumented “fn_dblog” function. I have no idea why Microsoft will listed this as undocumented. With using above function you can view LSN number. Who is deleted records and more information contains in transaction log file.
 

SELECT  SUSER_SNAME([Transaction SID]) userName,B.[Current LSN],B.Operation,[Transaction ID],B.AllocUnitName,B.[Description]
FROM fn_dblog(NULL, NULL) B
WHERE (AllocUnitName LIKE '%tblDeleteRecords%' OR AllocUnitName LIKE '%tblDropTable%')
AND [Operation] = 'LOP_INSERT_ROWS';

SELECT  SUSER_SNAME([Transaction SID]) Last_record_deleted_user,[Current LSN],Operation,[Transaction ID],AllocUnitName,[Description]
    FROM fn_dblog(NULL, NULL)
    WHERE
        [Transaction ID] = (
            SELECT top 1 [Transaction ID] FROM fn_dblog(NULL, NULL)
            WHERE [Operation] = 'LOP_INSERT_ROWS' AND AllocUnitName LIKE '%tblDeleteRecords%' OR AllocUnitName LIKE '%tblDropTable%'
            order by [Transaction ID] desc
        )
        AND [Operation] = 'LOP_BEGIN_XACT'
   
GO
 
SELECT  SUSER_SNAME([Transaction SID]) userName,B.[Current LSN],B.Operation,[Transaction ID],B.AllocUnitName,B.[Description]
FROM fn_dblog(NULL, NULL) B
WHERE AllocUnitName LIKE '%tblDeleteRecords%'
AND [Operation] = 'LOP_DELETE_ROWS';

SELECT  SUSER_SNAME([Transaction SID]) Last_record_deleted_user,[Current LSN],Operation,[Transaction ID],AllocUnitName,[Description]
    FROM fn_dblog(NULL, NULL)
    WHERE
        [Transaction ID] = (
            SELECT top 1 [Transaction ID] FROM fn_dblog(NULL, NULL)
            WHERE [Operation] = 'LOP_DELETE_ROWS' AND AllocUnitName LIKE '%tblDeleteRecords%'
            order by [Transaction ID] desc
        )
        AND [Operation] = 'LOP_BEGIN_XACT'
   
GO

SELECT  SUSER_SNAME([Transaction SID]) Last_record_deleted_user,[Current LSN],Operation,[Transaction ID],AllocUnitName,[Description]
    FROM fn_dblog(NULL, NULL)
    WHERE
        [Transaction ID] = (
            SELECT top 1 [Transaction ID] FROM fn_dblog(NULL, NULL)
            order by [Transaction ID] desc
        )
        AND [Operation] = 'LOP_BEGIN_XACT'
   
GO

Well! Hope you have view some information contains in Transaction log file. Now is time to recover above deleted data. Initially you after delete records you need to take tail log backup of SQL server database. As follows,

--SQL Server Transaction Log Backups
BACKUP LOG DATABASEUSERGROUP TO DISK = N'E:\Backups\MSSQL\DATABASEUSERGROUP_TR_LOG_04NOV2014.trn'
GO

Next you need to restore your last full backup with “NORECOVERY” state. After finish this, you will noticed your database is “on recovery” mode. Well finally you need to restore Tail log backup as follows,

--First recovery Deleted Table
USE master
go
RESTORE DATABASE [DATABASEUSERGROUP] FROM  DISK = N'E:\Backups\MSSQL\DATABASEUSERGROUP_FULL_04NOV2014.bak'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO

RESTORE LOG DATABASEUSERGROUP FROM DISK = N'E:\Backups\MSSQL\DATABASEUSERGROUP_TR_LOG_04NOV2014.trn' WITH STOPAT = '2014-11-05 11:43:30.000'

--Next recovery all
USE master
go
RESTORE DATABASE [DATABASEUSERGROUP] FROM  DISK = N'E:\Backups\MSSQL\DATABASEUSERGROUP_FULL_04NOV2014.bak'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO

RESTORE LOG DATABASEUSERGROUP FROM DISK = N'E:\Backups\MSSQL\DATABASEUSERGROUP_TR_LOG_04NOV2014.trn' WITH STOPAT = '2014-11-05 11:41:07.000'

Hope you will have good understand of this article.  
Cheers...
and Most welcome your comments and ideas...