Share via

SQL Server: How to recover from corrupt SQL Server database when recovery via TSQL might not be possible.

  • Article

Introduction

A corrupt database is a nightmare for DBA and whole business. Depending on nature of corruption and what caused it usually creates issues for SQL Server  administrators and users to get around with the corruption without having downtime. There are various types of corruption some which cannot be recovered by native TSQL commands or by options provided by SQL Server. Then what could be assured solution for it ?. Here we need a tool or an expert to help us get around in such situation. To hire Microsoft experts can be difficult for some small  firms and also there is some turn around time for the MS guy to respond back, similarly finding an expert recovery person might also take some time. With corrupt database every time that passes is valuable. 

Today we will see one such tool Stellar Database recovery software which is used to recover corrupt database with minimal time.

Note

Stellar Database recovery team asked me to review there product as an MVP. I do not speak or represent Stellar database recovery tool, if you have any questions it should be directed to them. This article  tries to show to DBA's out there who does not have facility of expert recovery agent, are not able to get help/raise ticket from MS or for some who doe not have time to find the both and want quick recovery. 

Stellar data recovery team provided me with corrupt SQL Server database.

Recovering Database Via T-SQL

We will first see if we can recover corrupt database via native T-SQL mechanism. To test the process of recovering via t-sql, I tried attaching it to SQL Server 2016 database server.

CREATE DATABASE   StellarCorruptDatabase ON
 
(FILENAME = 'E:\Corruption Test\Stellar Data recovery\NewDatabase.mdf')
 
For ATTACH_REBUILD_LOG
 
GO

In the above try to attach the corrupt SQL database files with SQL server, it failed with the below message:

Clearly this is a corrupt SQL database. But can we not repair it/attach it with some T-SQL commands?  We will try this as well.

We will try a method of hack attach

  1. Create a new database of equal size to the one we are trying to attach.
  2. Take new database offline/Shutdown SQL Server
  3. Replace the .mdf file of new SQL database with old one.
  4. Bring SQL Server online, mostly database would not come online and will go in suspect mode.
  5. Put database into Emergency and try repair
  6. Run DBCC CHECKDB (db_name, REPAIR_ALLOW_DATA_LOSS) and try rebuilding the log and removing corruption. Please note this can lead to data loss ,which you would have no idea about it.

Step 1

CREATE DATABASE  [NewDatabase]
 
 CONTAINMENT = NONE
 
 ON  PRIMARY
 
( NAME  = N'NewDatabase', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\NewDatabase.mdf' , SIZE = 10485760KB , FILEGROWTH = 65536KB )
 
 LOG ON
 
( NAME  = N'NewDatabase_log', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\NewDatabase_log.ldf' , SIZE = 102400KB , FILEGROWTH = 65536KB )
 
GO

Step 2/3

Take database offline and replace the mdf file with new one.

Step 4

If you try to bring it online via SSMS, you may bet the message like:

Step 5

We will try to bring it in emergency mode, so that it can be accessible. With below command

alter database  [NewDatabase] set  emergency
 
go

The emergency mode failed and database is still not accessible.

You can see that with above method, repair of SQL database is not possible as database is not showing up as online or accessible. Now we will use the Stellar Phoenix SQL Database Repair Software to repair the database and access it

Take the database offline from SSMS. For this you need to first take it out of emergency mode and then take it offline

alter database  [NewDatabase] set  online
 
go
 
alter database  [NewDatabase] set  offline

If you still want to give a try with TSQL, you can refer to blogs of  Paul Randal

Recovering via Stellar Tool

The tool can be download from This Link to try for free and to see the full preview of all recovered objects of corrupted SQL database. This feature helps to check the accuracy of scan results of the software.

After installing the tool and launching it from desktop it would look like below

Launch Stellar SQL recovery tool from desktop and select the mdf file and click repair. See below pic

After you click repair, the tool will start reading the data file and fixing the corruption. You can see the logs at the bottom.

After repair is done, click OK.

If you want to save logs generated after repair click on Tools and then click on Save Log to save the log file as text file

After this, you can expand the plus signs in your database and see all the objects

Now we have our database in the tool, we will see how we can export it to database and create a fresh database which is accessible.

Go to file and click on Save

And below window will pop up

The default option is MSSQL and that is what we are usually interested to save in.

Click OK

Select new Database radio button, select the server name and depending on location you can either select default location or location where you want the data files to be created.

Click connect

At bottom you would see the processing going on and after the database is exported you would see below screen

Select OK.

Now go to SSMS and check out the new database.  The name of database would be Recovered_DatabaseName. In my case the recovered SQL database was saved as recovered_newdatabase, whereas newdatabase was name of the original corrupt SQL database.

Conclusion

I would recommend this tool to repair corrupt SQL Server database. This saves lot of your time and effort and especially if you do not have expert knowledge about database and recovery. Considering how much a downtime can cost such tools are great lifesavers.