SQLServerCentral Article

Change Server Collation in SQL Server 2008



You have a SQL staging environment with a different collation than the SQL production environment. When you publish objects from staging to production you can have different results.

For example you have a case insensitive collation (SQL_Latin1_General_CP1_CI_AS) in staging and a case sensitive collation (SQL_Latin1_General_CP1_CS_AS) in production. Running this script in the 2 environments gives a different result:

DECLARE @strB VARCHAR(10) SET @strA = 'abcdefghij'
SET @strB = 'ABCDEFGHIJ' IF @strA = @strB
PRINT 'Case Insensitive Collation'
PRINT 'Case Sensitive Collation'

This inconsistent situation can have serious consequences when deploying scripts from staging to production. An equal environment is a requisite to prevent this kind of problems.

In this example the staging collation is Latin1_General_CI_AS and the production collation is SQL_Latin1_General_CP1_CI_AS. We will change the staging collation to SQL_Latin1_General_CP1_CI_AS.

To learn more about SQL Server 2008 collation please visit http://msdn.microsoft.com/en-us/library/ms144260.aspx


First we have to run some scripts to minimize manual actions. The next step is rebuilding the master database to change the server to the correct collation. After that we have to configure all the databases, logins and settings.

Run this statement that creates a script to detach all the user databases. Save the result of this script as detachdbs.sql

SELECT 'EXEC sp_detach_db ''' + name + ''', ''false''' FROM sysdatabases where name not in ('master','tempdb','model','msdb','northwind')

Now we have to make a script to attach all the user databases, therefore run following script and save the result to attachdbs.sql

DECLARE @dbname varchar(50)
DECLARE @dbname_prev varchar(50)
DECLARE @file varchar(150) SET @dbname_prev = ' '
FOR SELECT a.name, b.filename FROM sysdatabases a inner join sysaltfiles b on a.dbid = b.dbid where a.name not in ('master','tempdb','model','msdb','northwind', 'pubs') order by 1 OPEN cAttach FETCH NEXT FROM cAttach INTO @dbname, @file
WHILE (@@fetch_status = 0)
IF (@dbname_prev <> @dbname)
IF (@dbname_prev <> ' ') PRINT 'FOR ATTACH ';
PRINT 'CREATE DATABASE [' + @dbname + '] ON';
PRINT '( FILENAME = ''' + rtrim(@file) + ''') ';
ELSE PRINT ', ( FILENAME = ''' + rtrim(@file) + ''') ';
SET @dbname_prev = @dbname
FETCH NEXT FROM cAttach INTO @dbname, @file
CLOSE cAttach

The next step is making a script to create all SQL Logins.

The master database is the repository of the logins. After rebuilding the master database the changes made after installation are lost. Use this article to script the logins http://support.microsoft.com/kb/918992. Save the result as Logins.sql

EXEC dbo.sp_help_revlogin 

Also the server role credentials located in de master database are lost after rebuilding de master database. Run this script and save it as LoginRoles.sql

select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''sysadmin''' as script from syslogins
where sysadmin = 1
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''securityadmin''' as script from syslogins
where securityadmin = 1
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''serveradmin''' as script from syslogins
where serveradmin = 1
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''setupadmin''' as script from syslogins
where setupadmin = 1
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''processadmin''' as script from syslogins
where processadmin = 1
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''diskadmin''' as script from syslogins
where diskadmin = 1
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''dbcreator''' as script from syslogins
where dbcreator = 1
select 'EXEC master..sp_addsrvrolemember @loginame = N''' + [name] + ''', @rolename = N''bulkadmin''' as script from syslogins
where bulkadmin = 1

To make sure you don't lose anything you can export and script all the objects in the msdn database. So export SSIS packages, scripts jobs, linked servers, and anything else you can think of.

The preparation is done so we can start with the real stuff. To detach all the user databases you have to make sure everyone is disconnected. You also need to stop the application services that connect to the staging environment.

Run the prepared script detachdbs.sql to detach the databases.

Now that all the user databases are detached we can rebuild the master database to change the server collation. First make sure you bring the SQL Server service offline. Locate the Setup Bootstrap folder, which is usually C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release. You need to run this command, but be sure you use the correct instance name, your domain-account and a new sa password.


After a successful installation the collation of the SQL Instance is changed to SQL_Latin1_General_CP1_CI_AS. Please check it before you continue. (Right-click on the instance in the Object Explorer => Properties => 'General' page => Server Collation)

Now we can run the prepared scripts:

  • Attach all the user databases with attachdbs.sql
  • Create SQL Logins with Logins.sql
  • Create server roles for the logins with LoginRoles.sql

You also need to reconfigure your SQL Instance because all configurations are set to default after rebuilding the master database. To learn more how to configure your SQL instance please visit http://technet.microsoft.com/en-us/library/ms189631.aspx

The last step is changing the collation of the user databases. The collation of the attached user databases didn't change so you have to run the result of this script to change it.

Note: You need an exclusive lock on the database to do this

select  'ALTER DATABASE [' + [name] + '] COLLATE SQL_Latin1_General_CP1_CI_AS' from sys.databases
where collation_name <> 'SQL_Latin1_General_CP1_CI_AS'

I hope this is a useful article for some people. Please let me know your comments!

Kevin Sermijn


3.61 (18)




3.61 (18)