none
Downgrade SQL Server 2008 Enterprise to SQL Server2008 Standard Edition

    Question

  • I would like to know from fourm users, what are the necessary steps I would take care for above mentioned activity and also please let me know best suitable option for Production.

     

    SNS

    Saturday, June 18, 2011 3:02 AM

Answers

  • You can't do in-place downgrade. You need to go with backup -> uninstall ent -> install std -> bring it to same patch -> restore databases approach.


    You need to make sure that there is not enterprise only feature used else you can't restore database on standard.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Balmukund's answer above is the Microsoft supported route for doing a downgrade of an instance of SQL Server.  However, before you do this, you need to query the sys.dm_db_persisted_sku_fearures DMV in each of your user databases to make certain that you don't have any Enterprise features in use that would block your ability to restore a database onto Standard Edition.

    No matter what you are going to have to do an uninstall and reinstall of the SQL Server instance to downgrade the SKU.  However, you can save yourself some time and the headache of trying to restore the system databases if you are careful about what you do.  I have done a couple of SKU downgrades in the past and the easiest way to do it, and I am not saying this is the Microsoft supported way but that it works if done correctly, is to:

    1. Take a good backup of all of your databases (system and user).  
    2. Run SELECT @@VERSION and note the specific build number of SQL Server that you are currently on.
    3. Shut down your existing instance of SQL Server.  
    4. Copy the master, model, and msdb database files (both mdf and ldf), don't move them copy them, from the current location to a new folder that you mark as readonly. 
    5. Uninstall SQL Server from the system.
    6. Reboot the server.
    7. Install SQL Server Standard Edition.
    8. Apply the necessary Service Pack and/or Cumulative Updates to bring the instance up to your previous build number.
    9. Shutdown SQL Server.
    10. Copy the master, model, and msdb database files (both mdf and ldf) from the folder you saved them in to the correct location for the new install and remove the readonly flag from the files, and change the file ACL's so that the SQL Service account has Full Control over the files.
    11. Startup SQL Server and if you did it correctly it will startup and be exactly where you were before you made any changes, with all of your user databaes online and you should be ready to let applications connect and resume operations.

    If you screw something up in the above, you still have your backups and you can run setup to rebuild the system databases and then go about following the Microsoft supported path for restoring the system databases and then user databases into the system to bring it online.  Essentially the file copy is no different that what would occur through attach/detach you are just doing it with system databases which is not supported, but it does work.  The key is to have your backups from before you do anything so you have the supported route available if you encounter an issue.  The only issue I have ever had doing this set of steps is that I didn't set the file ACL's correctly and the database engine threw Access Denied errors and failed to start until I fixed the ACL's correctly.  This can save you many hours of frustration and downtime trying to restore everything since the database files are already there and it is just some small copy operations to put them where you need them to be.

    Balmukund, feel free to beat me over the head for suggesting a non-supported path. :-)

     


    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!


    • Proposed as answer by Mr. Wharty Saturday, June 18, 2011 5:54 AM
    • Marked as answer by Peja Tao Monday, June 27, 2011 2:59 AM
    Saturday, June 18, 2011 5:09 AM
    Moderator
  • You can't do in-place downgrade. You need to go with backup -> uninstall ent -> install std -> bring it to same patch -> restore databases approach.


    You need to make sure that there is not enterprise only feature used else you can't restore database on standard.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Proposed as answer by Mr. Wharty Saturday, June 18, 2011 5:54 AM
    • Marked as answer by Peja Tao Monday, June 27, 2011 2:59 AM
    Saturday, June 18, 2011 3:27 AM
    Moderator
  • Hi Bala,
    I understood that once we match the SQL Server previous build number of current installation the user databases automatically lists in Managment studio?
    No need to restore or attach the databases? Please clarify this.
    Thanks!

    Siri 

    Uninstall/Install is the approach which me and Jonathan have suggested.
    If you are planning to restore/use system databases from enterprise to standard then you need to make sure that old build (enterprise) and new build (standard)

    Since server build is 10.50.1600 then no need to worry because there is no patch applied.

     

     


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Proposed as answer by Peja Tao Tuesday, June 21, 2011 1:52 AM
    • Marked as answer by Peja Tao Monday, June 27, 2011 2:58 AM
    Saturday, June 18, 2011 11:50 AM
    Moderator
  • Hello there.

    There's an article, called how to transfer logins and passwords between instances of SQL Server 2005 and SQL Server 2008 (it's a KB article - http://support.microsoft.com/kb/918992).

     

    best regards,
    Calin 

    • Proposed as answer by Peja Tao Monday, June 27, 2011 2:58 AM
    • Marked as answer by Peja Tao Monday, June 27, 2011 2:58 AM
    Tuesday, June 21, 2011 8:13 PM

