Migrando um banco de dados para uma versão mais antiga do MS SQL Server

Você tem um banco de dados do MS SQL Server que precisa transferir para outro computador físico. Você já fez um backup e está felizmente iniciando uma recuperação. Porém, no computador em que você deseja transferir o banco de dados, uma versão mais antiga do MS SQL Server está instalada. O estouro de pilha garante que tudo está ruim. Mas é mesmo assim?


Obviamente, transferir um banco de dados de uma versão mais recente para uma antiga não é um cenário clássico nem o mais correto. Porém, geralmente os bancos de dados são criados de forma a oferecer suporte a mais e mais novas versões do SQL, começando com algumas, por exemplo, 2008 R2, porque A compatibilidade direta com o MS SQL é mais do que excelente. E, por exemplo, seu cliente já instalou o MS SQL 2016 e o ​​MS SQL 2014 foi instalado no servidor de teste para desenvolvimento.E você deseja expandir sua base de clientes para descobrir onde há confusão de dados.

A Microsoft negou o problema - eles dizem que não têm compatibilidade com versões anteriores e que não há buscas. O backup criado no servidor mais recente não pode ser restaurado no servidor mais antigo. Sim, eles possuem ferramentas como DTS, cópia de banco de dados, importação e exportação, etc. Mas eles são tão inconvenientes e complicados que a transferência normal de um banco de dados grande com muitas tabelas para usá-los não é particularmente conveniente. De qualquer forma, eu pessoalmente não tive sucesso.

Sim, você pode gerar scripts SQL para todo o banco de dados, incluindo dados. Mas imagine, você tem vários campos de blob com grande volume de dados no banco de dados e, em geral, o tamanho de todo o banco de dados é de mais de 500 GB. Imagine quanto tempo esse script levará, quanto tempo será gerado e executado.

Portanto, a tarefa é recriar com precisão o banco de dados (estrutura e dados) da nova versão do MS SQL Server na versão anterior. Eu vim com uma solução bastante simples que quero compartilhar. Obviamente, esta solução tem um número significativo de limitações, mas ainda na minha opinião é melhor que DTS e scripts.

A limitação número um é que você precisa acessar, através do MS SQL Management Studio, os dois servidores - antigos e novos. Se isso não for possível, deve ser possível na máquina de onde você deseja transferir o banco de dados, instale a versão do SQL na qual você deseja transferir o banco de dados para transferir o banco de dados primeiro para esta versão localmente e arraste-o pelo backup ou diretamente por * df arquivos de banco de dados (via Desanexar / Anexar) à nova máquina (a versão do SQL Server nesse caso já corresponderá).

Outra limitação é que você precisará de um script de esquema de banco de dados (todos os objetos, incluindo tabelas, índices, constantes, procedimentos armazenados, gatilhos, etc.) sem dados, e as instruções para criar restrições de chave estrangeira devem ir para esse script no final, separe do script para criar as próprias tabelas.

Descreverei brevemente o próprio algoritmo de transferência de dados. Todas as ações são executadas em uma sessão do Management Studio conectada ao servidor para o qual você deseja transferir o banco de dados.

1) No novo servidor, crie um banco de dados vazio com os mesmos arquivos e grupos de arquivos que o banco de dados portátil.

2) Usando o script de esquema do banco de dados, criamos todos os objetos do banco de dados (tabelas, índices, visualizações, gatilhos, procedimentos e funções armazenados), mas sem criar restrições de chave estrangeira. Você não pode criar FK nesta fase, porque eles irão interferir na inserção de dados.

3) Conectamos o banco de dados do qual transferiremos os dados como um servidor vinculado para que você possa usar o banco de dados antigo em consultas ao novo banco de dados.

EXEC sp_addlinkedserver @server=N'LinkedServerAlias', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'LinkedServerHost\LinkedServerName'; EXEC sp_addlinkedsrvlogin 'LinkedServerUser', 'false', null, 'RealUser', 'RealUserPassword'; 

4) porque Se as estruturas do banco de dados forem as mesmas, usaremos o procedimento armazenado interno sp_msforeachtable, que permite executar uma consulta em cada tabela do banco de dados para gerar um script para transferir dados do banco de dados antigo para o novo por meio de uma consulta do formulário

 INSERT INTO ? SELECT * FROM ? 

Em vez de um ponto de interrogação, sp_msforeachtable substitui o nome de cada tabela e executa a consulta várias vezes (uma vez para cada tabela).

Aqui me deparei com o maior número de ancinhos.

a) O problema número um é que, para tabelas com campos IDENTITY, você deve chamar:

 SET IDENTITY_INSERT ON; --INSERT INTO ... ( ); SET IDENTITY_INSERT OFF; 

b) O problema número dois é que, nas tabelas que não possuem campos de IDENTIDADE, você não pode fazer essa chamada, portanto, é necessário determinar dinamicamente se a tabela possui ou não uma coluna de IDENIDADE.

Isso pode ser feito com esta consulta:

 SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME='SomeTable') AND (COLUMNPROPERTY(object_id('dbo.SomeTable'), COLUMN_NAME, 'IsIdentity') = 1) 

c) O problema número três é que, como se viu, no modo IDENITY_INSERT ON, você não pode

 INSERT INTO ... SELECT * FROM ... 

, mas você precisa listar campos específicos.

Você pode listar os campos da tabela em uma linha com esta consulta:

 SELECT SUBSTRING( (SELECT ', ' + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SomeTable' ORDER BY ORDINAL_POSITION FOR XML path('')), 3, 200000); 

4) Geramos um script de inserção para todas as tabelas:


5) Executamos o script de transferência de dados gerado

6) Executamos um script para criar todas as restrições de chave estrangeira (agora já é possível).

7) Feito! Você transferiu o banco de dados do novo servidor SQL para o antigo, embora isso fosse considerado impossível. Além disso, a transferência é realizada apenas uma vez e meia mais lenta que a taxa de transferência de dados na rede, ou seja, bem rápido.

8) Nós limpamos depois de nós mesmos (desconectamos o Servidor Vinculado):

 EXEC sp_droplinkedsrvlogin 'LinkedServerUser', null; sp_dropserver 'LinkedServerAlias'; 

Limitações do método.

1) Usando um método semelhante, não será possível transferir tabelas nas quais existem colunas do tipo XML.
Certamente existem muitas outras restrições, como o banco de dados que transferi dessa maneira não utilizou muitos dos recursos do servidor SQL. Você pode escrever sobre restrições nos comentários e adicionarei um artigo a elas.

Obrigado pela atenção! Espero que ajude alguém.