SQL Server database restoration failed - Version Conflict

Question

I am attempting to restore SQL Server database backups from *.bak files and whenever I try a restore via Microsoft SQL Server Management Studio I get the following error:

Restore of database 'ALA_PPD' failed.
(Microsoft.SqlServer.Management.RelationalEngineTasks)

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The database was backed up on a server running version 12.00.2000. 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)

I realize that the versions of servers where different, my question is there a way to convert these 12.00.2000 *.bak files to be usable in version 11.00.2100?

I know that I could install ver. 11.00.2100 and successfully restore but I have databases currently in my 12.00.2000 installation that cannot be removed.

Any suggestions would be greatly appreciated. Sorry if it is a noob question.

Answer 1

You CANNOT do this - you cannot attach/detach or backup/restore a database from a newer version (SQL Server 2014 - v12.0) of SQL Server down to an older version (SQL Server 2012 - v11.0) - the internal file structures are just too different to support backwards compatibility.

You can either get around this problem by

• using the same version of SQL Server on all your machines - then you can easily backup/restore databases between instances

• otherwise you can create the database scripts for both structure (tables, view, stored procedures etc.) and for contents (the actual data contained in the tables) either in SQL Server Management Studio (Tasks > Generate Scripts) or using a third-party tool

• or you can use a third-party tool like Red-Gate's SQL Compare and SQL Data Compare to do "diffing" between your source and target, generate update scripts from those differences, and then execute those scripts on the target platform; this works across different SQL Server versions.