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:
It tells you that backup was taken on SQL Server 2012 SP3, while you run SQL Server 2012 SP2. Once you install SP3 for SQL Server script output will change to this:
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:
2 Comments
This is quite neat script, I’ve used this as a backup for my own script.
However you are querying the SQL server three times on the lines 5-7 which is quite unnessesary. You could have used something like this:
$BakVer = Invoke-Sqlcmd -Query $VerDetectQuery |
Select-Object -Property SoftwareVersionMajor, SoftwareVersionMinor, SoftwareVersionBuild |
%{“{0}.{1}.{2}.0” -f $_.SoftwareVersionMajor,$_.SoftwareVersionMinor,$_.SoftwareVersionBuild }
Thanks for the feedback, will look into updating my script later 🙂