Sometimes our users make the mistake of restoring the wrong version of SQL Server to their database add-on. It’s really an honest mistake to overlook, especially if you’re a developer who works with different versions of SQL Server and is rushed for time.
The hard part for us as support staff is to decipher what the error message trapped actually means. Here’s the general error that is thrown when there is a mismatch in SQL Server version:
“The media family on device ‘Path to database file’ is incorrectly formed. SQL Server cannot process this media family.“
Not really indicative of the actual problem, is it? I’ve included a table which shows when this error is thrown:
| Database Backed Up From | Database Restored To |
| SQL Server 2012 | SQL Server 2005 |
| SQL Server 2012 | SQL Server 2008 |
| SQL Server 2012 | SQL Server 2008 R2 |
| SQL Server 2008 R2 | SQL Server 2005 |
| SQL Server 2008 | SQL Server 2005 |
An exception to this rule is when you try to restore a SQL Server 2008 R2 database to SQL Server 2008. This is the error thrown which is definitely a better message:
“The database was backed up on a server running version 10.50.1617. That version is incompatible with this server, which is running version 10.00.5828. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.”
Generally speaking, SQL Server databases are forward compatible (i.e. you can restore a lower version to a higher version) without any problems with the exception of SQL Server 2000. You cannot restore a SQL Server 2000 database directly to SQL Server 2012. It will throw this error:
“The database was backed up on a server running version 8.00.2305. That version is incompatible with this server, which is running version 11.00.3339. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.”
To restore a SQL Server 2000 database to SQL Server 2012, you will need to perform a 2 step process. You will need to restore it to SQL Server 2005, 2008, or SQL 2008 R2 first and then perform a backup. From that backup, you can restore it to SQL Server 2012.
You can always find out what version number of SQL Server that you are running by executing this T-SQL statement in SQL Server Management Studio:
SELECT @@VERSION
Or there are nifty tables around the Internet which you can reference:
http://blog.sqlauthority.com/2013/01/18/sql-server-a-list-of-various-sql-server-rtm-and-service-pack-number/
http://sqlserverbuilds.blogspot.com/
http://www.sqlteam.com/article/sql-server-versions
Now, if you want to restore a database from a higher version to a lower version, you can do so by generating a script. I won’t provide a step-by-step walk-through here as there are already many great guides on how to do this on the Internet:
http://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-server-database-to-a-lower-version/
http://www.sqlserverblogforum.com/2011/05/restoring-a-database-from-higher-version-to-lower-version-sql-server/
http://blog.sqlauthority.com/2012/12/25/sql-server-restoring-2012-database-to-2008-or-2005-version-and-2-other-most-asked-questions/