sexta-feira, 20 de abril de 2012

Como alterar o COLLATION de um BD no SQL Server 2005 ou 2008

Esse artigo se propõe a ensinar como alterar os collations de todos os bancos de dados de uma instancia SQL Server 2005/2008, inclusive tabelas e campos.

As vezes ou quase sempre não é possível executar o comando abaixo e mesmo que execute será necessário mudar os colations das tabelas e colunas:
--ALTER DATABASE nome_banco COLLATE Latin1_General_CI_AI;

Outro problema é que se os collations estiverem errados nos campos, dependendo da aplicação as consultas irão gerar resultados inesperados.


Figura 01 - Listando os Collations dos Bancos de Dados


Foi observado que caso o collation que queira se mudar seja igual ao do master será necessário informar outro collation diferente para que haja mudança total e depois informar novamente o collation que se deseja.

Por exemplo:

Se deseja mudar o collation para Latin1_General_CI_AI, mas o BD master está com o mesmo collation e o BD do usuário está com outro collation a mudança não será possível, então será necessário mudar o collation do BD master por exemplo: Latin1_General_CI_AS para depois mudar novamente Latin1_General_CI_AI.

Observar também que será necessário que o BD do usuário esteja na pasta padrão data do SQL Server. Exemplo de onde fica a pasta data do SQL Server 2008 com nome da instancia MSSQLSERVER (padrão) na pasta:
 C:\Arquivos de Programas\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\data.

Figura 02 - Pasta DATA padrão do MS SQL SERVER 2008 

Neste caso o procedimento para mudança do collation se faz necessário parar a instancia  do SQL SERVER,  mas para isso por segurança vamos parar todos os serviços do SQL SERVER, neste caso usei a forma nativa do mesmo,  pelo Sql Server Configuration Manager, em Iniciar->Executar digite:


SQLServerManager10.msc
Figura 03 - Parando Todos os Serviços do MS-SQL Server


Exemplo do comando a ser executado em uma instancia com MS-SQL SERVER 2008 ou em Inicar->Executar

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr -m -sMSSQLSERVER -T4022 -T3659 -q"Latin1_General_CI_AI"


Figura 04 - Comando Executado para Mudança de Collation em Iniciar->Executar

Figura 05 - Mudança de Collation sendo Processada

Figura 06 - Final do Processo de Mudança de Collation, Pressionar Y

Outro ponto a observar é que em casos que o BD do usuário tenha algum problema do tipo corrupção, mesmo que ainda não esteja no nível de SUSPECT será necessário dá um DBCC CHECKDB ('NomeBanco',repair_allow_data_loss); e em casos mais extremos, isto é paginação com problemas, usar DBCC UPDATEUSAGE (NomeBanco); depois de corrigidos os problemas no BD, repetir o processo de conversão de collation.


Figura 07 - Erro na Conversão do Collation por Motivo de BD com Problemas


Figura 08 - Executando reparação de erros no BD


Depois de concluido com sucesso a conversão do collation, iniciar o serviço do SQL SERVER pelo Sql Server Configuration Manager, em Iniciar->Executar digite:

SQLServerManager10.msc

Para verificar as mudanças efetuadas, liste os collations dos Bancos de Dados, conforme Figura 01.

Uma outra dica é desatachar os bancos de dados do ReportServer, caso não deseje também mudar o collation do mesmo,
lembrar de parar o serviço do ReportServer antes de desatachar esses bancos.

Segue script abaixo:

-- 0. Caso haja problemas de corrupção, mas ainda não está no nível de suspect com o Banco de Dados, será necessário executar os procedimentos elencados, se não pule essa parte.
ALTER DATABASE NomeBanco SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
DBCC CHECKDB ('NomeBanco',repair_allow_data_loss);
DBCC UPDATEUSAGE (NomeBanco);
ALTER DATABASE NomeBanco SET MULTI_USER WITH ROLLBACK IMMEDIATE; 

-- 1. Listar os tipos de collations Latin1_General%
SELECT * 
  FROM ::fn_helpcollations()
 WHERE name LIKE 'Latin1_General%'
  
-- 2. Listar os bancos com os seus respectivos collations (via query) 
SELECT getdate();
SELECT collation_name
     , * 
  FROM sys.databases 
 WHERE name LIKE '%nome_banco%';

-- 3. Listar os bancos com os seus respectivos collations (via store procedure) 
sp_helpdb; 

/* 
-- 4. Parar o serviço do MS-SQL SERVER em Configuration Manager ou usando o comando direto (sqlservermanager10.msc) ou usando services.msc 

-- 5. Rodar esse comando no prompt de comando (cmd.exe), ao executar esse comando o MS-SQL entrar em modo single user mode e altera todos os collations 
-- de todos os bancos, tabelas e campos, neste caso altera o "Nome_Instancia_MSSQL" e o "Nome_Collation" para o adequando nesse caso para Latin1_General_CI_AI

sqlservr -m -sNome_Instancia_MSSQL -T4022 -T3659 -q"Nome_Collation"

-- 6. Exemplo do comando a ser executado em uma instancia com MS-SQL SERVER 2005 ou em Inicar->Executar
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr -m -sMSSQL -T4022 -T3659 -q"Latin1_General_CI_AI"

-- ou se a instancia que estiver com nome diferente 
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr -m -sMSSQL.1 -T4022 -T3659 -q"Latin1_General_CI_AI"

-- ou pelo prompt do cmd já pasta binn informada acima digitar:
sqlservr -m -sMSSQL.1 -T4022 -T3659 -q"Latin1_General_CI_AI"


-- 7. Exemplo do comando a ser executado em uma instancia com MS-SQL SERVER 2008 ou em Inicar->Executar
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr -m -sMSSQLSERVER -T4022 -T3659 -q"Latin1_General_CI_AI"

-- ou pelo prompt do cmd já pasta binn informada acima digitar:

sqlservr -m -sMSSQLSERVER -T4022 -T3659 -q"Latin1_General_CI_AI"


-- 8. Quando concluir encerrar, isto é, depois que aparacer a seguinte mensagem: 
-- The default collation wa successfully changed. 
-- Recovery is complete. This is an informational message only. No user action is required.
-- Pressionar CTRL + C e depois iniciar o serviço do MS-SQL SERVER (vê item 4, inciando o serviço)

-- Caso não consiga exito executar o comando DBCC CHECKDB, pois deve ter alguma corrupção no BD.
-- 9. Depois conferir se os collations dos bancos mudaram inclusive master, tempdb, msdb (vê item 3 ou item 2 mudando o nome do banco)

*/

Vale salientar que esse procedimento, não é documentado pela Microsoft, mas a conversão funciona no MS SQL Server 2005 e 2008 sem nenhum problema.

Mais uma vez... espero ter ajudado...

Nenhum comentário:

Postar um comentário