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?

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

1 Answer 1

Reset to default
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.

Improve this answer
2
  • Apologies, we were missing an important aspect, OP updated.
    – Jonesome Reinstate Monica
    Commented Jun 6, 2017 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
    Commented Jun 6, 2017 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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