As a SQL Server writer, I receive numerous questions on a daily basis. One recent question I received was from someone who encountered an error while trying to move and rename database files. The error message they received was:

2014-10-20 17:52:39.08 spid37s     Error: 5173, Severity: 16, State: 1.
2014-10-20 17:52:39.08 spid37s     One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.
2014-10-20 17:52:39.08 spid37s     Log file ‘E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\SQLAuth_log.ldf’ does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.

Upon further investigation, it was discovered that the database was in a “RECOVERY_PENDING” state, indicating that the recovery process could not be completed. The cause of the error was that the files being used by the database did not belong to the same database.

SQL Server has a safety mechanism in place during startup to ensure that all files belong to the same database. This is important to prevent accidentally overriding existing files. However, it is not possible to open and read the files to determine if they belong to the same database when they are attached.

To resolve this issue, we can use the DBCC CHECKPRIMARYFILE command to read the primary file header and gather information about the files. Here is a quick demonstration of the command:

CREATE DATABASE [SQLAuthority] ON PRIMARY (
    NAME = N'SQLAuthority_MDF',
    FILENAME = N'C:\Temp\SQLAuthority_MDF.mdf'
),
(
    NAME = N'SQLAuthority_NDF',
    FILENAME = N'C:\Temp\SQLAuthority_NDF.ndf'
)
LOG ON (
    NAME = N'SQLAuthority_log',
    FILENAME = N'C:\Temp\SQLAuthority_log.ldf'
)
GO

-- Detach the database
sp_detach_db 'SQLAuthority'

-- Use DBCC CHECKPRIMARYFILE to read file header and get details
-- Only the first file (primary data file) will have output
DBCC CHECKPRIMARYFILE ('C:\Temp\SQLAuthority_MDF.mdf', 1)

-- Additional parameters can provide more information about the file
DBCC CHECKPRIMARYFILE ('C:\Temp\SQLAuthority_MDF.mdf', 2)
DBCC CHECKPRIMARYFILE ('C:\Temp\SQLAuthority_MDF.mdf', 3)

By using the DBCC CHECKPRIMARYFILE command, we can retrieve various properties of the primary files, such as file IDs, filegroup ID, logical name, physical name, database version, and collation. This command can be particularly useful when you have files from the same database but do not know their logical file names, or when the attach operation is not working due to corruption.

It is important to note that this command should not be used to attach two different files from the same database taken from different servers. The command is designed to provide information about the files, not to bypass the normal attach process.

I hope you found this option for resolving database file errors interesting and informative. Feel free to reach out with any further questions or topics you’d like me to cover in future articles.