I have database in SQL Server 2008 R2. I took a backup of this database and restored that on a SQL Server 2014 server. However, some of the database queries are not looking compatible to the new version.
For example *= operator is throwing errors in SQL Server 2014.
I don't want to change each query where I get issue. Is there any way to upgrade the older version of SQL Server backup to work with the newer version of database?
You cannot restore a SQL Server 2000 database onto a SQL Server 2014 instance. You need to restore to SQL Server 2008 or 2008 R2 instance first, then take a backup of that database which you can then restore to 2014.
There a lot of features from SQL Server 2000 that have been discontinued in SQL Server 2014. See this list for the details about what features have been discontinued, and what to use as replacements.
Unfortunately, you have no choice but to re-code queries that use the ancient =* or *= join syntax.
SQLMag has a great article on how to do the migration.
*= / =* query to a proper join, insert semi-colons in all the right places accurately, etc. There are several tools that can help identify those, like upgrade advisor, best practices analyzer, SQLCop, etc (and I tried to point out a bunch of actual behavior changes in this answer). But moving from 2000 will require some actual work.
Commented
Jun 7, 2016 at 17:34
Follow this article and use upgrade advisor.
*=queries on SQL Server 2014. Those require 80 compatibility mode (see the second item in the table here), and SQL Server 2014 does not support 80 compatibility mode. So, you need to either not upgrade to SQL Server 2014, or fix your queries. There is no magic "ignore that this syntax no longer works" switch.