Avatar of Steve Bohler
Steve Bohler
Flag for United States of America asked on

SQL Server Corrupt database issue

Hello,

I seem to have a corrupt production database (SQL Server 2008 Standard edition)

When trying to connect to it, get the error:

"SQL Server detected a logical consistency-based I/O error: unable to decrypt page due to missing DEK. It occurred during a read of page (1:0) in database ID 13 at offset 0000000000000000 in file ..."

I can't even run DBCC Checkdb on it without getting an error that it can't connect to it.

I do have a backup from the previous day.

A) Do I have to try and restore from the previous backup (and lose a day of data)? Or Is there a way to try and repair?

B) If I need to restore, what steps to restore to the previous backup? The "restore" menu option for this offline database is grayed out.

Thanks!

Steve
Microsoft SQL Server

Avatar of undefined
Last Comment
Bharat Bhushan

8/22/2022 - Mon
Vitor Montalvão

I can't even run DBCC Checkdb on it without getting an error that it can't connect to it.
Is the database online or offline or it has any other status, like Suspect?
Steve Bohler

ASKER
It's Offline.
Vitor Montalvão

Try to bring it online and then run the DBCC CHECKDB.
If you can't bring it online, provide the error or the SQL Server Error Log.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Steve Bohler

ASKER
I can't bring it online.

The error is:

SQL Server detected a logical consistency-based I/O error: unable to decrypt page due to missing DEK. It occurred during a read of page (2:0) in database ID 13 at offset 0000000000000000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db_log.ldf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Vitor Montalvão

What happened for the corruption occurs?
You can always try to rebuild the transaction log (you will loose all non committed transactions) by detaching the database and then run the following command:
CREATE DATABASE databaseName
ON (FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\dbname.mdf')
FOR ATTACH_REBUILD_LOG;

Open in new window

NOTE: Provide the correct name for the data file (.mdf).
Steve Bohler

ASKER
I don't know how the file became corrupted.

I'm unable to detach the database. I get the same errors about I/O error and unable to decrypt the page due to missing DEK.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

If you can, stop the SQL Server (it will be hard on Production environments) and then copy the database .mdf file to another place (rename the old one). Start SQL Server and this time the database won't be presented. Now you can run the command I posted before.
Steve Bohler

ASKER
I stopped the SQL server. I made a copy of the MDF file and moved it a different location. I renamed the old MDF file. I started the SQL server, but the database is still in the list (but it doesn't say Offline, the icon next to it has a person on the image).
Vitor Montalvão

What the following returns?
SELECT *
FROM sys.sysdatabases
WHERE dbid = 13

Open in new window

NOTE: I used dbid=13 because is the ID presented in the error message. If that's not the correct ID please replace it before running the command.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Steve Bohler

ASKER
name      dbid      sid      mode      status      status2      crdate      reserved      category      cmptlevel      filename      version

Db_users      13      0x01      0      69632      1627389952      2016-02-25 17:05:46.627      1900-01-01 00:00:00.000      0      100      D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db_users.mdf      655
Vitor Montalvão

I don't know that status code. Please check what's the description:
SELECT databasepropertyex('Db_users', 'STATUS')

Open in new window

Steve Bohler

ASKER
The errors also show the same corruption problem with the mdf file.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Steve Bohler

ASKER
It says "SUSPECT"
Vitor Montalvão

(but it doesn't say Offline, the icon next to it has a person on the image).
Ok, the person means the database is in a Single Mode status.
Stop the SQL Server and rename back the .mdf file to the old one. Start the SQL Server and try to run the following:
USE master
GO

ALTER DATABASE Db_users
SET MULTI_USER
GO

ALTER DATABASE Db_users
SET PAGE_VERIFY CHECKSUM

Open in new window

Steve Bohler

ASKER
Unfortunately, it failed:

Msg 945, Level 14, State 2, Line 1
Database 'Db_users' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 824, Level 24, State 6, Line 1
SQL Server detected a logical consistency-based I/O error: unable to decrypt page due to missing DEK. It occurred during a read of page (1:0) in database ID 13 at offset 0000000000000000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db_users.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: unable to decrypt page due to missing DEK. It occurred during a read of page (2:0) in database ID 13 at offset 0000000000000000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db_users_log.ldf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Steve Bohler

ASKER
Actuallly, the little person icon is now gone.
Steve Bohler

ASKER
But I still can't access restore or do anything with this existing database.
alanccw

Hi,skbohler

The MDF file format is very complex. It starts with a file header containing the meta information of the whole file, followed by several parts containing the actual data. You can find more detailed information about the MDF file format by searching on Google with the keyword “MDF file format”. If the file header containing the meta information is corrupt, or any follow-up parts are damaged or corrupted, then you will not be able to open the file correctly with SQL Server. In such a case, the first step is to try to use the built-in repair or recovery function in the SQL Server, to see if it can repair or recover the corrupt file. If not, then you need to resort to a third party SQL Server repair tool to scan the corrupted file and retrieve all recoverable data for you. It is also possible to find a data recovery expert or company to do the task, but in most of the cases, they will also use a third-party tool to do the recovery for you.

The main difference between using the software by yourself and by a third-party service company is:
(1)      The company is familiar with the data recovery industry so he knows which software is the best one. While you need to search and compare the software online by yourself, which is time-consuming.
(2)      The company purchases the software for multiple usages, while you purchase the software for one-time usage only. So normally the service fee may be cheaper than the license fee for the software.
(3)      DIY will keep your data confidential while do the recovery via a company may cause data breach.

If you want to perform a DIY recovery, then you can first use the SQL Server build-in command DBCC CheckDB and CheckTable to repair it, see:

http://technet.microsoft.com/en-us/library/ms176064.aspx

and

http://technet.microsoft.com/en-us/library/ms174338(v=sql.110).aspx

to get more information about the commands. This is the recommended solution which you should try first.

If the corruption is severe and the above commands do not work, then you may try DataNumen SQL Recovery at:

https://www.datanumen.com/sql-recovery/

I have used it in the past and it works well.

Hope this will help. Good Luck!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Steve Bohler

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

So you went for option A:
A) Do I have to try and restore from the previous backup (and lose a day of data)?
And lost a day of data, right?
Steve Bohler

ASKER
YEs.
Vitor Montalvão

Recommendation to close this question by accepting the above comments as solutions.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Bharat Bhushan

I agree, updated backup is the beast way to fix any type of database corruption. If corruption is not repairable by using manual ways then, SQL database repair software can be secure way to fix it. Stellar Phoenix SQL Database Repair Software is recommended by Microsoft MVPs.