locked
Accidentally upgraded database to 706 RRS feed

  • Question

  • A web project I am working uses an SQL 2008 database that is distributed along with the app so it can be setup to run on several OS systems including XP. However, during an editing session using Visual Studio 2012 for web the master copy of the 665 version was inadvertently upgraded to 706 when Visual Studio loaded the project ... remember just say no to version upgrades :) ... now the XP machines that have SQL 2008 will not load the distributed database and produce an error saying that the database is version 706 and the machine's server only supports 665 and earlier.

    My question is this:

    Is there a way to recreate a 665 database on an 2008 SQL server version and then "import" the tables and the data to the 665 version from the 706 version without automatically upgrading the 665 version to 706? I have tried creating a blank database on an SQL 2008 server and then attaching both the 665 and 706 databases to the 2012 server and exporting from the 706 version to the blank 665 database. However, each time the import is complete the new database somehow changes to version 706, and the SQL server 2008 server will no longer load it even though it was created on the 2008 server. I am careful to select Native Client 10 for the import to the 665 version and Native Client 11 for export from the 706 version, but this does not seem to help.

    Any suggestions? I am stumped!

    David

    Wednesday, November 20, 2013 6:08 AM

Answers

  • Hello David,

    There is no direct way to go back to the old version.

    But yes, you can create a new database in version 2008 and copy all tables/view/etc from the newer SQL Server to the 2008 version. For this you can use SSIS (if available) or the SSMS Import/Export wizard.

    But: If you are using "User instances" of SQL Server, then take care in which instance you will start the database; if it a newer version, SQL Server will automatically upgrade the database to the never version without any feedback!

    And don't forget to backup your databases frequently.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Edited by Olaf HelperMVP Wednesday, November 20, 2013 6:46 AM
    • Proposed as answer by Allen Li - MSFT Thursday, November 21, 2013 1:59 AM
    • Marked as answer by rdaviddd Friday, November 22, 2013 6:40 AM
    Wednesday, November 20, 2013 6:45 AM

All replies

  • Hello David,

    There is no direct way to go back to the old version.

    But yes, you can create a new database in version 2008 and copy all tables/view/etc from the newer SQL Server to the 2008 version. For this you can use SSIS (if available) or the SSMS Import/Export wizard.

    But: If you are using "User instances" of SQL Server, then take care in which instance you will start the database; if it a newer version, SQL Server will automatically upgrade the database to the never version without any feedback!

    And don't forget to backup your databases frequently.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Edited by Olaf HelperMVP Wednesday, November 20, 2013 6:46 AM
    • Proposed as answer by Allen Li - MSFT Thursday, November 21, 2013 1:59 AM
    • Marked as answer by rdaviddd Friday, November 22, 2013 6:40 AM
    Wednesday, November 20, 2013 6:45 AM
  • Thanks!

    So if an older version database is loaded or attached by a newer version of SQL it upgrades it "automatically" without any feedback. I was thinking it was something like that. The default database is "preloaded" with specific data needed to initialize the application and then "augmented" with data by the application that accesses it from what ever OS it is running under, So hence the need to keep the original database a lower version since I don't believe the new versions of SQL will run under XP. It appears my mistake was in recreating the older version and than attaching both to a newer version SQL server to export the data instead of using the Import/Export Wizard.

    I guess we will need to keep the two databases completely separated and make sure the original 665 default version is never accessed from a newer version SQL server being used by a developer. That way the older version will not be changed in any way until it is actually employed on a machine and then the local machine SQL server can deal with the what version it is and upgrade it if needed ... and also keep better backups! :)

    Friday, November 22, 2013 6:13 AM