How to change SQL Server Collation change and Database Collation for Default and Named Instance in SQL Server

To rebuild system databases for an instance of SQL Server for Server Collation change

Insert the SQL Server 2014 installation media into the disk drive, or, from a command prompt, change directories to the location of the setup.exe file on the local server. The default location on the server is C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Release.

To see logs of summary after execute command below location

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log

From a command prompt window, enter the following command. Square brackets are used to indicate optional parameters. Do not enter the brackets. When using a Windows operating system that has User Account Control (UAC) enabled, running Setup requires elevated privileges. The command prompt must be run as Administrator.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

Default instance

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=Domainname\Administrator /SAPWD= Pas$w0rd /SQLCOLLATION=SQL_Latin1_General_CP850_BIN


Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=Domainname\Administrator  /SAPWD= user$123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS


Named Instance


Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQLEXPRESS /SQLSYSADMINACCOUNTS=Domain_name\Administrator  /SAPWD=user$123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

some time we will get error The Windows account admin-pc\administrator does not exist and cannot be provisioned as a SQL Server system administrator we need to use "" on account as below

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQLEXPRESS /SQLSYSADMINACCOUNTS="Domain-name\Administrator " /SAPWD=user$123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS




Pre Implementation Steps


Step 1:

Take the Backup of system and user databases and logins using sp_helprevlogin script with privilges and linked servers

Implementation Steps:

Step 1:

Log in to the domainname/administrator account to the machine/RDP which needs to change server collation

Step 2: verify the instance running default (MSSQLServer) or Named instance from SQL Configuration manager

Step 3:

Open explorer and navigate to the Setup.exe of SQL Server from following location

C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Release (or)

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2 (or)

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012


Step 4:

Open command prompt from Run command from start menu


Type cd with path like below and press enter

C:\Users\Documents>CD C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2


Step 5: Once got into the above location from command prompt

type below command press enter

Default instance

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=Domainname\Administrator /SAPWD= Pas$w0rd /SQLCOLLATION=SQL_Latin1_General_CP850_BIN

Named Instance

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQLEXPRESS /SQLSYSADMINACCOUNTS=domain_name\administrator /SAPWD=user$123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

some time we will get error The Windows account admin-pc\administrator does not exist and cannot be provisioned as a SQL Server system administrator we need to use "" on account as below

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQLEXPRESS /SQLSYSADMINACCOUNTS="domain-name\administrator" /SAPWD= user$123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

ex:

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2>Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQLEXPRESS /SQLSYSADMINACCOUNTS="domain-name\administrator" /SAPWD= user$123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

Step 6:

Now once finished above command command prompt will come to another line with blank


Step 7:

We can check the logs from below location from summary.txt file


C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Summary.txt

To see error log below location dont confuse

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log


To change collation of Database

ALTER DATABASE dbname
COLLATE CollationName;

Ex

USE master;
GO
ALTER DATABASE dbname
COLLATE French_CI_AS ;
GO

Note;
if you change the database collation you need to check whether column of table collation also changed or not. If not you need to change one by one use below script

USE databasename
GO
ALTER TABLE [dbo].[col_test]
ALTER COLUMN Text NVARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL

before change collation of column you need to verify following

You cannot change the collation of a column that is currently referenced by any one of the following:

A computed column

An index

Distribution statistics, either generated automatically or by the CREATE STATISTICS statement

A CHECK constraint

A FOREIGN KEY constraint

When you work with tempdb, the COLLATE clause includes a database_default option to specify that a column in a temporary table uses the collation default of the current user database for the connection instead of the collation of tempdb.

see for more

https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-column-collation?view=sql-server-2017


---------------------Verify the collation setting for database
SELECT name, collation_name
FROM sys.databases
WHERE name = N'dbname';


---------------------Alter the collation setting for database before verify existing --------------------

Declare @TableName Nvarchar(Max)
Declare @CollationName Nvarchar(Max)
Declare @ColumnName Nvarchar(Max)
Declare @SQLText Nvarchar(Max)
Declare @DataType Nvarchar(Max)
Declare @CharacterMaxLen Nvarchar(Max)
Declare @IsNullable Nvarchar(Max)


set @CollationName='SQL_Latin1_General_CP850_BIN'

DECLARE  MyTableCursor CURSOR FOR

SELECT TABLE_NAME
             from INFORMATION_SCHEMA.TABLES


   --Verify whether given collation existing on column or not
SELECT Distinct TABLE_NAME,COLLATION_NAME from INFORMATION_SCHEMA.Columns where COLLATION_NAME='SQL_Latin1_General_CP850_BIN'

OPEN MyTableCursor

FETCH NEXT FROM MyTableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE MyColumnCursor Cursor
        FOR
        SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
            IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS
            WHERE table_name = @TableName AND  Data_Type LIKE '%char%'
            OR Data_Type LIKE '%text%' AND COLLATION_NAME <> @CollationName
            ORDER BY ordinal_position
        Open MyColumnCursor

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType,
              @CharacterMaxLen, @IsNullable
        WHILE @@FETCH_STATUS = 0
            BEGIN

            SET @SQLText = 'ALTERTABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' +
              @DataType + '(' + CASE WHEN @CharacterMaxLen = -1 THEN 'MAX' ELSE @CharacterMaxLen END +
              ') COLLATE ' + @CollationName + ' ' +
              CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END

           

            PRINT @SQLText

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType,
              @CharacterMaxLen, @IsNullable
        END
        CLOSE MyColumnCursor
        DEALLOCATE MyColumnCursor

PRINT @SQLText

FETCH NEXT FROM MyTableCursor INTO @TableName

END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor

Ref;
https://docs.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-2014

https://www.sanssql.com/2012/10/how-to-change-server-collation.html

https://www.sanssql.com/2013/10/change-sql-server-collation-without.html

Named instance error

https://dba.stackexchange.com/questions/143246/error-when-trying-to-install-sql-server-2012-ent-in-to-a-server-core-2008-vm