RSS

Recover Data lose with fixing corrupted DB

17 Jun

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Figure 1 Freeware Hex Editor XVI32

Note: do the same steps on the second DB of Microsoft that contain MEMORY_OPTIMIZED_DATA filegroup

 

 

 

 

 

 

 

 

 

 

 

Figure 2 Two DB’s now in Suspect Mode

Applying T-SQL solution on Two DB’s

Now I will apply the first solution T-SQL to repair the DB from the suspect mode to return it to the online mode I will apply this solution on two DB’s after this we will check the data is there is any loss or the data returned back before the update statement we do it on the first step

ALTER DATABASE [DemoSuspect_corrupted_DB] SET OFFLINE;

GO

ALTER DATABASE [DemoSuspect_corrupted_DB] SET EMERGENCY;

GO

ALTER DATABASE [DemoSuspect_corrupted_DB] SET SINGLE_USER;

GO

DBCC CHECKDB (N’DemoSuspect_corrupted_DB’, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

GO

ALTER DATABASE [DemoSuspect_corrupted_DB] SET Multi_USER;

GO

SELECT DATABASEPROPERTYEX (N’DemoSuspect_corrupted_DB’, N’STATUS’) AS N’Status’;

GO

— What about the data?

use [DemoSuspect_corrupted_DB]

GO

SELECT * FROM [Employees];

GO

 

 

 

 

 

 

Figure 3 DB fixed but with data loss

Now after applying the t-SQL solution the Db returned online but as we see with data loss we can’t return the data updated accidentally I know what was going on when the crash occurred, but what about on a busy OLTP system with hundreds or thousands of active transactions? What state will the data be in?

I applied the same solution on the second DB with MEMORY_OPTIMIZED_DATA filegroup but the T-SQL not a success with me because T-SQL for repairing the SUSPECT mode it is not supporting the MEMORY_OPTIMIZED_DATA filegroup

Msg 41836, Level 16, State 1, Line 13

Rebuilding log is not supported for databases containing files belonging to MEMORY_OPTIMIZED_DATA filegroup.

Msg 7909, Level 20, State 1, Line 13

The emergency-mode repair failed. You must restore from backup.

 

 

 

 

 

 

 

 

 

 

 

Figure 4 Rebuilding log is not supported for databases containing files belonging to MEMORY_OPTIMIZED_DATA filegroup

Really if we have this case in production DB, it will be a nightmare in this case we are not able to return Db online even.

At this time we should think of another solution the easiest way with low cost is third party tool {Stellar Data Recovery} that you can download it from here (https://www.stellarinfo.com/sql-recovery.php) and you find also the installation guide here https://www.stellarinfo.com/installation-guide/sql-recovery/installation.pdf also to know more information about the tool and what it can do and what is the benefits from it look at here https://www.stellarinfo.com/installation-guide/sql-recovery/manual.pdf

After the installation of Stellar Data Recovery, we will apply the below steps for how we can fix the DB WideWorldImporters from SUSPECT MODE  to online mode without any data loss like what is happened before in our example in the first DB DemoSuspect_corrupted_DB

  • Shutdown SQL Server instance
  • Take a copy from DB files to another place
  • Restart SQL Server instance to return the instance online
  • Open Stellar Data Recovery
  • Select the DB files we take it copy

 

 

 

 

 

 

 

 

 

Figure 5 select damaged DB

  • Click on Repair button to start the repair process
  • After few seconds you will found the Tool repair the DB successfully

 

 

 

 

 

 

 

 

 

 

Figure 6 DB repaired

  • Click on tools to save the log to check what is happened during the repairing process
  • Click File to Save the new DB files after the repair Stellar Data Recovery will give you 4 option to save the DB (MSSQL, CSV, HTML, XLS) in our case we will select MSSQL

 

 

 

 

 

 

 

 

 

Figure 7 Stellar Phoenix SQL DB repair save options

The second option of saving

  • New database: Create new DB on our SQL Server instance with new repairing files
  • Live Database: Overwrite the corrupted by the repaired one

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Figure 8 Saving Option

  • Select New database
  • Write the SQL Server instance name
  • Click on connect to start the deployment process of the new repaired DB

After few minutes new DB created with the name (Recovered_WideWorldImporters) this meaning the Stellar Data Recovery fixed the corrupted DB in few minutes even if it contains MEMORY_OPTIMIZED_DATA filegroup it fixed it with returning the data without any data loss

 

 

 

 

 

 

 

 

 

 

Figure 9 Repair process Completed

Summary

Suspect DB is one of the common issues, and we are usually committed to fixing it through possible ways. Sometimes the database gets fixed, but with data loss. At this time, I recommend you to use very helpful Stellar Phoenix SQL Database Repair Software to recover your SQL Database completely.

 
Leave a comment

Posted by on June 17, 2018 in DB corruption

 

Tags: , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.