Quite unpleasant thing about MS SQL Server database backup files it that you can’t restore them on an older version of SQL Server (it seems that even if you really want to create such BAK file it is not possible), moreover this is valid not only for major versions but also for things like R2, meaning you can’t restore BAK file created in SQL Server 2008 R2 on SQL Server 2008 Server (non-R2).
Just to save my time trying to restore BAK files against wrong versions of SQL Server I created this script:
It will allow you to retrieve SQL version from BAK file headers and compare it with your server SQL version. Specify path to your BAK file and check the output – if BAK file SQL version is newer that your server version then BAK file can’t be restored on this server. Sample script output can be found below:
Once two numbers match (or your SQL Server version number is higher than BAK file SQL version number) you are ready for backup restore 🙂 I’ve also added to this script comparison and output of its result, along with prompt for BAK file name and location: