How to restore a corrupted SQL database?

Corruption in SQL Server database can occur in many ways. It can happen if the server crashes, while trying to install a SQL Server update and somehow it stops abruptly, or if the disk hosting the database suffers from corruption at block level. In this post, we’re going to discuss how to restore a corrupted SQL Server database using SQL backup via Management Studio and using third-party SQL repair software.

For this post, we’re going to use the following assets:

• SQL Server 2019 Developer Edition (free to use as it is not used in a production server)
• Stack Overflow open source database 2010 version (It’s less than 10 GB)
• Windows Server 2016 Standard Edition (evaluation version for tests purpose only)
• Stellar Repair for MS SQL software (version 10.1.0.0)

Corrupting the Database

Here, we’ll simulate logical corruption in the MDF file from the StackOverflow2010 database. Logical corruption in this case involves a SQL Server Page. According to Microsoft, a page is the fundamental unit of data. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages, numbered contiguously from 0 to n. Disk operations are performed at the page level. SQL Server reads or writes whole data pages.

The root causes of page corruption may include the following:

• Hardware issues (disk hosting the database fails)
• Power failure (database is shutdown abruptly)
• Virus attack (that can damage SQL Server files)
• SQL Server Updates (patches or updates stop before ending)

We can identify corruption using the DBCC command.

But first we’ll make a full backup and a log backup so that we can recover to the previous state

With our backup done, let’s go through the necessary steps to corrupt the database.

We’ll simulate a logical corruption forcing to write incorrect data in an index page.

To find information about the page, we need to use:

DBCC IND (StackOverflow2010, 'dbo.Comments', 1)

For changing our database to single user mode and corrupting it:

ALTER DATABASE StackOverflow2010 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC WRITEPAGE('StackOverflow2010', 1, 180, 60, 1, 0x00, 1)

With that our table is messed up. When trying to select our table, we’ve got the following error:

Restore Database using Management Studio (SSMS)

To do the restore, we’ll use another name so that we can later export the corrupted table only. For this to work, we need to change the physical name of the data files. Otherwise, it will cause an error saying that the files are already being used.

restore database StackOverflow2010_restoressms from disk='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\StackOverflow2010_backup.bak'

with

move 'StackOverflow2010' to 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\StackOverflow2010_restoressms.mdf',

move 'StackOverflow2010_log'?to 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\StackOverflow2010_restoressms_log.mdf',

replace,stats=1,NORECOVERY

restore log?StackOverflow2010_restoressms from disk='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\StackOverflow2010_log.trn' with stats=1

With that we have our database up again. We can see the tables restored.

Now, validate the data.

The next step would be to export the comments table to the original database.

Restore Database using SQL Repair Software

Now, we’ll use an advanced SQL repair software - Stellar Repair for MS SQL, to recover our corrupted table in the MDF file. This software can read corrupted SQL database (MDF) file, repair it, and recover all the database objects.

After launching the software, this screen appeared.

We selected our corrupted MDF file from the original location.

And then click on Repair.

Then, select the Standard Scan to proceed.

We can also see the progress while it’s running the repair actions.

With the scan finished, our MDF file is repaired and we can see the recovered objects.

Now that we saw our objects recovered, we’ll save only the comments table in a new database to validate.

Now that we saw our objects recovered, we’ll save only the comments table in a new database to validate.

When validating the data, we can see that the software has created a database and uploaded our table.

Conclusion

In this post, we’ve mentioned two ways to recover a corrupted SQL Server database. The manual restore operation involves many variables. If the backup is located outside the server, you would need to ask for the files and set a point in time recovery, applying multiple transaction logs to keep up-to-date.

The easiest way is by using Stellar Repair for MS SQL as it scans the MDF file and restores the recovered data to the source in a separate database. Using the software, you can recover a damaged table from a temporary database without much effort.