SQL Server Database Corruption – File Header Page Corruption

-> I tried attaching a MDF file and got below error,

Msg 5172, Level 16, State 15, Line 1
The header for file ‘F:\data\JBDB_Data.mdf’ is not a valid database file header. The PageAudit property is incorrect.
Msg 1802, Level 16, State 7, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

-> It is clear from the message that the file header page of this database is corrupted.

-> File Header Page is the first page on a file that is used by SQL Server to store information about the attributes of the file. Refer article for more details.

-> If File Header Page is corrupted, the best option will be to restore the database with latest available backup.

-> Lets create a new database with same name as the corrupted MDF file. Stop SQL Services and then replace the MDF file of this newly created database with the corrupted MDF file that we were trying to attach. Start SQL Services and check the status of the database.

-> The database is in “Recovery Pending” State,

-> Lets check the SQL Server Error Log,

2021-04-10 17:16:03.250 spid23s Starting up database ‘JBDB’.
2021-04-10 17:16:03.250 spid11s Starting up database ‘mssqlsystemresource’.
2021-04-10 17:16:03.250 spid23s Error: 5172, Severity: 16, State: 15.
2021-04-10 17:16:03.250 spid23s The header for file ‘F:\data\JBDB_Data.mdf’ is not a valid database file header. The PageAudit property is incorrect.

-> Tried executing DBCC checkdb on the database and got below as expected,

dbcc checkdb ([JBDB])
GO

Msg 945, Level 14, State 2, Line 1
Database ‘JBDB’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

-> Lets try putting the database in emergency mode,

ALTER DATABASE [JBDB] SET EMERGENCY;
GO

-> Getting below error which same as previous occurrences,

Msg 5172, Level 16, State 15, Line 1
The header for file ‘F:\data\JBDB_Data.mdf’ is not a valid database file header. The PageAudit property is incorrect.

-> The only option left is to restore the database with a valid backup. But the bad news is that there are no backups.

-> SQL Server Database recovery tools is something we can check as an option. In my case, I tried “Stellar Repair for MS SQL“.

-> Before using Stellar Repair for MS SQL tool, it is better to backup the MDF and LDF file for safety.

-> Once done make another copy of the files to use it with Stellar Repair for MS SQL tool. In my case I have placed the file in location F:\Debug,

-> Open “Stellar Repair for MS SQL” and click on “Select database”,

-> Select the corrupted MDF file and click on Repair,

-> Repair completed,

-> Once repaired, Click on Save,

-> Processing this new database is going to take time depending on the database size. The time spent is definitely worth it, as I was able to recover the whole database.

-> Once the database is recovered. Do thorough testing and make sure the database looks alright.

-> I executed DBCC CHECKDB on the new database recovered by Stellar Repair for MS SQL and it came out clean.

-> There you go. Database is available now.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Login to Azure Virtual machine using Azure Active Directory credentials

-> I am planning to create a series of blogs on Azure Active Directory and how SQL server can be connected using Azure AD.

Other Blogs in this Series,
-> Integrate Azure Active Directory with Active Directory in Azure Virtual Machine
-> Create a login in Azure Virtual Machine Active Directory and Sync it to Azure Active Directory
-> Connecting Azure SQL Database using Azure Active Directory – Password / Azure Active Directory – Universal with MFA

-> In this blog we will discuss the procedures using which we will be able to login to Azure Virtual machine using Azure Active Directory credentials.

Create an Azure virtual Machine

-> Basics tab is shown as below,

-> Select appropriate settings for Disks and Networking tab.

-> Management tab as shown below. This is the tab where we will enable “Login with Azure Active Directory” option,

-> Open the virtual machine jbvm in Azure portal and click on “Access Control (IAM)”. Click on “+ Add” and click on “Add role assignment”,

-> Click on Save,

-> Lets try connecting to Azure Virtual Machine jbvm using the Azure Active Directory account.

-> Login to Azure virtual machine will be possible using Azure Active Directory logins from now on.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.