June 20, 2007 at 10:49 am
I need to move our current sql2000 database server to our new server that contains sql 2005, anyone have any info, links to how i would accomplish this, ive never worked with sql 2005, thanks.
June 20, 2007 at 2:26 pm
You can detach databases one by one to bring it offline. Then copy and paste data and log file from the current sql2000 to the new sql2005. After you copy those files, re-attach the databases in the new server to bring them back online. The process seems to be straightforward if your current databases are not in replication or any other operations that keep them online.
Don't forget to back up databases before you even touch them!
Have fun!
June 20, 2007 at 2:49 pm
A side by side installation has worked for me in the 2 migrations I've quarterbacked. Basically install 2005 as a separate instance and then backup/restore the db's on the new instance. Make sure when you do this to upgrade the compatibility to 2005 (90). Good Luck!
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
Ben Sullins
bensullins.com
Beer is my primary key...
June 20, 2007 at 2:51 pm
This is going to be on a separate server so i think detaching/attaching the databases should work fine. What about master, model, and msdb? I have jobs id like to keep, and user access, but not sure if the jobs will still work on 2005?
June 20, 2007 at 10:56 pm
script the jobs, logins and user permissions and apply them to the new 2005 server.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 21, 2007 at 8:05 am
You should also install the Upgrade Advisory tool from the SQL 2005 cd onto the SQL 2000 box. Run that against the SQL 2000 database and it will tell you of any issues you may face when you go to compatability mode 90.
June 21, 2007 at 8:42 am
I agree with Sugesh. All the SQL 2000 to SQL 2005 migrations we've done so far have been done by scripting logins, jobs, and linked servers and running the script in the SQL 2005 instance. We then restored backups of the user databases and changed the compatibility level.
Greg
Greg
June 21, 2007 at 9:05 am
I know how to script the jobs, but how do i script the logins? Once i ahev the script for them, do i just run them in QA on the 05 server?
June 21, 2007 at 10:48 pm
Goto to enterprise manager, tools, generate script and in the options tabs you can find script server logins.... check that it will script your logins in the server. include object level permissions also if you need.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 22, 2007 at 6:33 am
Two other things that have not been mentioned.
-- This fixes storage that gets corrupted in 2000. 2005 keeps track better
dbcc updateuseage( 'eachdb')
-- 2000 statistics could be bad also, update them
exec eachdb..sp_updatestats
This was mentioned but definately use the upgrade advisor. As for changing compatiblity to 2005(90) You might want to hold off for a while until you are sure there are no issues. 80 mode works well to simulate 2000 and there may be procedures and such that don't work in 90.
June 22, 2007 at 8:21 am
To transfer jobs and logins, you could also use SSIS. There are tasks to transfer jobs, logins, database, and SQL Server Objects.
When we migrated from SQL Server 2000 to 2005, we did backup of the 2000 databases, used SSIS to transfer the logins and then restored the user databases. Everything worked well except we did have an issue with Collation settings. When we installed 2005 we used Latin1_General_CI_AI instead of SQL_Latin1_General_CP1_CI_AS which was what our 2000 server's default was. We decided that we wanted to use Windows collation instead of SQL collations because of some irregularities with sorting Unicode data. I wrote a script to loop through all tables in a database and convert the collation settings.
Wendy Schuman
June 22, 2007 at 8:26 am
Regarding scripting logins, sp_help_revlogin will script both SQL Server logins and Windows logins. You can get it here: http://support.microsoft.com/kb/246133/en-us
Greg
Greg
June 22, 2007 at 9:03 am
Thanks for the help guys, ill let you know how it goes.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply