SQL Server database files (MDF) and backup files (BAK) contain metadata that reveals their origin version. The file header stores compatibility level information that directly correlates with SQL Server versions.

For MDF files attached to a SQL Server instance:

SELECT 
    name AS [Database Name],
    compatibility_level,
    SERVERPROPERTY('ProductVersion') AS [SQL Server Version],
    CASE compatibility_level
        WHEN 70 THEN 'SQL Server 7.0'
        WHEN 80 THEN 'SQL Server 2000'
        WHEN 90 THEN 'SQL Server 2005'
        WHEN 100 THEN 'SQL Server 2008/R2'
        WHEN 110 THEN 'SQL Server 2012'
        WHEN 120 THEN 'SQL Server 2014'
        WHEN 130 THEN 'SQL Server 2016'
        WHEN 140 THEN 'SQL Server 2017'
        WHEN 150 THEN 'SQL Server 2019'
        WHEN 160 THEN 'SQL Server 2022'
        ELSE 'Unknown version'
    END AS [Version Name]
FROM sys.databases
WHERE name = 'YourDatabaseName';

For backup files without restoring:

RESTORE HEADERONLY 
FROM DISK = 'C:\path\to\your\backup.bak';

-- The BackupSize and CompatibilityLevel columns reveal version info

For files not currently attached to SQL Server, examine the binary header:

-- PowerShell script to read first 100 bytes
$filePath = "C:\Data\Database.mdf"
$stream = [System.IO.File]::OpenRead($filePath)
$reader = New-Object System.IO.BinaryReader($stream)
$header = $reader.ReadBytes(100)
$stream.Close()

# Version signature appears at offset 0x60
$versionByte = $header[0x60]
"Detected SQL Server version signature: 0x{0:X2}" -f $versionByte

For comprehensive analysis, use DBCC PAGE command on attached databases:

DBCC TRACEON(3604);
DBCC PAGE(YourDatabaseName, 1, 0, 3);
DBCC TRACEOFF(3604);

• Compatibility level doesn't always match the creating server version
• Upgraded databases retain original version signatures
• Compressed backups may obscure version information

The SQL Server database files (MDF) and backup files (BAK) contain metadata in their headers that reveals version information. This information is stored in a structure called the DBFILEHEADER which includes the version number used when creating the database.

For BAK files, you can extract version information using:

RESTORE HEADERONLY 
FROM DISK = 'C:\path\to\your\backup.bak'

The result set will include columns like SoftwareVersionMajor and SoftwareVersionMinor that indicate SQL Server version.

Here's a C# example to read the version from an MDF file header:

using (FileStream fs = new FileStream(@"C:\data\database.mdf", FileMode.Open))
{
    byte[] buffer = new byte[8192];
    fs.Read(buffer, 0, 8192);
    
    // Get version from offset 0x120 in the header
    short versionMajor = BitConverter.ToInt16(buffer, 0x120);
    short versionMinor = BitConverter.ToInt16(buffer, 0x122);
    
    Console.WriteLine($"SQL Server version: {versionMajor}.{versionMinor}");
}

Here are common SQL Server version numbers found in file headers:

• SQL Server 2019: 15.x
• SQL Server 2017: 14.x
• SQL Server 2016: 13.x
• SQL Server 2014: 12.x
• SQL Server 2012: 11.x

If you can attach the database, running this command will show version information:

DBCC CHECKDB('YourDatabase') WITH NO_INFOMSGS;

Look for messages containing "Database version" in the output.

Remember these key points when working with database files:

• Always work on copies of production files
• Version detection might not work for very old SQL Server versions (pre-2005)
• File headers might be corrupted in some cases
• For encrypted databases, additional steps may be required

For quick version checks without attaching databases:

$filePath = "C:\Data\Database.mdf"
$header = New-Object byte[] 8192
$stream = [System.IO.File]::OpenRead($filePath)
$stream.Read($header, 0, 8192) | Out-Null
$stream.Close()

$major = [BitConverter]::ToInt16($header, 0x120)
$minor = [BitConverter]::ToInt16($header, 0x122)
Write-Host "SQL Server version: $major.$minor"