How to transfer a database from one collation to another collation in SQL Server (325335)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup
This article was previously published under Q325335

IN THIS TASK

SUMMARY

This article defines collation and describes how to transfer a database from one collation in Microsoft SQL Server to another collation in SQL Server. The same concepts and discussions about SQL Server 2000 also apply to SQL Server 2005.

back to the top

What is collation?

A collation specifies the bit patterns that represent each character. It also specifies the rules that are used to sort and to compare the characters. A collation has the following characteristics:
  • Language
  • Case sensitivity
  • Accent sensitivity
  • Kana sensitivity
To know the collation that a server is currently using, you can run the sp_helpsort system procedure in SQL Query Analyzer.

SQL Server 7.0 does not support databases that have multiple collations. Therefore, all the databases that you create in SQL Server 7.0 use the default collation. SQL Server 2000 supports multiple collations. SQL Server 2000 databases can have collations other than the default collation. Additionally, SQL Server 2000 also supports columns that have collations other than the collations of the databases where they were created.

back to the top

When to use the Use Collation option in DTS

SQL Server 2000 can have multiple databases or columns that have collations other than the default collation. Because of this, a new option that is named Use Collation is introduced in Data Transformation Services (DTS). The behavior of the Use Collation option is determined by the type of transfer that you are performing. If you transfer data between two instances of SQL Server 2000 and you enable the Use Collation option, the data is translated from the source code page to the destination code page. If you do not enable the Use Collation option and the code pages are the same on both instances of SQL Server 2000, a direct data transfer occurs. If the code pages are different, the data from the source code page is translated to the destination code page. However, the translation may not be correct when you transfer the data.

Note The collation is important if the collation is used for the data itself and if a column uses the COLLATE clause. The Use Collation option determines whether a code page translation occurs when the data is transferred from one collation to another collation. The Use Collation option does not affect whether the COLLATE property of a column definition is set. Therefore, if a source table contains a column that was created with a specific collation by using the COLLATE clause, that collation persists when data is transferred, regardless of whether the Use Collate option is enabled in the Data Transformation Services Wizard.

back to the top

Transfer methods that do not change a database's collation

The following methods do not change a database's collation:
  • Backup and restore: Restoring a database on a server that has a different collation than the server that is used for the backup does not convert the restored database to the new collation. The database collation remains as is.
  • Detach and reattach: If you detach a database that was created with one collation and you reattach the database to another server that has a different collation, the collation of the database does not change. The collation of the database remains as is.
  • Copy Database Wizard: The Copy Database Wizard essentially automates the process of detaching and reattaching. The collation of the database remains as is.

    Note The Copy Database Wizard is only available in SQL Server 2000.

back to the top

Transfer a database from one collation in SQL Server 7.0 to a different collation in SQL Server 7.0

To change the collation of a database between two computers that are running SQL Server 7.0, you must create the user database and all the objects on the destination server and then transfer the data by using DTS or the bcp utility.

To transfer a database from a computer that is running SQL Server 7.0 to a computer that is running SQL Server 7.0 and that has a different collation, follow these steps:
  1. Back up the source database.
  2. Use SQL Server Enterprise Manager to create scripts for all the objects in the source database.
  3. To export the data from all the tables in the database, use DTS or the bcp utility.
  4. Create a new database on the destination server by using SQL Server Enterprise Manager or the CREATE DATABASE statement.

    Note When you use the CREATE DATABASE statement, the database will have the same collation as the computer that is running SQL Server 7.0.
  5. Use SQL Query Analyzer to run the scripts that you created in step 2 to re-create all the objects in the destination database.

    Note The tables and columns will have the same collation as the computer that is running SQL Server 7.0.
  6. Import the data in the destination tables by using DTS or the bcp utility.
back to the top

Transfer a database from one collation in SQL Server 7.0 to a collation in SQL Server 2000

To change the collation of a database between SQL Server 7.0 and SQL Server 2000, you must create the database, the columns, or both with the appropriate collation on the destination server before you transfer the data. However, you can use DTS to drop and then re-create the objects when you transfer data from SQL Server 7.0 to SQL Server 2000. When doing so, you must enable the Use Collation option in DTS.

Note Do not use the DTS utility that is included in SQL Server 7.0 to transfer all objects to or from a computer that is running SQL Server 2000. You must use the DTS utility that is included in SQL Server 2000 when you have to transfer data between SQL Server 7.0 and SQL Server 2000.

To transfer a database from one collation in SQL Server 7.0 to a collation in SQL Server 2000, follow these steps:
  1. Back up the source database.
  2. Use SQL Server Enterprise Manager to create scripts for all the objects in the source database.
  3. If the columns must have a different collation than the default collation on the destination database, make the required collation changes to the appropriate columns in the scripts.
  4. Create a new database on the destination server with the appropriate collation.
  5. Use SQL Query Analyzer to run the scripts that you created in step 2 on the destination server to re-create all objects in the database.

    Note The new tables and columns have the same collation as the database unless you specify a different collation for the columns.
  6. Use DTS or the bcp utility to transfer the data.

    Note If you use DTS, verify the following:
    • Make sure that the Use Collation option is enabled when you transfer data from SQL Server 7.0 to SQL Server 2000.
    • Because the objects are already created on the destination server with the appropriate collation, disable the Create Destination Objects First option.
