Post reply

Can you restore a 2008 DB to 2005?

  • March 13, 2012 at 7:25 am

    #258546

    I was given a SQL Server 2008 "bak" file.... it would not restore to SQL Server 2005. I then tried to restore to SQL Server 2008 to see if it were from a SQL Server 2008 backup. It restored with no issues. Unfortunately, the restore needs to be on a SQL Server 2005 instance on our cluster. I tried to use an SSIS package, but it complained about the versions. I think tried Export/Import.... still received version errors. I then tried to generate a create script. Still have issues. Can a SQL Server 2008 backup be restored to a SQL Server 2005 instance? Thank you in advance for any information.

    Charlie

  • March 13, 2012 at 7:34 am

    #1458461

    Hi Charlie, no. You will have to migrate the structure and data from the 08 to a 05 database.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • April 23, 2012 at 8:08 am

    #1477509

    In SQL Management Studio, right click on the database, click Tasks and then Generate scripts.

    Depending on the complexity of your database, generate scripts for tables, views and UDF's first, stored procedures second.

    Then generate separate scripts for the data.

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • April 23, 2012 at 8:26 am

    #1477528

    Robin Sasson (4/23/2012)

    Then generate separate scripts for the data.

    BWAAA-HAAAA! That's a bit like saying "It's easy to get to the moon. Just build a rocket ship." 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 23, 2012 at 8:27 am

    #1477530

    rummings (3/13/2012)

    I was given a SQL Server 2008 "bak" file.... it would not restore to SQL Server 2005. I then tried to restore to SQL Server 2008 to see if it were from a SQL Server 2008 backup. It restored with no issues. Unfortunately, the restore needs to be on a SQL Server 2005 instance on our cluster. I tried to use an SSIS package, but it complained about the versions. I think tried Export/Import.... still received version errors. I then tried to generate a create script. Still have issues. Can a SQL Server 2008 backup be restored to a SQL Server 2005 instance? Thank you in advance for any information.

    Charlie

    How big is the database and how many tables do you have?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 23, 2012 at 8:29 am

    #1477532

    Ok, yes Jeff you have a point but at least its a starting point.:hehe:

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • April 23, 2012 at 8:52 am

    #1477554

    Post removed. See GilsaMonsters' following post ... as it IS CORRECT !

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • April 23, 2012 at 8:55 am

    #1477559

    bitbucket-25253 (4/23/2012)

    ALTER DATABASE SET SINGLE_USER

    Change the compatibility level of the database. -- 2005 level is 90

    Put the database in multiuser access mode by using

    ALTER DATABASE SET MULTI_USER

    BACK UP the altered DB

    Attempt to restore the BACK UP of the altered DB to SQL 2005

    Will not work.

    Compatibility level solely controls how the query processor treats some T-SQL constructs.

    A database attached to a SQL 2008 server is a SQL 2008 database and cannot be restored or attached to a lower version.

    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

  • April 24, 2012 at 12:57 am

    #1477930

    rummings (3/13/2012)

    I tried to use an SSIS package, but it complained about the versions. I think tried Export/Import.... still received version errors. I then tried to generate a create script. Still have issues. Can a SQL Server 2008 backup be restored to a SQL Server 2005 instance?

    No you can't restore it, but you could use SMO to move the database objects from 2008 to 2005, google should help you here.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • April 25, 2012 at 8:48 am

    #1479053

    Alas and alack, a day late and a dollar short, but I ran across this article today, and it might be what is needed.

    http://www.mssqltips.com/sqlservertip/2676/export-data-to-an-earlier-sql-server-version/?utm_source=dailynewsletter&utm_medium=email&utm_content=text&utm_campaign=2012424

    The author is: By: K. Brian Kelley, and the article shows how to use SSMS to accomplish the task ...

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • April 29, 2012 at 6:58 am

    #1480918

    Its faded memory, I think I did it with Copy Database task in SSMS. I don’t have SS2K5 to test it now, please give it a try. It would be lot easier than Export / Import Data Task.

    As a side note, please don’t select detach-attach method (default) in Copy Database wizard.

  • April 29, 2012 at 7:09 am

    #1480921

    Dev (4/29/2012)

    It would be lot easier than Export / Import Data Task.

    huh, how do you work that out, they both have the same end result, execute an SSIS package!!

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • April 29, 2012 at 7:22 am

    #1480923

    Because you don't need to select individual tables in copy database command. Also, it copies everything that belongs to database, not just tables / views and data.

Viewing 13 posts - 1 through 12 (of 12 total)

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