October 7, 2006 at 8:56 am
What is the easiest way to upgrade from SQL-2000 to SQL-2005?
My 2000 production database uses: SQL athenication, automated backup and a database maintenance plan. In addition, it contains all the standard SQL-2000 objects e,g, tables, views, stored procedures, etc.....
sql2day
October 9, 2006 at 10:18 am
The easiest way is to run upggrade in place - just upgrade your instance to 2005 and it will automatically upgrade your databases.
But more reliable way is to run upgrade to another server. Make full backup of all your SQL Server 2000 databases, then restore them on a 2005 server, during restore it will automatically upgrade your databases. After restore check you maintenance plans. If something will go wrong, you will be able to switch back to your old, 2000 server. I would even recommend to run a "dry run" before actual production upgrade.
What will be hard or even impossible to upgrade is DTS packages, if you have them you will most likely have to re-write them in DTC successor - SSIS . Also, if you have some sripts running on other servers using linked server to yours, make sure that they are pointing to right server name.
Otherwise everithing else is pretty simple .
October 12, 2006 at 3:36 pm
Thank You--
1st attempt: I tried upgrading an SQL-2000 instances in place. Result: It failed due to password policy.
2nd attempt: I copied production database files and attached to SQL-2005 instance. All worked fine including database logins. However, Window logins were orphaned. Is there an easy way to synchronize orphaned windows logins with database logins?
3rd attempt: Recreated database on SQL-2005 instance and tried restoring from backup. Result: Failed
I'll try again
SQL2DAY
October 13, 2006 at 8:33 am
I'm starting to migrate sql2000 x32 to sql-2005 x64 on a different server.
My question is what's is the best way to migrate the databases between the boxes?
Steve
October 13, 2006 at 1:59 pm
To re-map orphaned users to logins you have to run sp_change_users_login from each database or you can run this script http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615 in master database and run it once from there.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply