locked
Can I attach/Restore 2005 database to 2000 server... RRS feed

  • Question

  • Can I attach/Restore 2005 database to 2000 server...
    Monday, August 16, 2010 11:34 AM

Answers

  • No, but  you can transfer the data by using SSIS or third party tool such as www.red-gate.com
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, August 16, 2010 11:57 AM
  • Uri Dimant MVP is correct

    Just pointing out that you need to take care of users and jobs to just in case you didn't think of this. Script out any jobs related to the database, and migrate over the accounts using the sp_help_revlogin procedure: http://support.microsoft.com/kb/246133

    An alternative solution to creating SSIS package is the Copy Database Wizard, it actually creates a SSIS package for you, but it's very easy to use.


    Regards Marten Rune Microsoft Certified IT Professional Database Administrator/Developer 2008
    Monday, August 16, 2010 12:23 PM

All replies

  • No, but  you can transfer the data by using SSIS or third party tool such as www.red-gate.com
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, August 16, 2010 11:57 AM
  • Uri Dimant MVP is correct

    Just pointing out that you need to take care of users and jobs to just in case you didn't think of this. Script out any jobs related to the database, and migrate over the accounts using the sp_help_revlogin procedure: http://support.microsoft.com/kb/246133

    An alternative solution to creating SSIS package is the Copy Database Wizard, it actually creates a SSIS package for you, but it's very easy to use.


    Regards Marten Rune Microsoft Certified IT Professional Database Administrator/Developer 2008
    Monday, August 16, 2010 12:23 PM
  • Use the microsoft tool "Database Publishing Wizard". It's a lot easyare. It generates a .SQL file witch builds the database, schemas, constraints etc etc, and then inserts the data.

    Quote: "SQL Server Database Publishing Wizard enables the deployment of SQL Server databases into a hosted environment on either a SQL Server 2000 or 2005 server. It generates a single SQL script file which can be used to recreate a database (both schema and data)"
    link: http://www.microsoft.com/downloadS/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

    In addition to this you can script out tha accounts using sp_helprevlogin, Quote: "After you move databases to a new server, users may not be able to log in to the new server" and "You must transfer the logins and passwords to the new server. This article describes how you transfer logins and passwords to a new server"
    link: http://support.microsoft.com/kb/246133

    And rightclick on any job thats related to the database, and choose "Script job as..."


    Regards Marten Rune Microsoft Certified IT Professional Database Administrator/Developer 2008
    Tuesday, August 17, 2010 10:44 AM