Changing the Character Set, Sort Order, or Unicode Collation

The character set, sort order, and Unicode collation are fundamental to the structure of all Microsoft® SQL Server™ databases. In order to change one or more of these settings, you must rebuild the master and user databases.

Follow these steps to rebuild the databases and maintain their contents.

1. Back up the definitions of objects you want to preserve.

   Use SQL Server Enterprise Manager to create scripts for all of the objects in the user databases and master database.

2. Export data from the user tables.

   Use Microsoft Data Transformation Services (DTS) or the bcp bulk copy utility to unload the user data.

3. Rebuild the master database.

   Run the Rebuildm.exe command prompt utility. You can select new character set, sort order, and Unicode values and rebuild the master database with these new settings.

4. Create new user databases.

   Use SQL Server Enterprise Manager or the CREATE DATABASE statement to re-create the user databases.

5. Create objects using the scripts created earlier.

   Use SQL Server Query Analyzer to run the scripts you created earlier.

6. Import data into the user tables.

   Use DTS or BCP to load data back into the user tables.

See Also