1

We are chasing an odd problem.

We see a significant perf delta when restoring via SSMS, but do NOT see the delta when restoring via TSQL.

When we restore, using SSMS, on SQL 2016 from a DB created on a SQL 2014 instance, the restore is really really slow.

But, on the same SQL 2016 DB server, when we restore from a SQL 2016 instance, again in SSMS, the restore is super fast.

The SQL 2016 system is a super fast system, all-SSD, etc. It should always be fast.

To address, we applied all OS and SQL patches and updates to the target system. Problem was not changed.

Why would restoring a SQL 2014 DB be so slow?

  • 1
    Because when you restore database between different version the Engine make the conversion in the system files make slower. – Krismorte Jun 5 '17 at 23:07
  • Also may want to check this: dba.stackexchange.com/questions/158506/… – Jeff A Jun 5 '17 at 23:24
  • Have you generated the script that the SSMS version is using and compared that with your T-SQL script? – swasheck Jun 6 '17 at 20:13
3

The restore from 2014 is an upgrade, not just a restore. You should actually see this in the messages window, assuming you are using T-SQL to do the restore. You don't mention what Fast and Slow are, or the differences, such as 5 mins vs. 5 hours, so we can't add much to this.

  • Apologies, we were missing an important aspect, OP updated. – samsmith Jun 6 '17 at 19:14
  • "Really fast" and "super slow" don't mean anything. T-SQL in the SSMS query window vs. SSMS GUI? No matter how you are doing it, you are still upgrading the 2014 to 2016. Database size? – Kevin3NF Jun 6 '17 at 19:19

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.