Backups of a higher version cannot be restored on a lower version of SQL Server

SQL Server has always allowed a database backup of a lower version to be restored on a higher version where as the opposite is not allowed. That is, if you have backup of a higher version, that cannot be restored on a lower version. We shall see this in this blog post.

This is true even for SQL Server 2008 R2 and SQL Server 2008, even though they both are from the same Major Version SQL 10, there is a change in the minor version SQL Server 2008 is 10.0.xxxx, SQL Server 2008 R2 is 10.50.xxxx

First we create a database by using the simple command “Create Database databasename”, in this case the database name is 2008R2db. In the query I included @@version to show what SQL Instance I am creating this database on.. When the query is run, the database is created and also the SQL Server Version Information…

Next I backup the newly created database to C:\2008R2db.bak using backup database command in T-SQL. The below is the screenshot when I ran the backup command.

Next I login to a SQL Server 2008 Instance in this case, which is on the same machine, and open a new query and run the restore database T-SQL command trying to restore from the backup file that was just created on 2008 R2 instance. 

As mentioned earlier, the restore command fails, giving the error details in detail that the database versions do not match and what SQL Server version the backup was taken on..