MS SQL 2000 to 2005 Database Migration

dman

dman

Perch
I'm looking for some guidance on how to migrate several MS SQL 2000 DBs to MS SQL 2005. I first posted about this two years ago and I'm now under pressure to get this done ASAP. I have hosting plans with DBs on each version of MS SQL but I'm unclear how to complete the migration. Does anyone have advice or instructions on how to migrate between MS SQL versions at Jodo?

Thanks in advance for any help.
 
We can simply move the DB for you if you wish, just let us know. It cannot move within the Hsphere however. We called off the 2000 to 2008 R2 updates after the second one totally screwed up due to an unknown, and then hotfixed error. I think we'll probably try again at some point after hsphere is upgraded. I think I will do it differently however, by doing it as a virtual machine export, and migrate to new node and upgrade, this was fall back is as simple as turning on the old machine if needed to rollback again like that last failure.
 
Hey Stephen,

Thanks for the reply! Can the DBs be copied from 2000 to 2005 so that I can still fall back to the 2000 version if the 2005 version has issues?

Not sure I understand what you mean by "it cannot be moved within Hsphere". I have basic DB set up in 2005 and would like to copy the 2000 DB there. When I talked to the chat rep they thought I could restore a backup from 2000 to 2005 but I couldn't save a backup up locally with MS SMS so they told me to submit a ticket. I submitted a ticket (DHP-85345-360) and they said I can't restore from 2000 to 2005. I'm still a bit confused. Thanks!
 
We can restore from 2000 to 2005 (not the other way), but you will NOT be able to manage it from the control panel.
 
Ok, I think I understand. So will this restrict me from creating DB users via the control panel? What level of management is lost? Thanks!
 
dman said:
Ok, I think I understand. So will this restrict me from creating DB users via the control panel? What level of management is lost? Thanks!
Click to expand...
Correct, creating users or changing size, and then you need to remember the new server it is on as the listed server on the control panel will be incorrect and make confusion, it has happened many times before (confusion in sql server due to move)

Techs can't know all the history in one glance, so that stuff happens.
 
Hmmm... this may be an issue because I really need to be able to manage the DB users and passwords.... Would it be possible to do some type of hack where I request a restore from 2000 to 2005 and then replicate/copy the newly restored DB to another that was created and can be managed by HSphere?

Alternately, I know I can generate the scripts to recreate the DBs on 2005 and then import data via MS SMS but it will take a lot of manual work. Do you have any suggestions or any advice on an alternate option for migrating the DB that would be less time consuming and still provide management access via HSphere? Any concerns I should be aware off if I go with the alternate option?

Thanks again!
 
1. This is how we recommend if you need to be able to control things in hsphere. If you have another user linux/windows etc that isn't otherwise using a DB at all, should be able to make a new SQL and it will go to the SQL 2008 server.
 
Hey Stephen,

I do have other accounts under my reseller plans with no DBs. I created new DB logins with these accounts and one uses 2005 and the other uses 2008. So I have two, basically fresh DBs, one on 2005 and one on 2008 available. But, I'm not sure I fully understand your recommendation.

Can I restore/copy the 2000 DBs to 2008 and still be able to acces it in Hsphere? Or are you recommending rebuilding all the DBs manually in 2008? Or could I restore to 2005 and then more easily copy to 2008 from 2005? The one issue is that some of these DBs work with an external 2005 SQL server so I wouldn't be able to use some of the 2008 function or scripting improvements. Please clarify your recommendation. Thanks!
 
ok, we can restore a SQL 2000 backup to 2005 or 2008. We can take your SQL 2000 DB, and move it, but you will need to tell us where to move it after you make a new DB in a different control panel account's own login if you need to control the users and all there. There is no need for manual move of data, we got that with backup and restore, we just need to know what DB you wish us to restore it to, so make a new DB in any account you wish on your side, make sure it is on MSSQL10/11/12 for SQL 2005, or MSSQL13 for SQL 2008, and from there we can handle the rest.
 
Hey Stephen,

Nice! That sounds like it will work well and save me a lot of migration pain. I'll send you a private message with the details and let me know if I should submit a ticket as well or instead. Thanks a lot!
 
A ticket would be best, I am trying to let the techs handle everything these days, even escalations I send to them and work out directly with and through them I don't reply tickets but it doesn't mean my influence is not directly there :)
 
OK, I'll send a ticket instead and let you know the ticket number in a reply here. Will the tech staff know what I am asking for? Are there specific instructions I should include? Thanks!
 
Hey Stephen,

Ok, I submitted it all in a ticket (DHP-85345-360). They suggested they would restore from the backup files they sent me earlier this week but I updated some Sprocs and users may have updated data today so the data would be stale. I requested they use new backup files from today so I hope they understood. Thanks again for your help!
 
Awesome... Thank to everyone at Jodo for the help and quick responses. Great support!

So, everything is looking good with the SQl 2000 databases that were restored to SQL 2005 but I have one issue that I'm not sure how to solve. All the DB objects contain the old user name in the schema for example: oldusername.tblName. Is there some way to easily update all the objects to use the new schema for the 2005 DB? Can I use something like ALTER schema via MS SMS without issues or is this something that needs to be done by the admins? Thanks again!
 
So I tried to ALTER the schema but I'm getting an error as shown below. It looks like maybe I cannot fix this from my end.

ALTER SCHEMA newuser_name TRANSFER olduser_name.table;

Error:
Cannot alter the schema 'newuser_name', because it does not exist or you do not have permission.
 
We have replied your support ticket. Please check our reply and update us back on ticket for further assistance. We are always here to help you.
 
It looks like the schema change effected the old 2000 version of one of the DBs as it is now not working and parts of the web site are down. I've updated the ticket as well. A restore on one of the old 2000 DBs may correct this and I'm confused as to why the schema change would have effected the old 2000 DBs. Any schema changes should only have been applied to the new 2005 DBs. Any ideas how to correct this? Thanks!
 
Any updates on this issue? I was hoping a quick restore could fix this but I haven't heard back on my support ticket updates. Please let me know what is happening if possible. This is causing major issues on the live web site and making me look bad to my client. Thanks!
 
You must log in or register to reply here.
Back
Top