Post reply

Restore SQL2008 File to SQL2005

  • March 9, 2009 at 8:49 am

    #200583

    Hello,

    now I know, that this isn't possible. (!?)

    I've found that link (http://www.devx.com/dbzone/Article/40531) which generates a script to import structure and data to the SQL2005 server.

    Is there a way to generate that script automaticly (via commandprompt e.g) ?

    Or is anybody here with an cool 'BACKUP DATABASE IN SQL2005 format' tool 😉

    thanks for advice,

    best regards,

    Michael

  • March 9, 2009 at 9:24 am

    #956110

    I think, if you downgrade the compatibility level of the database from 2008 to 2005 (100 to 90), detach the database from the 2008 server, copy the file over, and re-attach to the 2005 server, that it will do what you need. I'm not sure, since I haven't tried that, but I think it will work.

    - 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

  • March 9, 2009 at 10:43 am

    #956205

    I don't think you can downgrade a database form SQL 2008 to SQL 2005 it's not possible. Probably you could script it out and run it against the 2005 server.

  • March 9, 2009 at 2:30 pm

    #956402

    GSquared (3/9/2009)

    I think, if you downgrade the compatibility level of the database from 2008 to 2005 (100 to 90), detach the database from the 2008 server, copy the file over, and re-attach to the 2005 server, that it will do what you need.

    Won't work. Compat mode just affects how queries run and how T-SQL is interpreted. It does not change the underlying file structure or the database's internal version.

    There is no simple way to downgrade a SQL database from one version to another. You cannot backup a 2008 database and restore on 2005, you cannot detach a 2008 database and attach on 2005. The internal structure of the files are different, the system tables are different.

    The only way you're going to get that DB back to 2005 is to script all the tables, pros, views, etc, export all the data then create a new database on 2005 run all the scripts and reimport the data.

    The SSIS Copy Database Task should do all that for 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

  • March 10, 2009 at 4:14 am

    #956650

    Hello to all,

    thanks for your advice. I'll try the SSIS Copy Database Task.

    Best regards,

    Michael

Viewing 5 posts - 1 through 4 (of 4 total)

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