title: Create a Duplicate Database - Different Collation
body: <p>My main question is how to copy a whole database having a English collation to one having a Greek collation? The source database is having all the tables and the related data. I have a clean Greek collation database. I want to copy <strong><em>everything</em></strong> from source to destination.</p>
  
  <p>One <a href="http://support.microsoft.com/kb/325335" rel="nofollow noreferrer">solution</a> that I found in a KB article, consisted of the following steps:</p>
  
  <ul>
  <li>Generating scripts for all the objects (not including the indexes, the triggers, the primary keys, the foreign keys, the default settings, and the constraints) in the source database. Here, the <code>Script collation</code> setting was set to <code>False</code>, so the scripts were created <strong>without</strong> the <code>collate</code> keyword.</li>
  <li>Running the scripts from step 1 on the destination database to create the objects with the destination database collation.</li>
  <li>Using DTS to transfer the data from the source database. This is where I am stuck right now.</li>
  </ul>
  
  <p>I used the <code>Export Data...</code> wizard of SQL Server 2005 for exporting data from source to destination database. I am having approximately 1500 tables in my database, so I ticked the <code>Optimize of many tables</code> checkbox in the wizard step. Apart from that, all the settings were default.</p>
  
  <p>I got an <a href="http://kirtangor.googlepages.com/ssis-error-report.txt" rel="nofollow noreferrer">error (text-file containing the report)</a> while executing the export.</p>
  
  <p><a href="https://i.stack.imgur.com/9O1OZ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9O1OZ.jpg" alt="SSIS Error"></a><br>
  <sub>(source: <a href="http://kirtangor.googlepages.com/ssis-error.jpg" rel="nofollow noreferrer">googlepages.com</a>)</sub>  </p>
  
  <p>For moving ahead, either I need a solution to this error, or a new way to copy from source to destination database with a different collation.</p>
  
  <h2>EDIT 1</h2>
  
  <p>I forgot to mention that I've already tried to use SQL Compare. I have been using that tool since very long. But it won't help a bit in this situation. I am having tables with <code>Latin1_General_CS_AS</code> collation in the source database, but I DON'T want to create the tables with that collation. I know there's an option to <code>Ignore Collation</code>. But then, there's another problem with column names - some column names are weird, and SQL Compare introduces extra <code>[</code> or <code>]</code> characters while formatting column names, which breaks the WHOLE sync script!</p>