title: Changing the collation of a SQL Server 2012 database
body: <p><strong>Alter Collation</strong></p>
  
  <p>I need to change the collation of one of our databases on a particular server from <code>Latin1_General_CI_AS</code> to <code>SQL_Latin1_General_CP1_CI_AI</code> so that it matches the rest of our databases.</p>
  
  <p><img src="https://i.stack.imgur.com/qRuyV.png" alt="Alter Collation Dialog - SQL Server 2012 R2"></p>
  
  <p><strong>The Problem</strong></p>
  
  <p>However, when I attempt to do this, I get the following error:</p>
  
  <blockquote>
    <p>ALTER DATABASE failed. The default collation of database 'XxxxxXxxxxx' cannot be set to SQL_Latin1_General_CP1_CI_AI. (Microsoft SQL Server, Error: 5075)</p>
  </blockquote>
  
  <p><strong>My Research</strong> </p>
  
  <p>My googling on the topic has revealed a number of articles which indicate that I need to export all the data, drop the database, re-create it with the correct collation, then re-import the data.</p>
  
  <p>For example: <a href="https://stackoverflow.com/questions/7371234/problem-with-database-collation-change-sql-server-2008">Problem with database collation change (SQL Server 2008)</a></p>
  
  <p>Obviously this is a significant task, especially since primary-foreign key relationships must be preserved, and our database is quite large (over ten million data rows).</p>
  
  <p><strong>My Question</strong></p>
  
  <p>Is there a way to change the collation of an existing SQL Server 2012 database which does not require exporting and re-importing all the data? </p>
  
  <p>Alternatively, is there some tool or script(s) which can automate this process in a reliable manner?</p>