locked
Migrate db from sql 2000 to sql 2008 RRS feed

  • 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

    Friday, May 6, 2011 6:11 AM

Answers

  • 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.

    • Proposed as answer by Jerry Nee Monday, May 9, 2011 10:48 AM
    • Marked as answer by XavierXie Tuesday, May 10, 2011 12:22 AM
    Friday, May 6, 2011 6:24 AM

All replies

  • 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.

    • Proposed as answer by Jerry Nee Monday, May 9, 2011 10:48 AM
    • Marked as answer by XavierXie Tuesday, May 10, 2011 12:22 AM
    Friday, May 6, 2011 6:24 AM
  • thanks a lot Christa!
    Tuesday, May 10, 2011 12:22 AM