Import from MSSQL2000 (8) to 7?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Erwin Moller
    #1

    Import from MSSQL2000 (8) to 7?

    Hi Group,

    I developed a intranet site using MSSQL7/win2000 some time ago.
    The target environment used MSSQL2000/8.
    We were (almost painlessly) able to import the db-scheme and data from 7
    to 8. (Bravo MSSQL)

    Now I need to do some upgrading on the application and I would like to
    have a copy of the database from MSSQL2000/8 to MSSQL7.
    Is that also possible?

    Or should I download Microsoft SQL Server Express and use that instead
    of my MSSQL7? Is it better?

    I hope I can get the relationsheet too (that one with Foreign Keys
    mapped in a nice graphical way).

    Any advise highly appreciated.
    I am good with Postgresql, but my MSSQL skills leave a lot to be
    desired. :-/
    For an outsider like me the many versions and OS's are quite confusing.

    Do I need special commands on MSSQL2000/8 to create a MSSQL7 compatible
    export?

    Thanks in advance!

    Regards,
    Erwin Moller
    Tags: None
    • Erland Sommarskog
      #2
      Re: Import from MSSQL2000 (8) to 7?

      Erwin Moller (since_humans_r ead_this_I_am_s pammed_too_much @spamyourself.c om)
      writes:
      Now I need to do some upgrading on the application and I would like to
      have a copy of the database from MSSQL2000/8 to MSSQL7.
      Is that also possible?
      You would have to build the database on SQL 7 from scripts, and move the
      data with bulk load. I don't know you did the import in the other direction,
      but going to a higher version it's possible by backup/restore or simply
      attaching the database to the higher version. Going to a lower version
      this is not possibe.
      Or should I download Microsoft SQL Server Express and use that instead
      of my MSSQL7? Is it better?
      Well, SQL 7 came out in 1998, SQL Express in 2005, so there are some
      more features. But there are also some things that don't really work
      as in SQL 7. For instance the old-style join *= is available only
      in compatibility level 80 and lower.

      Nevertheless, I would recommend going to SQL 2005 if possible.




      --
      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

      Books Online for SQL Server 2005 at
      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

      Books Online for SQL Server 2000 at
      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

        Comment

        • Erwin Moller
          #3
          Re: Import from MSSQL2000 (8) to 7?

          Erland Sommarskog schreef:
          Erwin Moller (since_humans_r ead_this_I_am_s pammed_too_much @spamyourself.c om)
          writes:
          >Now I need to do some upgrading on the application and I would like to
          >have a copy of the database from MSSQL2000/8 to MSSQL7.
          >Is that also possible?
          >
          You would have to build the database on SQL 7 from scripts, and move the
          data with bulk load. I don't know you did the import in the other direction,
          but going to a higher version it's possible by backup/restore or simply
          attaching the database to the higher version. Going to a lower version
          this is not possibe.
          >
          >Or should I download Microsoft SQL Server Express and use that instead
          >of my MSSQL7? Is it better?
          >
          Well, SQL 7 came out in 1998, SQL Express in 2005, so there are some
          more features. But there are also some things that don't really work
          as in SQL 7. For instance the old-style join *= is available only
          in compatibility level 80 and lower.
          >
          Nevertheless, I would recommend going to SQL 2005 if possible.

          Hi Erland,

          Thanks for your answers.
          I think I'll switch to SQL Express 2005.
          One last question: If I switch to SQL Express 2005, do you expect that I
          can copy the database from MSSQL2000 to SQL Express 2005 without scripts
          and bulk data load?

          Regards,
          Erwin Moller

            Comment

            • Erland Sommarskog
              #4
              Re: Import from MSSQL2000 (8) to 7?

              Erwin Moller (since_humans_r ead_this_I_am_s pammed_too_much @spamyourself.c om)
              writes:
              I think I'll switch to SQL Express 2005.
              One last question: If I switch to SQL Express 2005, do you expect that I
              can copy the database from MSSQL2000 to SQL Express 2005 without scripts
              and bulk data load?
              Yes. You can copy with attach/detach or BACKUP/RESTORE. Note that you
              cannot copy back this way. This must be done with scripts and bulk load.

              Also, be sure to run sp_updatestats when you have copied the database
              to SQL 2005, as the migration invalidates all statistics.


              --
              Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

              Books Online for SQL Server 2005 at
              http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

              Books Online for SQL Server 2000 at
              http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                Comment

                Previous template Next
                Working...