测试环境重建数据库排序规则和系统数据库的区别及解决办法

　　测试环境重建数据库排序规则和系统数据库的区别及解决办法

　　最近因为测试环境中的MSSQL server collat​​ion是区分大小写的，所以临时表（tempdb）中的数据和临时对象都是区分大小写的。于是重建了系统数据库，改变了排序规则，这里再总结一下。

　　开始之前，确认排序规则（或者是否区分大小写）

　　--查看服务器排序规则(安装时指定的排序规则)

SELECT SERVERPROPERTY('COLLATION') AS ServerCollation

,DATABASEPROPERTYEX('tempdb','COLLATION') AS TempdbCollation

,DATABASEPROPERTYEX(DB_NAME(),'COLLATION') AS CurrentDBCollation

--查看数据库排序规则

SELECT name, collation_name FROM sys.databases

--当前数据库是否大小写敏感

SELECT CASE WHEN N'A'=N'a' THEN N'不敏感' ELSE N'敏感' END

　　本环境实例中，服务器排序规则为Chinese_PRC_BIN，当前数据库排序规则为Chinese_PRC_CI_AS。如果在当前数据库中创建的所有对象和脚本都执行，如果使用变量、临时对象等，则情况不一致，导致出现问题。二进制 (_BIN) 排序规则区分大小写，请参阅 Windows Collat​​ion Sorting Styles。

　　安装实例时指定的collat​​ion是master数据库的collat​​ion，model和msdb的collat​​ion也是一致的，tempdb和user数据库的collat​​ion和model数据库的collat​​ion是一样的。系统数据库不能直接改变排序规则，所以只能重建系统数据库，使系统数据库的排序规则保持一致。

　　重建实例排序规则和系统数据库排序规则：

　　1. 备份系统数据库！如果需要，可以在失败时返回或者以后想使用当前环境！

　　2. 记住所有数据库和文件路径，并保存在excel中。如果您记住所有数据库位置，那么您就不必记住。

　　select DB_NAME(database_id) as name,physical_name from sys.master_files

　　3. 导出服务器配置（sp_configure）到excel。因为系统数据库重建后会初始化配置。

　　4. 导出账号信息到txt。当数据库重建时，帐户信息将丢失。除备份外，帐户必须单独备份。 （SQL Server中登录账号和数据库用户迁移）

　　5. 将链接服务器生产脚本导出到txt，如果有的话。

　　6. 将代理作业导出到 txt。

　　7.（如有其他配置，如审计、邮件配置、策略等，请保存，稍后重建）

　　8. 分隔所有用户数据库。

　　select 'ALTER DATABASE ['+name+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'+char(10)+'go'+char(10)

+'EXEC master.dbo.sp_detach_db @dbname = N'''+name+''''+char(10)+'go'+char(10)

from sys.databases where name not in('master','model','msdb','tempdb')

　　9. 停止所有与数据库相关的服务。

　　10. 重建数据库，制定新的排序规则。打开命令行，进入安装目录。

　　cd D:\Software\en_sql_server_2008_r2_enterprise_x86_x64_ia64_dvd_520517

setup /QUIET /ACTION=REBUILDDATABASE /instancename=mssqlserver /SQLSYSADMINACCOUNTS=服务器账号 /sapwd=密码 /sqlcollation=Chinese_PRC_CI_AS

　　11.启动SQL Server引擎服务，其他暂时不启动。

　　12. 创建登录账号（之前导出的脚本）

　　13. 附加所有用户数据库。

　　14. 创建代理作业、链接服务器、更改配置等！

　　完成！ ~

　　如果在重建系统数据库后恢复master数据库，服务器排序规则和master数据库排序规则恢复到与之前相同，因此除非恢复之前的环境，否则无法恢复。

　　如果还原msdb或model，数据库的collat​​ion也会还原，所以不要还原系统数据库备份。重建系统数据库后才能重新配置。

　　更改用户数据库排序规则：

　　ALTER DATABASE test COLLATE Chinese_PRC_CI_AS

　　更改数据库排序规则时，需要更改以下内容：

　　>> 更改系统表中的任何 char、varchar、text、nchar、nvarchar 或 ntext 列以使用新的排序规则。

　　>>所有现有的char、varchar、text、nchar、nvarchar或ntext参数以及存储过程和用户​​定义函数的标量返回值都更改为使用新的排序规则。

　　>>更改 char、varchar、text、nchar、nvarchar 或 ntext 系统数据类型以及基于这些系统数据类型的所有用户定义数据类型，以使用新的默认排序规则。

　　对于数据库中字段定义的排序规则，请参考以下设置或更改列排序规则。

　　设置或更改列排序规则：

　　--示例

CREATE TABLE dbo.MyTable(

PrimaryKey INT PRIMARY KEY,

CharCol VARCHAR(10) COLLATE French_CI_AS NOT NULL

);

GO

ALTER TABLE dbo.MyTable ALTER COLUMN CharCol VARCHAR(10) COLLATE Latin1_General_CI_AS NOT NULL;

GO

　　如果以下之一当前正在引用一列，则无法更改该列的排序规则：

　　>>计算列

　　>>索引

　　>> 自动生成或由 CREATE STATISTICS 语句生成的分布统计信息

　　>>检查约束

　　>>外键约束

　　参考：

　　Windows 整理排序样式

　　设置或更改服务器排序规则

　　SQL Server 整理

　　SQL Server 中的登录账号和数据库用户迁移