Identify SQL Server Version From a Backup File

To identify SQL Server version from a backup file, run RESTORE HEADERONLY and read two columns: DatabaseVersion and SoftwareVersionMajor. The command reads the header only. It doesn’t restore anything, and it reads only the start of the file.

Why the File Name Isn’t Enough

Backup folders collect files with look-alike names. A team with four versions can end up with four DevDepartment backups, each stamped with a time. The name doesn’t say which server made the file, and the version decides where the backup can be restored.

The header of the file holds the answer. It lists the server that made the backup, the login, the database, and the version numbers.

Build a Demo Backup

The demo creates a database named BackupVersionDemo and a copy-only full backup of it. A copy-only backup doesn’t disturb a real backup chain, and CHECKSUM adds a page check. Change the folder to one that exists on your test server.

IF DB_ID(N'BackupVersionDemo') IS NULL CREATE DATABASE BackupVersionDemo;
GO
BACKUP DATABASE BackupVersionDemo TO DISK = N'D:\data\BackupVersionDemo.bak' WITH INIT, COPY_ONLY, CHECKSUM;

Read the Header

One statement reads the header. It returns a single row with 59 columns on SQL Server 2025.

RESTORE HEADERONLY FROM DISK = N'D:\data\BackupVersionDemo.bak';

A few columns answer most questions. ServerName and UserName say where the backup came from and who ran it. DatabaseName names the database. DatabaseVersion is the internal version number of the database. SoftwareVersionMajor is the major version of the product that wrote the file. CompatibilityLevel is the compatibility level of the database inside the file.

Two neighbors of this command help before a restore. RESTORE FILELISTONLY lists the files inside the backup. You need their logical names for a MOVE clause. RESTORE VERIFYONLY checks that SQL Server can read the whole backup. Run both on a file you don’t know.

Map the Numbers to a Product

SoftwareVersionMajor gives the product directly. The internal DatabaseVersion gives more detail, and it’s the number that decides whether a restore works.

The temporary table result below shows the 998 and the 17 from the demo backup. The other numbers are documented values. Match the header against the table, and you know which version wrote the file.

Put the Header Into a Query

A grid is fine for one file. A script that checks many files needs the header in a table. The statement can’t be used as a subquery. The script creates a temporary table with the same columns and fills it with INSERT and EXEC.

CREATE TABLE #BackupHeader (
    BackupName nvarchar(128), BackupDescription nvarchar(255), BackupType smallint, ExpirationDate datetime, Compressed bit, Position smallint, DeviceType tinyint,
    UserName nvarchar(128), ServerName nvarchar(128), DatabaseName nvarchar(128), DatabaseVersion int, DatabaseCreationDate datetime, BackupSize numeric(20,0),
    FirstLSN numeric(25,0), LastLSN numeric(25,0), CheckpointLSN numeric(25,0), DatabaseBackupLSN numeric(25,0), BackupStartDate datetime, BackupFinishDate datetime,
    SortOrder smallint, [CodePage] smallint, UnicodeLocaleId int, UnicodeComparisonStyle int, CompatibilityLevel tinyint, SoftwareVendorId int, SoftwareVersionMajor int,
    SoftwareVersionMinor int, SoftwareVersionBuild int, MachineName nvarchar(128), Flags int, BindingID uniqueidentifier, RecoveryForkID uniqueidentifier, Collation nvarchar(128),
    FamilyGUID uniqueidentifier, HasBulkLoggedData bit, IsSnapshot bit, IsReadOnly bit, IsSingleUser bit, HasBackupChecksums bit, IsDamaged bit, BeginsLogChain bit,
    HasIncompleteMetaData bit, IsForceOffline bit, IsCopyOnly bit, FirstRecoveryForkID uniqueidentifier, ForkPointLSN numeric(25,0), RecoveryModel nvarchar(60),
    DifferentialBaseLSN numeric(25,0), DifferentialBaseGUID uniqueidentifier, BackupTypeDescription nvarchar(60), BackupSetGUID uniqueidentifier, CompressedBackupSize bigint,
    Containment tinyint, KeyAlgorithm nvarchar(32), EncryptorThumbprint varbinary(20), EncryptorType nvarchar(32), LastValidRestoreTime datetime, TimeZone nvarchar(32), CompressionAlgorithm nvarchar(32)
);
INSERT INTO #BackupHeader EXEC (N'RESTORE HEADERONLY FROM DISK = N''D:\data\BackupVersionDemo.bak''');
SELECT DatabaseName, DatabaseVersion, CompatibilityLevel, SoftwareVersionMajor, SoftwareVersionMinor, BackupTypeDescription, RecoveryModel FROM #BackupHeader;

This table definition matches the header of SQL Server 2025. Older versions return fewer columns, and the INSERT then fails with a message that the column count doesn’t match. Run RESTORE HEADERONLY once on your version, and cut the column list to what comes back.

Compare the Backup With Your Server

A backup restores to the same version or a newer one. It never restores to an older one. The server’s own internal version is available through DATABASEPROPERTYEX on the master database. Compare it with the header value. That is how you identify SQL Server version limits for a restore.

SELECT h.DatabaseVersion AS BackupVersion,
       CAST(DATABASEPROPERTYEX(N'master', N'Version') AS int) AS ServerVersion,
       CASE WHEN h.DatabaseVersion <= CAST(DATABASEPROPERTYEX(N'master', N'Version') AS int) THEN N'Can be restored here' ELSE N'Needs a newer server' END AS Verdict
FROM #BackupHeader AS h;

A backup made on this server always passes, because the two versions match. The check earns its keep when the file comes from somewhere else.

When the Header Won’t Read

An older server can fail on the header of a newer backup. The message says that the media family on the device is incorrectly formed, and SQL Server can’t process it. Nothing is wrong with the file. Read the header on a newer server instead. Without a newer server, install a free Express or Developer edition of the newest version on any machine. Use it only to read headers. A restore of a newer backup on an older server fails with Msg 3169, which names both versions.

Is It Easier to Restore It?

You could argue that a restore answers every question. Restore the file to the newest server and look. That costs time and disk space for a large database. It also has no way back. A database restored on a newer version can’t return to an older one.

Reading the header changes nothing and costs almost no disk space. I read it first.

Make the check a routine before every restore. Read the header, compare the versions, list the files, and verify the backup. That is four statements and no restore. Write the result in the ticket, so the next person sees which version produced the file.

What to Remember

Identify SQL Server version from the header before you restore. SoftwareVersionMajor names the product, and DatabaseVersion decides whether the restore can work. Remove the demo and its backup file when you finish.

DROP TABLE #BackupHeader;
GO
USE master;
GO
ALTER DATABASE BackupVersionDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE BackupVersionDemo;
GO
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'BackupVersionDemo';

The last statement removes the demo’s backup history from msdb. The backup file stays on disk. The next block is PowerShell, not T-SQL, and it deletes exactly that file.

Remove-Item 'D:\data\BackupVersionDemo.bak'

A backup file is not a pile of data, it is a letter with the sender’s version on the envelope.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.