Change the SQL Server instance default character set

Demand

When you install the database, the character set is installed as "sql_latin1_general_cp1_ci_as" and you now need to change it to "chinese_prc_ci_as".

Method

rebuild the system database , and then restore the configuration information.

Limitations

When you regenerate the master, model, msdb, and tempdb system databases, the databases are deleted and recreated in their original locations. If a new collation is specified in the Rebuild statement, the system database is created using the collation setting. Any changes that users make to these databases are lost. For example, your user-defined objects in the master database, scheduled jobs in msdb, or changes to the default database settings in the model database will be lost.

Pre-preparation

Perform the following tasks before rebuilding the system database to ensure that the system databases can be restored to their current settings.

1. record all server-wide configuration values. SELECT * from Sys.configurations;
2.   These updates must be re-applied after the system database has been rebuilt. select           serverproperty (' ProductVersion ' As ProductVersion,           serverproperty (' ProductLevel ') as ProductLevel, serverproperty (' resourceversion ') as Resourceversion,            serverproperty (' Resourcelastupdatedatetime ') as Resourcelastupdatedatetime,            serverproperty (' Collation ') as Collation;

1. record the current location of all data files and log files for the system database.   Rebuilding the system database installs all system databases to their original locations.   If you have moved the system database data files or log files to a different location, you must move the files again. select name, physical_name as     current_file_location            from Sys.master_files           WHERE database_id in (db_id (' master '), db_id (' model '), db_id (' msdb '),     db_id (' tempdb '));

1. If the instance of SQL Server is configured as a replication Distributor, locate the current backup of the distribution database.
2. Make sure that you have the appropriate permissions to regenerate the system database. You must be a member of the sysadmin fixed server role to perform this operation. For more information, see Server-level roles .

1. The original database of the account,JOB , and other information related to the system database back up (can be the corresponding script, Sir), the separation of all user libraries.

Rebuild the System database

Run the following command to rebuild the database (depending on your system, you need to make the appropriate changes; D:/setup is the path where the database installation files are located)

D:/setup/quiet/action=rebuilddatabase/instancename=mssqlserver/sqlsysadminaccounts=administrators/sapwd= XXXX /sqlcollation=chinese_prc_ci_as

Parameter description:

Parameter name	Description
/quiet or/Q	Specifies that the Setup program runs without any user interface.
/action=rebuilddatabase	Specifies that the Setup program will recreate the system database.
/instancename=instancename	The name of the SQL Server instance. for the default instance, enter MSSQLSERVER.
/sqlsysadminaccounts=accounts	Specify to be added to   Sysadmin  Windows   When you specify multiple accounts, separate the accounts with a space.   For example, please enter builtin\administrators  mydomain\ Myuser   For example, enter  nt Authority\System
[ /sapwd=strongpassword ]	specifies sql  server sa  The password of the account.  sql Server    Windows Security instructions sa< Span lang= "en-US" >  account is a well-known SQL Server account, And often become an attack target for malicious users.   so  sa  Do not specify this parameter for Windows authentication mode.
[/sqlcollation=collationname ]	Specifies a new server-level collation. This parameter is optional. If not specified, the current collation of the server is used. Important Notes Changing a server-level collation does not change the collation of an existing user database. By default, all newly created user databases will use the new collation. For more information, see Set or change a server collation.

When Setup finishes , the following information is displayed (you can also check the Summary.txt log file to verify that the rebuild process completed successfully.) This file is located in C:\Program Files\Microsoft SQL server\100\setup bootstrap\logs).

Check and Repair

After the system database recovery is complete, check that the character set is restored as required:

After the character set changes, we also need to attach the user database, the original account and JOB and Other information and configuration information to repair, also need to repair orphaned accounts and so on.

Additional Instructions

If you only need to change the character set of a user database, it's very simple:

--Modifying the database character set

ALTER DATABASE DBName COLLATE chinese_prc_ci_as

Attention

The above recovery is done on the SQLSERVER2008R2 version, different versions you may need to make some changes, and this method has some risks (mainly in rebuilding the database phase), the product environment

It is a good idea to reinstall the database.

Change the SQL Server instance default character set