All replies

  • I am planning to downgrade SQL2008 Enterprise to SQL2008 Standard Edition. Can you tell me which process is best suites for Production/?

    I'm planning to Detach and Attach database. Will it work? Please let me know as soon as possible?

     

    Saturday, June 18, 2011 3:20 AM
  • You can't do in-place downgrade. You need to go with backup -> uninstall ent -> install std -> bring it to same patch -> restore databases approach.


    You need to make sure that there is not enterprise only feature used else you can't restore database on standard.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Proposed as answer by Mr. Wharty Saturday, June 18, 2011 5:54 AM
    • Marked as answer by Peja Tao Monday, June 27, 2011 2:59 AM
    Saturday, June 18, 2011 3:27 AM
    Moderator
  • Sidx,
    Please avoid duplicate posts. I am merging this with your original post.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Saturday, June 18, 2011 3:35 AM
    Moderator
  • You can't do in-place downgrade. You need to go with backup -> uninstall ent -> install std -> bring it to same patch -> restore databases approach.


    You need to make sure that there is not enterprise only feature used else you can't restore database on standard.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Balmukund's answer above is the Microsoft supported route for doing a downgrade of an instance of SQL Server.  However, before you do this, you need to query the sys.dm_db_persisted_sku_fearures DMV in each of your user databases to make certain that you don't have any Enterprise features in use that would block your ability to restore a database onto Standard Edition.

    No matter what you are going to have to do an uninstall and reinstall of the SQL Server instance to downgrade the SKU.  However, you can save yourself some time and the headache of trying to restore the system databases if you are careful about what you do.  I have done a couple of SKU downgrades in the past and the easiest way to do it, and I am not saying this is the Microsoft supported way but that it works if done correctly, is to:

    1. Take a good backup of all of your databases (system and user).  
    2. Run SELECT @@VERSION and note the specific build number of SQL Server that you are currently on.
    3. Shut down your existing instance of SQL Server.  
    4. Copy the master, model, and msdb database files (both mdf and ldf), don't move them copy them, from the current location to a new folder that you mark as readonly. 
    5. Uninstall SQL Server from the system.
    6. Reboot the server.
    7. Install SQL Server Standard Edition.
    8. Apply the necessary Service Pack and/or Cumulative Updates to bring the instance up to your previous build number.
    9. Shutdown SQL Server.
    10. Copy the master, model, and msdb database files (both mdf and ldf) from the folder you saved them in to the correct location for the new install and remove the readonly flag from the files, and change the file ACL's so that the SQL Service account has Full Control over the files.
    11. Startup SQL Server and if you did it correctly it will startup and be exactly where you were before you made any changes, with all of your user databaes online and you should be ready to let applications connect and resume operations.

    If you screw something up in the above, you still have your backups and you can run setup to rebuild the system databases and then go about following the Microsoft supported path for restoring the system databases and then user databases into the system to bring it online.  Essentially the file copy is no different that what would occur through attach/detach you are just doing it with system databases which is not supported, but it does work.  The key is to have your backups from before you do anything so you have the supported route available if you encounter an issue.  The only issue I have ever had doing this set of steps is that I didn't set the file ACL's correctly and the database engine threw Access Denied errors and failed to start until I fixed the ACL's correctly.  This can save you many hours of frustration and downtime trying to restore everything since the database files are already there and it is just some small copy operations to put them where you need them to be.

    Balmukund, feel free to beat me over the head for suggesting a non-supported path. :-)

     


    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!


    • Proposed as answer by Mr. Wharty Saturday, June 18, 2011 5:54 AM
    • Marked as answer by Peja Tao Monday, June 27, 2011 2:59 AM
    Saturday, June 18, 2011 5:09 AM
    Moderator
  • Hi Bala,

     

    I understood that once we match the SQL Server previous build number of current installation the user databases automatically lists in Managment studio?

    No need to restore or attach the databases? Please clarify this.

    Thanks!

    Siri

     

     

    Saturday, June 18, 2011 8:35 AM
  • Same process is applicable for SQL Server 2008 R2 Enterprise to SQLSever 2008 R2 Standard Edtion?

    Exisiting build is

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)   Apr  2 2010 15:53:02   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 6.0 <X86> (Build 6001: Service Pack 1)

    so we need to installa same build Standard Edition right? Let me clarify-

    SNS

    Saturday, June 18, 2011 11:42 AM
  • Hi Bala,
    I understood that once we match the SQL Server previous build number of current installation the user databases automatically lists in Managment studio?
    No need to restore or attach the databases? Please clarify this.
    Thanks!

    Siri 

    Uninstall/Install is the approach which me and Jonathan have suggested.
    If you are planning to restore/use system databases from enterprise to standard then you need to make sure that old build (enterprise) and new build (standard)

    Since server build is 10.50.1600 then no need to worry because there is no patch applied.

     

     


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Proposed as answer by Peja Tao Tuesday, June 21, 2011 1:52 AM
    • Marked as answer by Peja Tao Monday, June 27, 2011 2:58 AM
    Saturday, June 18, 2011 11:50 AM
    Moderator
  •  

     

    Successfully migration was done but I lost logins. My experience is as per our above instructions 1 to 6 steps followed, While installation i've selected same location where i've installed my previous SQL 2008 Enterprise edition. Once after installation I happened to see all system and user data files on this location.

    I thought System databases are not overwritten with new installation and I've attached all user databases successfully.Finally noticed that I've missed logins.

    Can any one help me where went wrong on my exection part.

    SNS

    Tuesday, June 21, 2011 1:55 PM
  • Successfully migration was done but I lost logins. My experience is as per our above instructions 1 to 6 steps followed, While installation i've selected same location where i've installed my previous SQL 2008 Enterprise edition. Once after installation I happened to see all system and user data files on this location.

    I thought System databases are not overwritten with new installation and I've attached all user databases successfully.Finally noticed that I've missed logins.

    Can any one help me where went wrong on my exection part.

    SNS

    Tuesday, June 21, 2011 2:07 PM
  • If I ever meet any of you SQL Skills guys, beer's on me:-). As Paul says, well done, my boy - please correct me if I'm wrong:-)
    Tuesday, June 21, 2011 8:06 PM
  • Hello there.

    There's an article, called how to transfer logins and passwords between instances of SQL Server 2005 and SQL Server 2008 (it's a KB article - http://support.microsoft.com/kb/918992).

     

    best regards,
    Calin 

    • Proposed as answer by Peja Tao Monday, June 27, 2011 2:58 AM
    • Marked as answer by Peja Tao Monday, June 27, 2011 2:58 AM
    Tuesday, June 21, 2011 8:13 PM
  • What would happen if we went up to a higher patch level?  I need to downgrade 2008R2 SP1 EE to standard edition.  Can I install 2008R2 SP2 std. and still used this method?  Or do I have to install to SP1 and then apply SP2 after everything is up and running?

    I need to know ASAP as I'm planning to do a downgrade this weekend!

    Thursday, April 18, 2013 4:49 PM
  • System databases can't be reused across the version (b/w SP2 and SP1) If you are on SP2 Ent and you want to go to SP1 Std then you need to script out all system level objects (logins, linked server, configurations, jobs, alerts, operators and whatever you have defined in system databases)

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Thursday, April 18, 2013 5:50 PM
    Moderator
  • Just wondered if any one got the error "Some or all identity references could not be translated"

    I have a server windows server 2008 R2 standard. While doing an Enterprise to Standard downgrade, I have removed enterprise edition of SQL Server 2008, rebooted and while installing standard edition, I get the below error saying "You SQL server 2008 installation completed with failures. Some or all identity references could not be translated"

    Bit of background. This server has another express instance of SQL Server 2008. I have used local system account, network service as well as our general SQL service account, but I get the error towards the end of the installation and it fails saying “Some or all identity references could not be translated.”

    I am installing a named instance with the same name as before but fails. I have tried creating a test instance that fails too. Any ideas? I have also run the clean up utility but no joy.

    Cheers

    Faltaf

    Sunday, January 25, 2015 5:51 PM
  • I would advice you start a new thread and not piggyback to an old thread.

    I would also advice that you take the contents on the log directories of on the failed setups and upload it somewhere and post a link. You find them in C:\Program Files\Microsoft SQL Server\100\Setup Bootstap.

    Googling on the error message you got, it appears that this message is related to Windows permissions. That is, something may be broken in your Windows installation. But that is only speculation on my part.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, January 25, 2015 6:17 PM