Resolving SQL Server Instance and Database Collation Discrepancies: Step-by-Step Guide

Changing an instance’s collation after installation means rebuilding the system databases, and that destroys everything in master. I did it on a migration where the instance collation didn’t match the databases I was restoring, and tempdb had become unusable as a result.

Microsoft’s Set or Change the Server Collation page lays out the full sequence, and it’s more than the rebuild on its own. You script out your user databases, export the data with something like bcp, drop all the user databases, rebuild master with /SQLCOLLATION, then recreate everything and import it back. I got out of the export and import steps because I was restoring from backups afterward anyway, but that’s the documented shape of it and it’s worth seeing before you decide this is the route you want.

Temp tables inherit tempdb’s collation, which comes from the instance. The same page lists what the server collation governs, including the “collation of the CHAR, VARCHAR, NCHAR, and NVARCHAR columns in system views, system functions, and the objects in tempdb (for example, temporary tables).” So a query joining a column in your database against a column in a #temp table is comparing two different collations, and it fails rather than doing something subtly wrong. JOIN, ORDER BY, and GROUP BY all started throwing collation conflict errors. Facing a strict deadline to get these applications migrated, I decided to rebuild the instance collation to match the new application databases.

I want to be clear this is the heavy option. Rebuilding replaces master, model and msdb with fresh copies, which means every login, every SQL Agent job, every linked server and every server-level setting is gone afterwards. The alternative is fixing collation per column or adding COLLATE DATABASE_DEFAULT to the joins that break, and if I’d had more than a deadline’s worth of time I might have gone that way instead. The preparation below is entirely about being able to put back what the rebuild removes.

Preparations

Run the following queries and copy the results to notepad or an Excel spreadsheet.

-- View system configuration
SELECT * FROM sys.configurations;

-- View server properties
SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ResourceVersion') AS ResourceVersion,
SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,
SERVERPROPERTY('Collation') AS Collation;

-- View database files locations
SELECT name, physical_name AS current_file_location
FROM sys.master_files
WHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));

Before moving forward, I backed up everything that would be lost due to the rebuild, including SQL logins, jobs, alerts, linked servers, and permissions. Ensuring recent backups of all databases is important—future you will thank past you for this step.

SQL Server Configuration

To save the current SQL Server settings, I ran the following script and copied the results to a file:

-- Enable advanced options
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

DROP TABLE IF EXISTS #ConfigSettings;

-- Create a temp table to store the configuration settings
CREATE TABLE #ConfigSettings (
    name NVARCHAR(35),
    minimum INT,
    maximum INT,
    config_value INT,
    run_value INT,
    config_script NVARCHAR(MAX)
);

-- Insert the configuration settings into the temp table
INSERT INTO #ConfigSettings (name, minimum, maximum, config_value, run_value)
EXEC sp_configure;

-- Update the temp table to add the script column
UPDATE #ConfigSettings
SET config_script = 'EXEC sp_configure ''' + name + ''', ' + CAST(config_value AS NVARCHAR(10)) + ';';

-- Select the results
SELECT * FROM #ConfigSettings;

This script captures the SQL Server settings that will be wiped out during the rebuild. Save the results to a folder dedicated to the rebuild process.

Script Logins and Permissions

SQL Logins need to be extracted, along with users and roles. To accomplish this, I used the following script to create two stored procedures that will help me export the logins. I got these stored procedures from Microsoft’s official website which walks you through this step.

EXEC sp_help_revlogin

I then executed the stored procedure to generate a script to create SQL Logins, which I saved to my REBUILD folder:

Script Msdb Objects

All of the steps outlined in this section are optional, so only complete the steps you need. In my case, I needed to copy the SQL Jobs, Alerts, and Operators. However, if you have Linked Servers, Endpoints, Extended Events, or db_mail. Now would be the time to copy those configurations. I did this by expanding the SQL Server Agent in the Object Explorer, selecting the Jobs folder, then hitting the F7 key to bring up the Object Explorer Details. From here I could highlight the jobs I wanted and script the CREATE TO to a file in my REBUILD folder. I then repeated these steps for the Alerts and Operators.

Script Master Objects

Again, all of the following steps in the section are optional and depend on how the SQL Server is used. I always install sp_WhoIsActive and similar stored procedures to the master database. Just right click on the objects you need to keep and script them, individually or all together, to the REBUILD folder.

Rebuilding the System Databases

Once I completed those steps, I moved on to rebuilding the system databases. This step will require downtime. Once situated, login to the database server and find your setup.exe file for SQL Server. I found mine in C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\SQL2022. I opened a CMD prompt and changed the directory to the one listed above. I then ran the following CMD

setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=<ListOfAdminAccounts> /SAPWD=<YourStrongPassword> /SQLCOLLATION=<NewInstanceCollation>

Optionally, Microsoft has a list of additional parameters you can specify in your command.

After the command completed, I logged back into SQL Server via SSMS and verified the database collation with:

SELECT name, collation_name FROM sys.databases WHERE database_id < 5

With that behind me, I was able to run the CREATE scripts I generated earlier, as well as the configuration script.

I needed to add the following to the top of my sp_configure script to enable Advanced Options.

EXEC sp_configure 'show advanced options', '1'
GO
RECONFIGURE
GO

After that was finished, I disabled Advanced Options again.

EXEC sp_configure 'show advanced options', '0'
GO
RECONFIGURE
GO

Finally, I restored all databases from the most recent backup, completing the migration.

Looking back, the rebuild itself was the easy part. It’s a single command and it finishes in a few minutes. What took the time was everything in the Preparations section, and I’d do that part more carefully next time, because the things you forget to write down are things you only discover are missing when someone tells you their job didn’t run. I don’t have a clean answer for how to be sure you captured everything, which is the part of this that still bothers me.