SQL Server Restore Database fail because of incompatible backup and restore server version
TITLE: Microsoft SQL Server Management Studio
Restore of database 'SQL Server database name' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
System.Data.SqlClient.SqlError: The database was backed up on a server running version 8.00.0534. That version is incompatible with this server, which is running version 11.00.2100. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&LinkId=20476
SQL Server database administrators especially running a database server farm with different versions of Microsoft SQL Server database platform tools might experience the above SQL Server database restore error.
Restore of database '[SQL Server database name]' failed.
If a database administrator or database developer take backup of a database on a server which is running a previous version of Microsoft SQL Server, and then tries to restore database backup on SQL Server instance that is different than the instance that backup is taken, the related SQL Server engine error might occur.
The error is related with incompatible SQL Server versions of the source database instance and the target database instance of the backup and restore operation.
One cause of this incompatible SQL Server instance versions error resulting to SQL database restore fail is restoring database backup from a source server which has higher version than the target server.
In other words, when the backup server is let's say SQL Server 2008 R2 but the restore database operation takes place on SQL Server 2008 then database administrator will fail to restore backup because of the same incompatibility issue.
You can never restore your backup on downwards SQL Server version.
Following database restore error is a sample case for restore fail because of downgrading problem.
The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.2573. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
The displayed database restore error also gives the solution to the database restore operator.
The SQL Server database restore operator or DBA should restore database on a SQL Server instance that supports the database backup.
This is simply the same SQL Server version with the database backup source.
Generally Microsoft SQL Server 2000 installed instance databases cause this database restore error when you try to restore on a database server running SQL Server 2012 version.
Because Microsoft SQL Server 2012 only supports 3 previous SQL Server versions (SQL Server 2008 R2, SQL Server 2008 and SQL Server 2005).
In that case it might be an alternative solution to upgrade the SQL Server 2000 instance to SQL Server 2005 and then other SQL Server versions.
This in-place upgrade will upgrade source database compatibility versions too.
Any how for upgrading your database backup or downgrading the backup file on an incompatible server version, scripting database schema and data might be a solution.
It is not easy, but it can be a solution when database administrators stuck and finding a solution for failed database restore operations.
Please refer to Generate and Publish Scripts Wizard in SQL Server 2012 for downgrade operations.
If you follow the steps in SQL Server tutorial Script Data in MS SQL Server 2012 using Generate and Publish Scripts Wizard, you can generate SQL scripts of database objects and table data in SQL Server 2012 to restore this schema and data on a database on a server running previous SQL Server versions.
If your incompatible backup is from SQL Server 2008 or SQL Server 2008 R2 versions, you can use SQL Server Scripts Wizard for generating schema script and script data in database tables.
Please refer to SQL tutorial Script Data in MS SQL Server 2008 Database Tables using Generate SQL Server Script Wizard for this task.