SQL 2000 to SQL 2005 Migration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL 2000 to SQL 2005 Migration

Author

Topic

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2007-02-18 : 12:22:48

I have to migrate our existing SQL Server 2000 (standard edition) running on Windows 2000 server (32 bit) to a new windows 2003 server (64 bit) (SQL 2005 standard edition). I am thinking of doing this migratin after hours (or may be on weekend so a

I have planned the following steps (in order).

1. Install SQL Server 2005 on new server.(windows authenticatin and a SQL server will be running on a separate domain account)

2. Download SQL server upgrade advisor on current SQL Server 2000 (windows 2000) and run a report.

3. Back up (Full) SQL Server 2000

4. Script logins and schedule tasks on SQL 2000

5. Restore database on SQL 2005

6. Create jobs by runing Jobs scripit on SQL 2005

7. Create users and passwords by running the exported script file.
Please refer to:
How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/246133/en-us

I am aware that I might face issues of
db compatibility level, statistics out of date so I need to update statistics and may be redindex all my tables

PLEASE LET ME Know if I am missing something obvious? Not sure about our existing DTS packages? Any information. Right now I am just thiking of running all my existing 2000 DTS in 2005 as legacy

Note: Rob, Tara, Michael Valentine Jones, Mladen or Nigel , if you cam provide some input from your wealth of experience on this matter. I would sincerely appreciate it.

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2007-02-18 : 13:29:15

Forgot to mention our current SQL2K have SP3, do we need SP4 applied before we can migrate to SQL 2005?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-18 : 13:48:30

you don't need to apply sp4 before migration.

well you can't port dts packages. you'll have to rewrite them in SSIS.
you can run them in legay mode.

check if you're using any unsupported stored procedures in SS2k that also aren't supported in SS2k5.

you forgot
8. Test everything very well