Moving from SQL Server 7 to 2008

  • I have a customer who, despite my efforts over the years, has stuck with SQL server 7.

    However, they have now bought a new server which will come with SQL Server 2008 installed.

    As I'll have to do the switch for them soon, I just wondered if anyone on this board has done a jump in versions like this.

    I know MS provides ways to go from SQL 2000/2005 to 2008 but can see nothing about moving data from SQl server 7 to the new one.

    Any tips would be greatly appreciated.

    Thanks

  • I don't think you'll be able to do it in one step. SQL 2008 won't allow a database that's in compatibility mode 7, which is what a SQL 7 database will be in after upgrade. You're probably going to have to migrate it to 2000/2005, and then up to 2008.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Depending on how big your database is, you could write a SSIS package to copy all the database objects across.

  • I'd go with a two-step upgrade. First to 2005, then to 2008. Unless the database is really, really simple, that should get the job done in the best way.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • hello,

    you have to upgrade to mssql 2000 and next to 2008

  • I think it's safe to assume 2 versions backward comptability

    SQL 2005 = upgrade from 2000 and 7.0

    SQL 2008 = upgrade from 2005 and 2000

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • Well thanks to everyone for their replies.

    We've just completed the switch and it worked fine.

    Just in case anyone's interested, here's what I did.

    I used the "detach - attach" system for the change. I set up a SQL 2005 Express on a workstation, detached the databases from SQL Server 7, copied them to the workstation and then attached in SQL Server 2005. At this point I ran DBCC UPDATEUSAGE on all the databases. Then I did the same from 2005 to 2008, detaching, copying to the new server and attaching. I also ran DBCC UPDATEUSAGE on the 2008 server, however it seems like this step is not necessary as DBCC reported no changes. Just to be sure I also did an sp_updatestats in each database. I also found a script somewhere to copy all the logins from SQL Server 7 to 2008. Worked fine too.

    So far we've been up and running two days on the new server, just one small error detected in a stored procedure, otherwise everything seems hunky-dory.

    Thanks again

    John

  • I would suggest that you run a CheckDB at your earliest convenience, as more checks are done in 2008 then were in 2000 or earlier.

    May the Force be with you.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for the tip!

    😉

  • jchallett (1/30/2009)


    Thanks for the tip!

    😉

    ...and final decision is !?

    1) form SQL 7 >>> SQL 2000 >>>> SQL 2008

    2) from SQL 7 >>>> SQL 2005 >>> SQL 2008

    ..and the winner is ...!?

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Dugi (1/30/2009)


    jchallett (1/30/2009)


    Thanks for the tip!

    😉

    ...and final decision is !?

    1) form SQL 7 >>> SQL 2000 >>>> SQL 2008

    2) from SQL 7 >>>> SQL 2005 >>> SQL 2008

    ..and the winner is ...!?

    Well as I said I went the SQL 7 >>>> SQL 2005 >>> SQL 2008 route.

    Can't compare because I didn't try the other way. All I can say is this way worked fine for me.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply