RSS

Tag Archives: MEMORY_OPTIMIZED_DATA

Recover Data lose with fixing corrupted DB

Introduction

While working on the production environment, we as database administrators are exposed to unforeseen problems associated to our SQL Server. This may lead to the crucial data loss, in case we don’t have updated backup solution. Such situations are not less than the nightmare for DBA’s when recovery of the complete data becomes their utmost responsibility

Problem

I will show here two corrupted issues one in normal DB and another one in DB with MEMORY_OPTIMIZED_DATA filegroup

the suspect mode is one of the common issues that can lead us for data loss and in my case I don’t have a backup for this DB, and I am committed to returning the DB online without any data loss after more research and investigation I tested two solutions

 

Before going to the two solutions deeply and how we can repair the DB I will simulate the issue by creating new DB and deleting some record from the log files using (Freeware Hex Editor XVI32) you can download it from here. http://www.chmaas.handshake.de/delphi/freeware/xvi32/xvi32.htm

Preparing the corrupted DB’s

  • First issue: Normal DB I’m going to create a simple database to use, called DemoSuspect_corrupted_DB with a table and some random data.

USE [master];

GO

CREATE DATABASE [DemoSuspect_corrupted_DB];

GO

USE [DemoSuspect_corrupted_DB];

GO

CREATE TABLE [Employees] (

[FirstName]   VARCHAR (20),

[LastName]    VARCHAR (20),

[YearlyBonus] INT);

GO

INSERT INTO [Employees] VALUES (‘Paul’, ‘Randal’, 10000);

INSERT INTO [Employees] VALUES (‘Kimberly’, ‘Tripp’, 10000);

GO

— Simulate an in-flight transaction

BEGIN TRAN;

UPDATE

[Employees]

SET

[YearlyBonus] = 0

WHERE

[LastName] = ‘Tripp’;

GO

 

— Force the update to disk

CHECKPOINT;

GO

 

Open stop the SQL Server services using below T-SQL or manually

SHUTDOWN WITH NOWAIT;

GO

 

  • Second issue: I will use here one of Microsoft DB sample for SQL Server 2016 version (WideWorldImporters-Full.bak) you can download the DB backup file from HERE, and I will restore the DB on my SQL Server instance with creating a table and some random data

USE [WideWorldImporters];

GO

CREATE TABLE [Employees] (

[FirstName]   VARCHAR (20),

[LastName]    VARCHAR (20),

[YearlyBonus] INT);

GO

INSERT INTO [Employees] VALUES (‘Paul’, ‘Randal’, 10000);

INSERT INTO [Employees] VALUES (‘Kimberly’, ‘Tripp’, 10000);

GO

— Simulate an in-flight transaction

BEGIN TRAN;

UPDATE

[Employees]

SET

[YearlyBonus] = 0

WHERE

[LastName] = ‘Tripp’;

GO

 

— Force the update to disk

CHECKPOINT;

GO

Open stop the SQL Server services using below T-SQL or manually

SHUTDOWN WITH NOWAIT;

GO

 

Now the services offline let us delete files from log file from the two DB’s to corrupt the DB’s I will do it in one DB, and you can do the same scenario on the second DB

  • Download the (Freeware Hex Editor XVI32) from HERE
  • After installation take a copy from your DB log file in another location
  • Click File >> New >> insert >> Select the copy log file for example Ldf
  • Delete some files are and save the changes over the same log file you take it copy
  • Replace this log file by the source one
  • Now the log file of the DB damaged
  • Restart the SQL instance to return it online
  • You will find the DB in SUSPECT mode

Read the rest of this entry »

 
Leave a comment

Posted by on June 17, 2018 in DB corruption

 

Tags: , , , , ,