Difference between revisions of "How to change microsoft sql edition"
From Hostek.com Wiki
(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.
- Install new instance
- Connect to old instance
- backup databases
- set databases offline
- 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 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)
- At this point, all databases have been restored to the new sql instance and all logins should be imported and working
- Stop both (old and new) instances of SQL Server
- Use the 'SQL Server Configuration Manager' to edit the 'TCP/IP' settings for the new instance of SQL.
- Under the 'IP ALL' section, change 'Dynamic Port' from '0' to (blank)
- Change the Static Port to '1433'
- Start the new instance of SQL Server (With the TCP/IP changes, this is now the default instance)
- Test any sites or connections on the server to verify they are working
- Set the old instance to 'Disabled' in the service manager