0

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?

5

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.

  • Is there any tool to convert old version sql query to sql server 2014? – user3783446 Jun 7 '16 at 17:28
  • 3
    @user3783446 I don't know of any tools that can convert a *= / =* 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. – Aaron Bertrand Jun 7 '16 at 17:34
1

Follow this article and use upgrade advisor.

  • Some of the queries are of compatibility level=80. ex. *= operator and starting CTE without using ; before it. So, these kind of queries are not working in sql server 2014. – user3783446 Jun 7 '16 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.