Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Do we migrate (new hardware) 2000 databases or server?

Status
Not open for further replies.

Trackhappy

IS-IT--Management
Oct 1, 2002
81
US
We have new hardware for our SQL 2000 server. It has a myriad of small databases on it, all from different sources. Is there an easy way to migrate the whole thing to a new platform, or should we build the new server (new name) and migrate each database separately? I guess the main concerns are security (SQL security), how does SQL 2000 handle renaming the new server once built to be the same name as the old?, ODBC links etc.
I am favouring separate moves, only because I have never moved a SQL server in big-bang fashion.
Any hints, experiences, links to documents? All the ones I have found are migration from SQL7 to SQL 2000.

Thanks
Glenn.
 
I would go for restoring backups.
Just follow your DR plan :).

For renaming the server - can't you give it a new name and change the clients to connect to the new server.
Otherwise it's always best to build from scratch with the correct name.
If you can't do that then it's quite easy to change the name - just rerun the setup and it will spot the name change. After that there may be a few niggling things - agent jobs may have the wrong target server so you have to update sysjobs.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
 
Thanks Nigel. The issue is that there are lots of "wierd" little apps (Tax apps, authentication servers, EDMS,...) that all have their own settings. To find them all and change them is somewhat painful. I'd prefer to keep the same name. DR type recovery did cross my mind, I was hoping there was an easier/nicer way. Detaching/Attaching the database sounds good, but I wasn't sure about security. I did find a post about using a dts package and exporting it. That's a maybe. :).
 
>> I did find a post about using a dts package and exporting it.
That would be a last resort I would think.

Best is to backup and restore.
Next detach / attach. You will have to take backups first and should do test restores just in case the files aren't attachable so the above is easiest.

Security shouldn't be a problem - you may not match up login ID's with the database users after the restore but see sp_change_users_login

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top