Is it possible to restore 2017 database to a 2016 server

If the 2017 database is running in 2016 compatibility mode?

Sorry, I don't have access to the environments right now to test this out.


Also, curious if anyone has upgraded to 2017 and experienced any issues. I've looked at the list of deprecated features and they seem pretty obscure. We have tested our application pretty thoroughly and it seems fine but I'm worried we might be missing something.

I would go this route.

  1. Don't upgrade but instead install a fresh new install on a separate server
  2. restore db from 2016 to this new server
  3. stand up a segregated environment in staging or qa/dev and a copy of your app, change connection strings
  4. test
  5. If all is good stop current prod server so nothing connects to it. stop application. do one final backup from prod, restore to new 2017 server
  6. point prod app to new server

do you have a lot of artifacts on the old server?

Thanks for the feedback. We are doing something similar. Since our databases are a combined 10TB. We have already done the backup and restore to the new server and are shipping tlogs to the new server. We will stop the application Saturday night, wait 15 minutes for the last tlogs to ship, restore the tlogs, and change the connection string.

My concern is that we will discover a problem an hour or two after the switch and was hopeful that we could have a rollback contingency just in case where we would backup and restore back to 2016. Since making the original post, one of my DBs attempted to do this and it was not possible unfortunately. Not having this rollback possibility makes me more nervous about the move but I don't think there is a way around it. I just hope we tested everything!

junk! Huge operation! so why not test that exact scenario trimmed down? so why not test that same exact scenario as you describe. in lab environment 2 servers on 2016 one 2017. is the data too big to do this fire drill? is this an external facing web site or internal?
and where are your testers? (I seldom test but if I do its in production :rofl:)

We have a pretty robust QA department (16 staff) and they have been working on this. We have had a few availability incidents recently and I can't afford to have anything go wrong. Having a robust rollback procedure is always something I like to have in place just in case but it looks like my options are limited in this case.

I agree 100%. sounds like you have done your DD. what does the senior leadership team say about the change control in this situation, have they not asked the question about contingency plans? or are you the senior leadership team?

My title is VP, Operations. I am part of the senior leadership team. Don't ask why I'm knee deep in SQL (I love it - that's why). The migration is not only from 2016 to 2017 but also from Standard Edition to Enterprise Edition. The primary motivation is DRP. We will be leveraging Always On. We could have, and perhaps should have, simply did the Edition migration and stuck with 2016. But the feeling is that we would like to move to 2017 at some point anyway and will have to confront this challenge at that point regardless. Saturday is fast approaching and we have already scheduled the downtime with clients. Never too late to back out but at this point we just need to continue to test the heck out of it.