December 14, 2016 at 6:13 am
Our vendor has a new version of their application that we will be upgrading to in the coming year. Today the database is in SQL 2008 (NOT SQL 2008R2). They say they support SQL2014 and 2016. My question is can I backup/restore a SQL2008 database into SQL 2016? From the searches I have done I think that is possible.... 2005 no but I think 2008 you can.
Anyone know for sure that this is possible?
December 14, 2016 at 6:21 am
Markus (12/14/2016)
Our vendor has a new version of their application that we will be upgrading to in the coming year. Today the database is in SQL 2008 (NOT SQL 2008R2). They say they support SQL2014 and 2016. My question is can I backup/restore a SQL2008 database into SQL 2016? From the searches I have done I think that is possible.... 2005 no but I think 2008 you can.Anyone know for sure that this is possible?
The easiest way to find out is to stand up a test VM and install SQL 2016 Developer Edition and then try the restore and see if it works. Any time you have to do something that "hasn't been done before by your company", it's ALWAYS a good idea to test it and see that it works before committing to living with the results. As the cardinality estimator changed in SQL 2014, there are likely to be queries that ran just fine in SQL 2008 that will suddenly not perform very well under SQL 2016, so you'll want to research mitigation methods for that as well.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 14, 2016 at 6:27 am
sgmunson (12/14/2016)
Markus (12/14/2016)
Our vendor has a new version of their application that we will be upgrading to in the coming year. Today the database is in SQL 2008 (NOT SQL 2008R2). They say they support SQL2014 and 2016. My question is can I backup/restore a SQL2008 database into SQL 2016? From the searches I have done I think that is possible.... 2005 no but I think 2008 you can.Anyone know for sure that this is possible?
The easiest way to find out is to stand up a test VM and install SQL 2016 Developer Edition and then try the restore and see if it works. Any time you have to do something that "hasn't been done before by your company", it's ALWAYS a good idea to test it and see that it works before committing to living with the results. As the cardinality estimator changed in SQL 2014, there are likely to be queries that ran just fine in SQL 2008 that will suddenly not perform very well under SQL 2016, so you'll want to research mitigation methods for that as well.
Understood. I know about the cardinality change in SQL 2014 as we have upgrading many dbs to SQL 2014 already. This is a very small app with minimal db usage so I don't think it will be an issue but it will be fully tested in a test environment. I was simply asking if Microsoft supports SQL 2008 to 2016 backup/restore is all.
December 14, 2016 at 6:32 am
Markus (12/14/2016)
sgmunson (12/14/2016)
Markus (12/14/2016)
Our vendor has a new version of their application that we will be upgrading to in the coming year. Today the database is in SQL 2008 (NOT SQL 2008R2). They say they support SQL2014 and 2016. My question is can I backup/restore a SQL2008 database into SQL 2016? From the searches I have done I think that is possible.... 2005 no but I think 2008 you can.Anyone know for sure that this is possible?
The easiest way to find out is to stand up a test VM and install SQL 2016 Developer Edition and then try the restore and see if it works. Any time you have to do something that "hasn't been done before by your company", it's ALWAYS a good idea to test it and see that it works before committing to living with the results. As the cardinality estimator changed in SQL 2014, there are likely to be queries that ran just fine in SQL 2008 that will suddenly not perform very well under SQL 2016, so you'll want to research mitigation methods for that as well.
Understood. I know about the cardinality change in SQL 2014 as we have upgrading many dbs to SQL 2014 already. This is a very small app with minimal db usage so I don't think it will be an issue but it will be fully tested in a test environment. I was simply asking if Microsoft supports SQL 2008 to 2016 backup/restore is all.
I don't know that answer, but it might actually be faster to test it than to find someone that does know the answer. And then, you'll not only know the answer, but you can "prove it", and you can let the rest of the SQL community be aware of it. My guess is, the moment you try to do the restore, if SQL doesn't support it, it will barf up an error, and probably tell you directly.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 14, 2016 at 6:34 am
Markus (12/14/2016)
My question is can I backup/restore a SQL2008 database into SQL 2016?
Yes
From the searches I have done I think that is possible.... 2005 no but I think 2008 you can.
2005, yes. 2008, yes, Anything past 2008, yes.
SQL 2000, no.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 14, 2016 at 6:38 am
Thank you for your input everyone.
December 14, 2016 at 6:38 am
Well... I guess I should Google something I don't know the answer to, as I found information that is suggestive that even SQL 2005 is supported. See the following link:
https://msdn.microsoft.com/en-us/library/ms177429.aspx
On that page, you'll find the following text:
"If you restore an older version database to SQL Server 2016, that database will automatially upgrade to SQL Server 2016.
Typically, the database becomes available immediately. However, if a SQL Server 2005 database has full-text indexes, the upgrade process either imports, resets, or rebuilds the indexes, depending on the setting of the Full-Text Upgrade Option server property."
This text suggests that SQL 2008 should not be a problem, but even so, testing is warranted. Not everything that Microsoft says is necessarily accurate...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 15, 2016 at 2:19 am
sgmunson (12/14/2016)
Well... I guess I should Google something I don't know the answer to, as I found information that is suggestive that even SQL 2005 is supported.
Yes it is supported, see my previous reply.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2016 at 4:36 am
Thanks everyone. I guess I didn't search enough myself. I guess I am surprised that they still support SQL 2005 into 2016... good for those still on SQL2005.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply