Is it possible to determine the default Collation and server version of a database without a full restore?

I work for a company who regularly receives large SQL database backups from clients for support purposes. We support multiple SQL versions and collations, but currently have to manually confirm our client's SQL versions before restoring to the correct SQL instance. I am wondering if there is a way to automate this process.

I am looking to:

• Find the SQL Server version a database was backed up from (we get 2008 R2 as well as 2014 and 2017)

• Find the database's default Collation

Without requiring a full restore to a potentially incorrect SQL instance, which takes a significant amount of time due to their size. (maybe a piecemeal restore?)

You can get some info by inspecting a backup file using the RESTORE HEADERONLY command. The linked documentation explains what all of the result set fields are and mean, but the ones you are looking for should be:

• [SoftwareVersionMajor]

• 8 = SQL Server 2000

• 9 = SQL Server 2005

• 10 = SQL Server 2008 or 2008 R2 (see [SoftwareVersionMinor] for distinction)

• 11 = SQL Server 2012

• 12 = SQL Server 2014

• 13 = SQL Server 2016

• 14 = SQL Server 2017

• 15 = SQL Server 2019

• If you might be getting SQL Server 2008 R2 backups, then you will also need [SoftwareVersionMinor] as this value should be "50" for that version, else "00" for all others (so far)

• [Collation]

For example:

RESTORE HEADERONLY FROM DISK =
N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\master.bak'


That will return a result set. If you are executing this from app code, it should be fairly easy to grab those specific fields. If you are executing this from T-SQL, and you need to act on it programmatically, then you will need to:

• create a local temporary table with all possible result set fields from that command

• execute the RESTORE HEADERONLY command within an INSERT INTO ... EXEC(...) construct:

INSERT INTO #TempBackupInfo
EXEC(N'RESTORE HEADERONLY FROM DISK =
N''C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\master.bak'';');


• select the fields in question from that local temp table

StackExchange Database Administrators Q#256623, answer score: 18