Find the sql version of a Database Backup file / MDF file
Determine the version of SQL Server that was used to create an MDF or BAK file?
You can also check the DatabaseVersion value by running the query mentioned by Hilary, and you can refer to the following mappings to get SQL Server version where backup performed.
SQL Server 7.0 - 515SQL Server 2000 - 539
SQL Server 2005 611/612
SQL Server 2008 655
Find the sql version of a Database Backup file Query:
RESTORE HEADERONLY FROM DISK = N'E:\SRGITS\database\FM2008.bak' WITH NOUNLOAD;
Output:
Output:
BackupName BackupDescription BackupType ExpirationDate Compressed Position DeviceType UserName ServerName DatabaseName DatabaseVersion DatabaseCreationDate BackupSize FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN BackupStartDate BackupFinishDate SortOrder CodePage UnicodeLocaleId UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName Flags BindingID RecoveryForkID Collation FamilyGUID HasBulkLoggedData IsSnapshot IsReadOnly IsSingleUser HasBackupChecksums IsDamaged BeginsLogChain HasIncompleteMetaData IsForceOffline IsCopyOnly FirstRecoveryForkID ForkPointLSN RecoveryModel DifferentialBaseLSN DifferentialBaseGUID BackupTypeDescription BackupSetGUID CompressedBackupSize
-------------------------------------------------------------------------------------------------------------------------------- ------------------
FM2008 backup NULL 1 NULL 0 1 2 VIGNESH\Administrator VIGNESH FM2008 539 2008-05-26 17:09:34.000 863232 10000000002400001 10000000002800001 10000000002400003 0 2008-05-26 17:10:51.000 2008-05-26 17:10:52.000 52 228 1033 196609 80 4608 8 0 2039 VIGNESH 0 F3F1E4EF-54D6-45FF-97D4-344A60A30E3D A0CA4F57-5867-4B90-9B4F-FB57A04E9E9A SQL_Latin1_General_CP1_CI_AS A0CA4F57-5867-4B90-9B4F-FB57A04E9E9A 0 0 0 0 0 0 0 0 0 0 A0CA4F57-5867-4B90-9B4F-FB57A04E9E9A NULL NULL NULL NULL Database 1679AA78-A142-4392-A269-992F8FF87787 863232
(1 row(s) affected)
Note: 539 => SQL Server 2000
Find the sql version of a Database MDF file
Query:
dbcc checkprimaryfile ('E:\SRGITS\database\FM2008.mdf', 2)
Output:
property value
-------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Database name FM2008
Database version 611
Collation 872468488
(3 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note: 611 => SQL Server 2005