February 13, 2017 at 6:38 am
Hello, I took over a LOB app that has been running on 2003 server w/SQL 2000. Created a VM with 2012R2 and have SQL 2014 running. Backed up the 2000 databases but can't restore to 2014. Quick searchs showed that I need to stand up a 2008 SQL server, restore the 2000 dbs to that (as long as the 2000 dbs were running on SP4), then back them up on the 2008 server and restore to 2014. Do those steps sound correct? Is there an easier, more direct way? Any gotchas I need to be aware of? Thank you.
John
February 13, 2017 at 6:44 am
Beaux_Zeaux - Monday, February 13, 2017 6:38 AMHello, I took over a LOB app that has been running on 2003 server w/SQL 2000. Created a VM with 2012R2 and have SQL 2014 running. Backed up the 2000 databases but can't restore to 2014. Quick searchs showed that I need to stand up a 2008 SQL server, restore the 2000 dbs to that (as long as the 2000 dbs were running on SP4), then back them up on the 2008 server and restore to 2014. Do those steps sound correct? Is there an easier, more direct way? Any gotchas I need to be aware of? Thank you.John
2005 or 2008/2008 R2 will work as the intermediate step, other than that, the steps are correct.
I would recommend going to SQL 2016 instead of 2014. The amount of work and process is the same, and 2016 is a very nice version.
You MUST test carefully before you upgrade the production DB. There have been a lot of changes over 14 years, both in what's valid and in performance. You need to find and fix those problems before the users run into them
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
February 13, 2017 at 7:01 am
On the note of "gotchas", as Gail pointed out there are a lot of changes from 2000 to 2016. MSDN does list all the Breaking changes from version to version, of which you may run in to many (or few). Unfortunately, if you're unfamiliar with the databases, then this could be a somewhat slow task, with a lot of testing.
You can find the details of them here:
2005: https://msdn.microsoft.com/en-us/library/ms143179(sql.90).aspx
2008: https://msdn.microsoft.com/library/ms143179(v=sql.100)
2012: https://msdn.microsoft.com/library/ms143179(v=sql.110)
2014: https://msdn.microsoft.com/library/ms143179(v=sql.120)
And, if you need it:
2016: https://msdn.microsoft.com/en-us/library/ms143179.aspx
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 13, 2017 at 7:38 am
GilaMonster - Monday, February 13, 2017 6:44 AMBeaux_Zeaux - Monday, February 13, 2017 6:38 AMHello, I took over a LOB app that has been running on 2003 server w/SQL 2000. Created a VM with 2012R2 and have SQL 2014 running. Backed up the 2000 databases but can't restore to 2014. Quick searchs showed that I need to stand up a 2008 SQL server, restore the 2000 dbs to that (as long as the 2000 dbs were running on SP4), then back them up on the 2008 server and restore to 2014. Do those steps sound correct? Is there an easier, more direct way? Any gotchas I need to be aware of? Thank you.John
2005 or 2008/2008 R2 will work as the intermediate step, other than that, the steps are correct.
I would recommend going to SQL 2016 instead of 2014. The amount of work and process is the same, and 2016 is a very nice version.You MUST test carefully before you upgrade the production DB. There have been a lot of changes over 14 years, both in what's valid and in performance. You need to find and fix those problems before the users run into them
ok, thanks for the info. LOB vendor recommended 2014 as the supported platform so that is why it was chosen. However, I will certain spin up a 2016 when time permits and put it through the paces.
John
February 13, 2017 at 7:41 am
Thom A - Monday, February 13, 2017 7:01 AMOn the note of "gotchas", as Gail pointed out there are a lot of changes from 2000 to 2016. MSDN does list all the Breaking changes from version to version, of which you may run in to many (or few). Unfortunately, if you're unfamiliar with the databases, then this could be a somewhat slow task, with a lot of testing.You can find the details of them here:
2005: https://msdn.microsoft.com/en-us/library/ms143179(sql.90).aspx
2008: https://msdn.microsoft.com/library/ms143179(v=sql.100)
2012: https://msdn.microsoft.com/library/ms143179(v=sql.110)
2014: https://msdn.microsoft.com/library/ms143179(v=sql.120)
And, if you need it:
2016: https://msdn.microsoft.com/en-us/library/ms143179.aspx
Will certainly look at the links and wouldn't doubt there have been a 'few' changes since 2000. I will push back on the Vendor regarding these details before going live in production, to make sure things work smoothly. Thank you.
John
February 13, 2017 at 2:15 pm
I found out the SQL 2000 db is on SP3. Do you know if in fact I need to be on SP4 in order to do the backup and restore chain to get it finally in 2014? Read a few articles about needing to be on SP4 but no reasons given why.
February 13, 2017 at 2:52 pm
Beaux_Zeaux - Monday, February 13, 2017 2:15 PMI found out the SQL 2000 db is on SP3. Do you know if in fact I need to be on SP4 in order to do the backup and restore chain to get it finally in 2014? Read a few articles about needing to be on SP4 but no reasons given why.
Do you have a TEST instance to run this all through? If so, I'd do the upgrade process entirely in a test environment to ensure nothing goes bonkers.
Since SQL 2000 is not a supported version anymore, what I'd recommend is:
spin up a brand new SQL 2000 DB, install SP3 on it.
restore your database onto the new 2000 one. Install SP4 to make sure it is as up to date as you can get it and then do your upgrades to 2014, testing inbetween each one as carefully as you can. Any problems that crop up, you then know what step it busted on and can go from there.
I would not recommend doing the upgrade on the live system just in case it breaks things you were not expecting. I would also make sure you are on the latest SP before you go to the next major version. I don't think this is "required", but it is recommended.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 13, 2017 at 5:46 pm
bmg002 - Monday, February 13, 2017 2:52 PMBeaux_Zeaux - Monday, February 13, 2017 2:15 PMI found out the SQL 2000 db is on SP3. Do you know if in fact I need to be on SP4 in order to do the backup and restore chain to get it finally in 2014? Read a few articles about needing to be on SP4 but no reasons given why.Do you have a TEST instance to run this all through? If so, I'd do the upgrade process entirely in a test environment to ensure nothing goes bonkers.
Since SQL 2000 is not a supported version anymore, what I'd recommend is:
spin up a brand new SQL 2000 DB, install SP3 on it.
restore your database onto the new 2000 one. Install SP4 to make sure it is as up to date as you can get it and then do your upgrades to 2014, testing inbetween each one as carefully as you can. Any problems that crop up, you then know what step it busted on and can go from there.
I would not recommend doing the upgrade on the live system just in case it breaks things you were not expecting. I would also make sure you are on the latest SP before you go to the next major version. I don't think this is "required", but it is recommended.
I do not have a test instance, but was thinking the same thing. I'm in the middle of doing a P2V (existing 2000 is a physical 2k3 server) into a test environment. If I didn't have to go to SP4, I could have skipped this step and the upgrade process to SP4 and just stand up a 2005 or 2008 SQL box to restore a backup to and see how the process runs out. I'll see how things go from soup to nuts. Thank you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply