none
Upgrading from SQL Server Express 2005 to 2008 - lost all databases

    Question

  • When I upgraded my development environment from MSSQL 2005 Express to 2008 Express, I lost all my databases.  Since it's a development environment, I just rebuilt them all.

    Now I want to upgrade my production server, and losing all my data is NOT an option.  The instructions I found on Microsoft's sites don't have any instructions for prepping, archiving, or converting databases, they just explain how to install the new version over the old one.

    Is there anyone out there who can help me figure this out?


    I'd rather live with false hope than with false despair.
    Tuesday, January 05, 2010 5:27 PM

Answers

  • Bryan,

    There are a bunch of methods for doing the upgrade, in-place which you had already stated which you install over the old binaries in the directories, side by side in which you install a new instance of the the new version on the same server, and a migration where you migrate teh data from the old instance on the old server to a new instance on the new server.

    Other than the in-place upgrade, there are multiple methods of putting the databases where they belong and bringing them into the new instance. Before you do ANY upgrades you should make sure that you have a current tested backup of the databases. Depending on the size of the databases, smaller ones I like to do COPY ONLY full backups which won't interrupt the current backup process. Larger ones I like to go by the last tested good backup and make sure that we have all of the chain back to current before proceeding and that they AREN'T on the same server we are upgrading. You can do backup/restore to the new instance, you can also do detach/attach.

    If you are reusing the same server with the upgrade, I would make a backup (or have a good one standing by on some type of media - this includes any special startup flags, account info, etc) and install a new instance of the (new version) of sql server. Some people don't like doing it this way and to be honest if there is a good deal of custom code with hard paths I do in-place. However this allows for minimal downtime (unless reboots are in order) and you can actually synchronize the databases after bringing a copy of the older one up.

    There are hundreds of pages dedicated to upgrading sql server, if you give us an idea of how you want to do this we may be able to help you out a little more.

    Hope this helps,
    Sean
    • Proposed as answer by LekssEditor Tuesday, January 05, 2010 11:50 PM
    • Marked as answer by Bryan Valencia Monday, January 11, 2010 10:28 PM
    Tuesday, January 05, 2010 6:01 PM
  • Hi,

    I think using the  Upgrade from SQL Server 2000 or SQL Server 2005 option in SQL Server 2008 installation wizard is the easiest way to upgrade your SQL Server instance. When you upgrade SQL Server, the previous SQL Server instance will be overwritten and will no longer exist on your computer, and all databases in the previous instance are preserved. Therefore, you don’t need to uninstall and reinstall. Please confirm your upgrading steps according to the following document: http://technet.microsoft.com/en-us/library/ms144267.aspx

    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    • Marked as answer by Bryan Valencia Monday, January 11, 2010 10:28 PM
    Thursday, January 07, 2010 8:02 AM
    Moderator

All replies

  • Bryan,

    There are a bunch of methods for doing the upgrade, in-place which you had already stated which you install over the old binaries in the directories, side by side in which you install a new instance of the the new version on the same server, and a migration where you migrate teh data from the old instance on the old server to a new instance on the new server.

    Other than the in-place upgrade, there are multiple methods of putting the databases where they belong and bringing them into the new instance. Before you do ANY upgrades you should make sure that you have a current tested backup of the databases. Depending on the size of the databases, smaller ones I like to do COPY ONLY full backups which won't interrupt the current backup process. Larger ones I like to go by the last tested good backup and make sure that we have all of the chain back to current before proceeding and that they AREN'T on the same server we are upgrading. You can do backup/restore to the new instance, you can also do detach/attach.

    If you are reusing the same server with the upgrade, I would make a backup (or have a good one standing by on some type of media - this includes any special startup flags, account info, etc) and install a new instance of the (new version) of sql server. Some people don't like doing it this way and to be honest if there is a good deal of custom code with hard paths I do in-place. However this allows for minimal downtime (unless reboots are in order) and you can actually synchronize the databases after bringing a copy of the older one up.

    There are hundreds of pages dedicated to upgrading sql server, if you give us an idea of how you want to do this we may be able to help you out a little more.

    Hope this helps,
    Sean
    • Proposed as answer by LekssEditor Tuesday, January 05, 2010 11:50 PM
    • Marked as answer by Bryan Valencia Monday, January 11, 2010 10:28 PM
    Tuesday, January 05, 2010 6:01 PM
  • Thank you for this.
    I am talking about a single server, which is my on-line web server and email server as well.
    I am a small one-person shop.
    As I said, when I upgraded my Dev workstation, I was expecting the upgrade to take care of the databases automatically, and was disappointed that it didn't.  But again, what I had in the office was a lot of scratch data so all was well.

    So here are the pertinent details.

    • single server (I only have one server in the colo site)
    • up to 2 hours of downtime is acceptable for database services
    • There are NO large databases
    If I boil down what you said to a procedure, it seems like it would be this.

    1. back up the databases (copy only, full)
    2. detach the old databases
    3. uninstall SQL Server 2005 Express
    4. install SQL Server 2008 Express
    5. restore the backups as new databases
    6. delete/archive the old main database files.
    The reason I am uninstalling 2005 first is that there is a space issue on the C: volume, and MS doesn't offer the option of installing it anywhere else.


    I'd rather live with false hope than with false despair.
    Wednesday, January 06, 2010 7:27 PM
  • Hi,

    I think using the  Upgrade from SQL Server 2000 or SQL Server 2005 option in SQL Server 2008 installation wizard is the easiest way to upgrade your SQL Server instance. When you upgrade SQL Server, the previous SQL Server instance will be overwritten and will no longer exist on your computer, and all databases in the previous instance are preserved. Therefore, you don’t need to uninstall and reinstall. Please confirm your upgrading steps according to the following document: http://technet.microsoft.com/en-us/library/ms144267.aspx

    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    • Marked as answer by Bryan Valencia Monday, January 11, 2010 10:28 PM
    Thursday, January 07, 2010 8:02 AM
    Moderator