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

Recently I ran into a situation while migrating a database to a new SQL Server instance where the instance collation was different from the database collation. JOIN, ORDER BY, GROUP BY, and other operations started failing with collation conflict errors. I had a strict deadline to get these applications migrated, so I decided to rebuild the instance collation to match the new application databases.

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 skipped the export and import steps because I was restoring the databases from backups afterward anyway, but it’s worth reading the documented sequence 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).” That’s why the errors show up where they do. Temp tables get the instance’s collation, so comparing a column in your database with a column in a #temp table means comparing two different collations.

This is the heavy option. Microsoft’s rebuild system databases page says master, model, msdb, and tempdb are “dropped and recreated” and that user modifications to them are lost. That means logins, SQL Agent jobs, linked servers, and server-level settings all have to be put back afterward. The lighter alternative is fixing the queries instead. The COLLATE clause supports database_default, which can make a temp table column use the current database’s collation instead of tempdb’s. The preparation below is all 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. I also made sure I had recent backups of every database.

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 exported along with users and roles. I created the two stored procedures from Microsoft’s page on transferring logins, then ran this to generate a script that recreates the logins, which I saved to my REBUILD folder:

EXEC sp_help_revlogin

Script Msdb Objects

Everything in this section is optional, so only do the parts you need. In my case, I needed to copy the SQL Jobs, Alerts, and Operators. If you have Linked Servers, Endpoints, Extended Events, or Database Mail, now is the time to copy those configurations too. I expanded SQL Server Agent in Object Explorer, selected the Jobs folder, and pressed F7 to bring up Object Explorer Details. From there I could highlight the jobs I wanted and script them as CREATE To a file in my REBUILD folder. I then repeated these steps for the Alerts and Operators.

Script Master Objects

This section is optional too and depends on how the server is used. I always install sp_WhoIsActive and similar stored procedures in the master database. 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 requires downtime. I logged in to the database server and found the setup.exe for SQL Server, which for me was in C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\SQL2022. I opened a CMD prompt, changed to that directory, and ran:

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

Microsoft has a list of additional parameters you can add to the 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.