| Author |
Topic |
|
bryan99y
Starting Member
42 Posts |
 Posted - 2002-01-28 : 11:27:23
|
| We are in the process of migrating our sql server 7.0 databases to sql server 2000.We have purchased two new servers for this process.What is the best method for doing the database migration ?- Copy Database Wizard ?- DTS ? |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-01-28 : 11:35:10
|
| I've personally had quite a bit of luck with the Copy Database Wizard. If you are coping from one server two another, the only real gotcha can be security.Jeff BanschbachConsultant, MCDBA |
 |
|
|
monkeybite
Posting Yak Master
152 Posts |
Posted - 2002-01-28 : 12:36:30
|
| I'm not a big fan of any type of wizards, but the copy database wizard does these steps anyway. Here's what I do: - Transfer logins from 7.0 to 2000 server (using DTS on 2000 box) - Detach DB files from 7.0 box, copy to 2000 box - Attach DB files to 2000 box - Run sp_updatestats on all newly attached DBs on 2000 box - Run sp_change_users_login for each DB user in all newly attached DBs to sync user SID with new login SIDs.Again, I'm pretty sure the Copy Database wizard does all these steps anyway, but I like to know what's exactly what's going on, so I run these steps manually. One can skip the DTS Transfer Logins task by running a script to extract logins and encrypted passwords from the 7.0 box, then executing the output on the 2000 box to re-create the logins and passwords (Check out Microsoft KB article Q246133).-- monkeybite |
|
|
|
bryan99y
Starting Member
42 Posts |
Posted - 2002-01-28 : 14:03:53
|
| what about using Backup/Restore ?1. Backup 7.0 DB's2. Copy backup files to new server3. Restore to 2000 DB'sAny issues ? |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-28 : 15:42:26
|
| You can do that instead of detach/attach, but the rest of the operations are still required. You will have to fix the orphaned users. I have used that method several times. It actually works quite well.-Chad |
|
|
|
shurley
Yak Posting Veteran
90 Posts |
Posted - 2002-01-28 : 15:55:58
|
| Where was this post last week when I was in the middle of a 7.0 to 2000 migration???Glad to know it wasn't just me.Regards,Shawn |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-28 : 16:13:30
|
quote:
Where was this post last week when I was in the middle of a 7.0 to 2000 migration???
All you had to do was ask and it would have been here   -Chad |
|
|
|
bryan99y
Starting Member
42 Posts |
Posted - 2002-01-28 : 17:05:05
|
| Chad - why whould I have Orphaned Users ???On the New Server : (SQL Server 2000)I would restore the Master, Model, MSDB, them all user Databases from 7.0 Server. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-01-28 : 17:18:21
|
| System databases have changed in SQL2000, so you will not be able to restore master and msdb (or rather you could restore them, but things aren't gonna work right after you do...). BTW, there is some more information on SQL7->SQL2k conversion here:http://sqlteam.com/Forums/topic.asp?TOPIC_ID=9565 |
|
|
|
bryan99y
Starting Member
42 Posts |
Posted - 2002-01-28 : 19:33:15
|
| Chad - I have a situation where our SQL Servers can be taken down for the weekend of the Migration. What method of Migration would you suggest ? (7.0 to 2000)(I have 2 New SQL Server for this process).Also, what issues are there with your suggestion ?(more details the better)Thanks in advance. |
|
|
|
JamesH
Posting Yak Master
149 Posts |
Posted - 2002-01-29 : 10:45:15
|
| Man, you're lucky. I have to schedule a downtime atleast a quarter in advance and usually only get 15 minutes. LMAO.Seriously,It really depends on which method you are most comfortable with. I personally would take a look at what I think needs cleaned up (old users, old jobs, old packages, etc.) and take the oppurtunity to get rid of the junk. After I've cleaned out the junk I would see no problem using the upgrade wizard, it's pretty solid. You could also script out all jobs, DTS Packages, Alerts, Operators, logins (Include syslogins.sid) and then detach the databases - run sp_attach_db (now's your chance to clean up the .mdf/.ldf file directories), run the rest of your scripts LOGINS First, then Operators, Alerts, DTS Packages, Jobs. By bringing over the sid from your original server there will be no need to re-synch the login. If you don't sp_attach_db first then all of your users will default to master database. I would go with the wizard, but what the hell you have all weekend right? |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-30 : 00:55:56
|
| James makes good suggestions. Since you have 2 new machines, you can test whichever method you choose, and that is the most important thing. Whether you choose detach/attach, backup/restore, or wizard, I suggest doing a dry run or 2 before the final migration.I just migrated a client last month, and I used Backup/Restore. The only real issue has already been addressed, and that is the login/user issue. It is relatively simple to fix with sp_change_users_login. The rest of the stuff (Jobs, operators, etc..) can either be moved via DTS, scripted, or manually recreated on the new server.Finally, don't forget to reindex, and update stats after the migration.HTH-Chad |
|
|
|
bryan99y
Starting Member
42 Posts |
Posted - 2002-01-31 : 23:26:04
|
| Chad - Did you restore the Master database to the SQL Server 2000 Server ?Did the SQL Server 2000 Server have a different name ? |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-01 : 12:22:53
|
| I didn't restore master, I did almost exactly as Monkeybite says in his first post. I transfered the logins via dts and then updated them using sp_change_users_login. There is not a real big reason, aside from the logins, that you need the same master.The machine had a different name (The new machine was actually a cluster)-Chad |
|
|
|
bryan99y
Starting Member
42 Posts |
Posted - 2002-02-01 : 18:58:52
|
| sp_change_users_login - what values do I put in this to fix all Logins ? |
|
|
|
sumwanlah
Starting Member
43 Posts |
Posted - 2002-02-04 : 03:57:55
|
if you want a warp-speed solution, then use this command: sp_change_users_login 'report'. this command will give you the list of logins that have been left orphaned after the move.then you have to run sp_change_users_login 'auto_fix', 'userName', 'loginName' to reunite these poor souls with their 'parents'...  be warned... you might have to run this command for every lost soul... am i right on this people?!?!a good resource of where you can get more information on this, and many more stuff SQL, use the SQL Books Online reference. you can either refer to it online or download it at this address: [url]http://microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url] |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-04 : 13:19:33
|
| I have a script (Stored Procedure) that will fix all the users in the current database.I will post it in the scripts forum.-ChadRobvolk-Here's the link if anyone needs it:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615Edited by - robvolk on 02/04/2002 14:24:15 |
|
|
|
|
Migrating from 7.0 DB's to 2000