back to the top

Transfer a database from one collation in SQL Server 2000 to a different collation in SQL Server 2000

To transfer a database from one collation in SQL Server 2000 to a different collation in SQL Server 2000, follow these steps:
  1. Back up the source database.
  2. Note if any columns use the COLLATE clause.
  3. Create a new database on the destination server with the appropriate collation.
  4. If no columns use the COLLATE clause, use DTS to transfer the data to the destination server. To do so, enable the Use Collation option for code page translation and to transfer the data to the new collation on the destination database. If any columns use the COLLATE clause, follow these steps:
    1. Generate scripts for all the objects (not including the indexes, the triggers, the primary keys, the foreign keys, the default settings, and the constraints). Additionally, make sure that you enable the Only script 7.0 compatible features option to remove the COLLATE clause from the script.

      Note When you use the Only script 7.0 compatible features option, you can change the collation. However, any new SQL Server 2000 options (including user-defined functions, extended properties, the INSTEAD OF trigger, and indexes on views) will not be considered when the scripts are generated.
    2. Run the scripts from step a on the destination database to create the objects with the destination database collation.
    3. Use DTS to transfer only the data from the source database.
    4. After the data is successfully transferred, generate scripts for all the constraints, foreign keys, primary keys, and indexes from the source database.
    5. Run the scripts from step d on the destination database.
back to the top

Change the collation of the master database

If you want to change the collation of the master database, you must rebuild the master database. When you rebuild the master database, you essentially create a new master database. Therefore, consider the following items before you rebuild the master database:
  • Make sure to make a valid backup of the current master database. When you rebuild the master database, the msdb database and the model database are also re-created. Therefore, you must back up the msdb database and the model database before you rebuild the master database. The msdb database is the system database that is used to store your SQL Server jobs, alerts, operators, and DTS packages. The model database is the template database that is used when you create a new database.
  • Because rebuilding the master database creates a new master database, you must re-enter the existing login information after you rebuild the master database. Therefore, you must export the login information before you rebuild the master database. After you re-create the master database, import the login information. For additional information about how to export login information, click the following article number to view the article in the Microsoft Knowledge Base:

    246133 HOW TO: Transfer logins and passwords between instances of SQL Server

  • Because the msdb database is rebuilt when you rebuild the master database, you must generate scripts for all the jobs, alerts, and operators before you rebuild the master database. Additionally, you must make sure that you move all DTS packages. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

    314546 HOW TO: Move databases between computers that are running SQL Server

  • Because the model database is rebuilt when you rebuild the master database, any changes that were previously made to the model database must either be noted, scripted, or exported before you rebuild the master database. After the model database is rebuilt, reapply any noted changes.

Change the collation of the master database in SQL Server 2000

Before you change the collation of the master database, follow these steps:
  1. When you rebuild the master database, it does not automatically recall all the databases that were created before the database is rebuilt. Therefore, all databases must be restored from a backup or if the files are still on disk, you can reattach the databases by using the sp_attach_db system stored procedure. Make sure that you have all the necessary information to reattach existing databases before you rebuild the master database. For more information about the sp_attach_db system stored procedure, visit the following MSDN Web site:

    http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp

  2. When you rebuild the master database, only the collation of the master database, the model database, and the msdb database is changed. The collation of the user databases is not changed. To change the collation of an existing user database or to create a new database with the appropriate collation, use the ALTER DATABASE command, and then use DTS or the bcp utility to transfer the data to the new database.

    Note If you use the ALTER DATABASE command in SQL Server 2000 to change the collation of a database, the collation of the columns in the tables is not automatically changed. To change the collation of the columns, use the ALTER TABLE command and the ALTER COLUMN command. If you are using DTS, you can create the table and the columns with the appropriate collation before you transfer the data or you can use the Use Collation option. If you are using DTS and the table with the appropriate collation already exists, make sure to disable the Drop Existing Objects First option before you run the package.

Change the collation of the master database in SQL Server 7.0

Before you change the collation of the master database, follow these steps:
  1. SQL Server 7.0 does not support having databases with collations other than the default collation. Therefore, before you rebuild the master database, export all the data from the user databases.
  2. Generate the scripts for all the objects in the database.
  3. Re-create the master database with the appropriate collation.
  4. Create the new databases. The new databases are automatically created with the new default collation.
  5. Run the previously saved scripts to re-create the objects, and then import the data that you previously exported.
back to the top

REFERENCES

For more information about collations, visit the following Microsoft Web sites: back to the top
Modification Type:MajorLast Reviewed:12/20/2005
Keywords:kbHOWTOmaster KB325335
©2004 Microsoft Corporation. All rights reserved.