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/