Difference between revisions of "How to change microsoft sql edition"

From Hostek.com Wiki
Jump to: navigation, search
(Created page with "In case you need to upgrade or downgrade a Microsoft SQL edition, here are the steps to successfully complete this process. # Install new instance # Connect to old instance #...")
 
Line 7: Line 7:
 
# Restore databases to new instance
 
# Restore databases to new instance
 
# On old instance, use sp_help_revlogin to generate a script to import sql logins into the new instance (https://support.microsoft.com/en-us/kb/918992)
 
# On old instance, use sp_help_revlogin to generate a script to import sql logins into the new instance (https://support.microsoft.com/en-us/kb/918992)
 +
# On the new instance, Open a new Query Editor window, and then run the output script from above
 
# On new instance, Run "EXEC sp_change_users_login 'Auto_Fix', 'USERNAME'" on each user (replacing USERNAME with the user's actual name)
 
# On new instance, Run "EXEC sp_change_users_login 'Auto_Fix', 'USERNAME'" on each user (replacing USERNAME with the user's actual name)
 
# At this point, all databases have been restored to the new sql instance and all logins should be imported and working
 
# At this point, all databases have been restored to the new sql instance and all logins should be imported and working

Revision as of 20:40, 26 November 2016

In case you need to upgrade or downgrade a Microsoft SQL edition, here are the steps to successfully complete this process.

  1. Install new instance
  2. Connect to old instance
    1. backup databases
    2. set databases offline
  3. Restore databases to new instance
  4. On old instance, use sp_help_revlogin to generate a script to import sql logins into the new instance (https://support.microsoft.com/en-us/kb/918992)
  5. On the new instance, Open a new Query Editor window, and then run the output script from above
  6. On new instance, Run "EXEC sp_change_users_login 'Auto_Fix', 'USERNAME'" on each user (replacing USERNAME with the user's actual name)
  7. At this point, all databases have been restored to the new sql instance and all logins should be imported and working
  8. Stop both (old and new) instances of SQL Server
  9. Use the 'SQL Server Configuration Manager' to edit the 'TCP/IP' settings for the new instance of SQL.
    1. Under the 'IP ALL' section, change 'Dynamic Port' from '0' to (blank)
    2. Change the Static Port to '1433'
  10. Start the new instance of SQL Server (With the TCP/IP changes, this is now the default instance)
  11. Test any sites or connections on the server to verify they are working
  12. Set the old instance to 'Disabled' in the service manager