07-04-11, 02:35 #1Registered User
- Join Date
- Dec 2006
Unanswered: Migrating form MS SQL2000 to MS SQL2008
i've got an outdated but heavily used SQL-Server 2000. In fact it is the only component of an old SBS2000 that is still in Use. Not only the Software ist outdated, the Hardware is old too.
So i got to migrate. I downloaded an SQL 2008 R2 Express-Edition as a playground and startet testing before i gonna buy a Standard-Editon Server.
Doing some imports, several questions came up that i couldn't solve myself. Hence a short answer ( may be just a link to an good article on that topics ) is apreciated very much. Here is my list of questions.
1. my SQL2000 Database is 32Bit, i read that it would be better to migrate to a 32Bit Edition then. Are there disadvantages when migrating to 64Bit? Any problems to cope with?
2. I was unable to just load a 2000 .bak backup into the SQL2008-Database. Is there no way to do so or did i iss anything?
3. I used the import via DTS then. This way leads to loss of Indixes and Identity-Columns. I assume that i have to build SQL-Scripts for creating empty tables containing identity-Columns and triggers and then use DTS-Importing on them (IDENTIY_COPY) !? Is this way of building a Skeleton first and then importing Data the best way?
4. Is there no comfortable way to get a database with all its tables and there corresponding identityIDs, Indexes,Triggers and Stored-Procedures transfered? Or have i just missed something in transferring databases.
5. DTS-Importing always turns Views to complete tables. I mean it's easy to recreate the views by a script, but is there really no way in DTS to import Views ans Views???
The last time i did such an migration it has been the Migration from MS SQL 6.0 (SBS 4.5) to MS SQL 2000 ( SBS 2000) and this is by now almost a decade away . So i remember we did it this way, but ist there 10 years later, no better way?
Any hints,words, tipps are apreciated as it is better to learn before i do a real migration and i perhaps baught the wrong version ( 32bit vs. 64bit).
Greetings and Thanx in advance
Last edited by wegus; 07-04-11 at 02:48.an Apple a day keeps Dr. Watson away !
07-04-11, 23:04 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
The only real disadvantages to converting from 32 bit to 64 bit is that you lose the opportunity to spend hours trying to deal with memory problems. Without this, yoiu actually work instead of spending hours trying to figure out work arounds for problems that don't exist in the 64 bit platform... In case you're still wondering, go 64 bit if you have the opportunity!
I'm nearly certain that you can restore a SQL 2000 onto a SQL 2008 R2 server, but I don't have either one handy to confirm that thought... I'm not sure what is causing you grief, so please post whatever diagnostic messages you get when you do the restore.
The one thing I can guarantee that you can NOT restore is the system databases, especially master. The only way to take them from SQL 2000 to SQL 2008 R2 would be to do an "in place upgrade" of your server. This is probably beyond your current skill set, but this would be the only way to preserve the logins and other system settings.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.
07-05-11, 03:05 #3Registered User
- Join Date
- Dec 2006
Thank you for your 64bit advice. A very good point!
Well system/master don't bother me much. Things in there will be replaced/rebuild quite quick in our case.
What is anoying is the trouble you run in when using DTS. I made a Skeleton rebuild by generating empty tables and triggers, indexes and views first and then importing the content via DTS that just works fine.
i only had to disable all timestamp-columns as they can not be inserted and i do have to activate IDENTITY_INSERT on most tables.
Well as i read, i could create and save the DTS Script when using a real Server ( i am just trying with the Express Edition by now). The Express Edition obviously can't start the SQL Agent so i can't test our cron-jobs and Backup. But those things surely work in the commercial version.
Originally Posted by Pat Phelanan Apple a day keeps Dr. Watson away !