Migrate db from sql 2000 to sql 2008

Question

Hi guys

I am currently doing a job that I need to migrate a database from a sql 2000 server to sql 2008 server. My questions are:

1. if I get the backup file (database.bak) and restore it onto the sql 2008 server, will the DTS packages, security settings, tables, views ect all be transferred? (what data does a bak file actually contain?)

2. if not, what's the best way to migrate DTS packages to SSISs ? I learned that it's like only 50% of DTS can be transferred ok.......

3. if not, how do I transfer security settings?

 

Thanks for your helps guys!

 

Xavier

Answer 1

1. all objects will be transferred but not the DTS packages. Security Settings for the database will be transferred but if you use SQL Logins, they must be createtd in the new server and the logins have to be pointed to the existing users via sp_change_users_login.
The Logins can be scripted and created on the new server.

2. DTS Packes are located in msdb. DTS Packages should be recreated with SSIS. If no Active-X is used in the packages they might be migrated to SSIS but you should test it thoroughly.

3. If you use Integrated Security, all will work after creating the Logins.

Answer 2

thanks a lot Christa!