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...

Shrink MSSQL Server 2005 e 2008

Objetivo deste artigo tem com principio a orientação de como proceder redução de LOG e BD no MSSQL SERVER 2005 e 2008.

Lembro também que execução de SHRINK, impossibilita a recuperação do BD, caso ocorra alguma suspeita de corrupção do banco de dados (SUSPECT), todavia é recomendável configurar plano de manutenção, evitando assim muitas problemas.

Recomenda-se backup full antes de executar os scripts abaixo:

No MSSQL SERVER 2005 para MDF e LDF

--SHIRINK SQL SERVER 2005, formato 1, SHIRINK DE LDF e MDF 
--Alterar o [nome_banco] para seu banco de dados 
USE [nome_banco]; 
GO
BACKUP LOG [nome_banco] WITH TRUNCATE_ONLY;
GO
DBCC SHRINKDATABASE([nome_banco], 10, TRUNCATEONLY);

No MSSQL SERVER 2008 para MDF e LDF

--SHIRINK SQL SERVER 2008, formato 1, se tiver como recovery_mode full, SHIRINK DE LDF e MDF 
--Lista bancos com os modos de recuperação 
SELECT name, recovery_model, recovery_model_desc, state_desc, collation_name, user_access_desc, create_date 
FROM sys.databases;
GO

--Alterar o [nome_banco] para seu banco de dados 
USE [nome_banco]; 
GO

BACKUP LOG [nome_banco] TO DISK = 'NUL:';
GO

DBCC SHRINKDATABASE([nome_banco], 10, TRUNCATEONLY);
GO

Truncando apenas o LOG (LDF) MS SQL SERVER 2005

--SHIRINK SQL SERVER 2005, formato 2, SHIRINK DE LOG 
--Alterar o [nome_banco] para seu banco de dados 
USE [nome_banco];
GO

BACKUP LOG [nome_banco] WITH TRUNCATE_ONLY;
GO

DECLARE @log_file_logical_name sysname;
SELECT @log_file_logical_name=name FROM sys.database_files d WHERE type = 1;
PRINT @log_file_logical_name;

DBCC SHRINKFILE (@log_file_logical_name, 1);
GO

Truncando apenas o LOG (LDF) MS SQL SERVER 2008

--SHIRINK SQL SERVER 2008, formato 2, SHIRINK DE LOG 
--USANDO RECOVERY SIMPLE, isto é, desabilitando o LOG detalhado 
--Alterar o [nome_banco] para seu banco de dados 
SELECT name, recovery_model, recovery_model_desc, state_desc, collation_name, user_access_desc, create_date FROM sys.databases;
GO

USE [nome_banco];
GO
ALTER DATABASE [nome_banco] SET RECOVERY SIMPLE; 
GO

DECLARE @log_file_logical_name sysname;
SELECT @log_file_logical_name=name FROM sys.database_files d WHERE type = 1;
PRINT @log_file_logical_name;

DBCC SHRINKFILE (@log_file_logical_name, 1);
GO

-- Retornando para recovery_model para full 
ALTER DATABASE [nome_banco] SET RECOVERY FULL ; 

Espero ter ajudado!