Warning: this method of changing SQL Server’s default collation is not supported by Microsoft
The issue was an instance of SQL Server was installed with the default collation (SQL_Latin1_General_CP1_CI_AS), and the software vendor was requesting that the default collation be set to ‘SQL_Latin1_General_CP850_BIN2’. With our security already setup in the Master database for this instance, we did not want to reinstall SQL Server or have the Master database rebuilt (Microsoft’s recommended resolution).
Note: example below done on virtual edition of SQL Server 2016 Developer edition and not a production system.
Below is the steps taken to change the default collation for the SQL Server Instance, tested to work as far back as SQL Server 2005.
1. In a SQL Server query window, check what is the current SQL Server’s default collation.
SELECT SERVERPROPERTY('COLLATION') AS 'DEFAULT COLLATION';
In this example, the installation of SQL Server 2016 Developer edition defaulted to ‘SQL_Latin1_General_CP1_CI_AS’ for this instance.
2. Make backups of all the databases on the instance of SQL Server being worked on. Important to verify that all the databases were backed up before continuing. The best way to backup, if the server is virtualized, is to snapshot the server.
3. When the collation is changed for the instance, all attached databases will also have its collation changed. Check the collation of all the databases on the instance. Detach any database that the collation should not be changed to the new default for the SQL Server instance.
select name, collation_name from sys.databases;
4. In a SQL Server query window, find the root directory where the SQL Server executable file and save for step 7.
declare @rootdir nvarchar(500) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @rootdir OUTPUT SELECT @rootdir + '\binn' as 'RootDirectoryPath'
Thanks to Sandeep Arora for providing the query listed above.
5. Shutdown the instance of SQL Server.
6. Open a command prompt in Administrator mode. On Windows Server 2012 R2, right clicking on the Windows Start button will offer the Administrative Command Prompt in the menu. If the command prompt is not in the administrator mode, a permission error may occur when attempting to start the SQL Server instance.
7. Change the root directory to where SQL Server’s executable exists in the directory found in step 4.
8. Start SQL Server in the command prompt with with the switches listed below:
sqlservr -m -T4022 -T3659 -s"<instance name>" -q"<new collation>"
-m — starts SQL Server in single user mode and does not let other’s users connect
-s — specifics the instance of SQL Server that is being started (use MSSQLSERVER if not using a named instance)
-T4022 — has SQL Server not run any stored procedures that have been configured as part of the startup process
-T3659 — logs all errors to error log during the SQL Server startup
-q — the undocumented switch that changes the default collation for the SQL Server instance
This will run SQL Server in a single user node while displaying the error log in the command prompt window.
If there are databases attached to the instance, entries into the error log will be displayed as the indexes for each database are altered. This processes may take minutes or hours depending on the number and size of databases.
9. Stopping SQL Server when finished, type ctrl-c to stop and type a Y and click the enter button.
Important: do not stop until the following two lines are displayed:
The default collation was successfully changed.
Recovery is complete. This is an informational message only. No user action is required.
If the SQL Server instance is stopped before it has a chance to update all the indexes in the attached databases, it will revert back to the original default collation. A lesson this author learned the hard way.
10. Restart SQL Server normally, and verify the new default collation.
Also, verify that the databases collation have been altered.
If there are any detached databases, this would be the time to re-attach them.
Note about “Net Start” method to change the default collation:
If choosing to use the “net start” method of changing the instance’s default collation, it is best to detach all of the users databases before running this command. The problem the author found using this command, if not all the user databases are detached, there is the lack of notification when SQL Server is done changing the collation for all the user databases. If SQL Server is started before the collation change process is completed, the original collation will reverted back.
If using the “net start” method with attached user databases, monitor the errorlog though Windows Explorer. Do not stop the SQL Server Instance until the line appears: “Recovery is complete.”
