Post reply

Is it possible to restore a database to a lower version server?

  • March 7, 2011 at 1:41 pm

    #388996

    Is it possible to restore a database that had been backed up after the SP4 was applied to a reinstalled version of SQL Server 2005 at the SP3 level?

    I have production backups that can be restored to my test environment for all databases but one.

    [production is still at SQL Server 2005 SP3 CU1 Microsoft SQL Server 2005 - 9.00.4053.00 (X64) ]

    Thank you

  • March 7, 2011 at 1:44 pm

    #1295573

    backs are stored at a major version level: SQL200/2005/2008/R2;

    service packs do not alter the format of the backup files, so you can restore to any installation of 2005; you can restore to 2008 or above., but you wouldn't be able dummy them down to a lower version afterwards; you'd be stuck at the higher version.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 7, 2011 at 6:30 pm

    #1295665

    What's the error that's raised when you attempt to restore the database?

    SQL BAK Explorer - read SQL Server backup file details without SQL Server.
    Supports backup files created with SQL Server 2005 up to SQL Server 2017.

  • March 8, 2011 at 7:52 am

    #1295886

    I have not received an error -- before removing the SQL Server installation to reinstall I am making sure that I have a means to restore all of the exisiting databases.

    I would prefer salvage this server but will recreate if I have to.

    Thank you.

  • March 9, 2011 at 9:23 am

    #1296594

    never tested with SP4 yet, but on SP3 and back to SP2 it works

  • March 11, 2011 at 9:49 pm

    #1297964

    What I've been able to do is to, under Tasks, generate scripts - there I need to choose 2005 as the backup type, and one of the other options (schema is the default property value), I choose schema and data.

    Once I generate the script, then, I open up 2005, open the script and run it. Voila - I have 'downgraded' from 2008 to 2005!

    The full scenario I had (including publishing to a hosted server) is here:

    http://allthingsdotnet.net/?p=691

    David Wier

    http://allthingsdotnet.net

  • March 18, 2011 at 3:49 am

    #1300366

    I totally agree with what Lowell said. SErvice packs do not alter the format of the backup files. So you can restore to any installation of 2005.

    David,

    Scripting out works well for small databases, but you will have a hard time scripting out objects on large db's.

    Sanz

  • March 18, 2011 at 3:04 pm

    #1300819

    Recently applied SP4 to our Development server and tested by moving a database from that particular server to our QA (which is still SP3) all worked fine.

    Chris Powell

    George: You're kidding.
    Elroy: Nope.
    George: Then lie to me and say you're kidding.

Viewing 8 posts - 1 through 7 (of 7 total)

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