MS SQL - amending collation

Question

Hi,

I have some queries on changing the collation from "SQL_Latin1_General_CP1_CI_AS" to "SQL_Latin1_General_CP1_CS_AS" i.e. case insensitive to case sensitive.

I tried :

• exporting the data from the original DB
• create a BACPAC of the original DB
• change the collation
• import the data from the original DB to this new DB

It did not work. I was unable to change the collation in the BACPAC DB.

How can I go about creating a new DB while preserving all the settings, configuration, etc of the original DB and just change the collation and thereafter import the data back?

Is that even possible? I am not very good in MS SQL.

Thanks & regards
James

Answer 1

As I will be "playing" around, what is the best way to "protect" the actual operational database - detaching or taking it offline?

You should absolutely not make any experiments on the live database. Restore a backup on a test instance of SQL Server. Make sure that the instance has the disk space for at least two copies of the databases.

We can try the below first, as this is a smoother way. But it may not work out.

First step, run this query:

   SELECT 'ALTER TABLE ' + quotename(s.name) + '.' + quotename(t.name) + 'ADD ' +   
          CASE WHEN cc.is_system_named = 1 THEN ''  
               ELSE ' CONSTRAINT ' + quotename(cc.name)  
          END + ' CHECK (' + cc.definition + ')'  
   FROM   sys.tables t  
   JOIN   sys.schemas s ON t.schema_id = s.schema_id  
   JOIN   sys.check_constraints cc ON cc.parent_object_id = t.object_id  
   ORDER  BY s.name, t.name, cc.name  

Copy the output and save it to a file CHECK_CONSTRAINTS.sql. On top of the file add these two lines:

   USE <YourDBNameHere>  
   go  

Next, run this query:

   SELECT 'ALTER TABLE ' + quotename(s.name) + '.' + quotename(t.name) +   
          'DROP CONSTRAINT ' + quotename(cc.name)  
   FROM   sys.tables t  
   JOIN   sys.schemas s ON t.schema_id = s.schema_id  
   JOIN   sys.check_constraints cc ON cc.parent_object_id = t.object_id  
   ORDER  BY s.name, t.name, cc.name  

Copy the result to a query window and run it.

Now, make the collation change:

   ALTER DATABASE YourDB COLLATE SQL_Latin1_General_CP1_CS_AS  

If this completes successfully, open CHECK_CONSTRAINTS.sql and run the script.

If you have come so far without errors, run this query again:

SELECT o.type, c.collation_name, COUNT(*)  
FROM   sys.objects o  
JOIN   sys.columns c ON o.object_id = c.object_id  
WHERE  c.collation_name IS NOT NULL  
GROUP  BY o.type, c.collation_name  
ORDER BY o.type, c.collation_name  

If you get any error messages, abort the operation at that point and restore the database to the original state.

Share any messages you get, as well as the output as final query as screenshots. (To give us a higher fidelity in what we are seeing.)