Migrar una base de datos a una versión anterior de MS SQL Server

Tiene una base de datos MS SQL Server que necesita transferir a otra computadora física. Ya ha realizado una copia de seguridad y se embarca felizmente en una recuperación. Pero luego resulta que en la computadora donde desea transferir la base de datos, se instala una versión anterior de MS SQL Server. Stack Overflow te asegura que todo está mal. ¿Pero es realmente así?


Por supuesto, transferir una base de datos de una versión más nueva a una antigua no es un escenario clásico ni el más correcto. Pero a menudo las bases de datos se crean de tal manera que admiten más y más versiones nuevas de SQL, comenzando con algunas, por ejemplo, 2008 R2, porque La compatibilidad directa con MS SQL es más que excelente. Y, por ejemplo, su cliente ya ha instalado MS SQL 2016 por sí mismo, y usted tiene instalado MS SQL 2014 en su servidor de prueba para el desarrollo. Y desea expandir su base de clientes para descubrir dónde hay confusión de datos.

Microsoft repudió el problema: dicen que no tienen compatibilidad con versiones anteriores y que no hay una quiebra. La copia de seguridad creada en el servidor más nuevo no se puede restaurar al servidor más antiguo. Sí, tienen herramientas como DTS, copiar una base de datos, exportar-importar, etc. Pero son tan inconvenientes y engorrosos que la transferencia normal de una gran base de datos con muchas tablas para hacer que su uso no sea particularmente conveniente. En cualquier caso, personalmente no tuve éxito.

Sí, puede generar scripts SQL para toda la base de datos, incluidos los datos. Pero imagine que tiene un montón de campos de blob con grandes datos en la base de datos y, en general, el tamaño de toda la base de datos es de más de 500 GB. Imagine cuánto tardará un script así, cuánto tiempo se generará y ejecutará.

Entonces, la tarea es recrear con precisión la base de datos (estructura y datos) de la nueva versión del servidor MS SQL en la versión anterior. Se me ocurrió una solución bastante simple que quiero compartir. Por supuesto, esta solución tiene una cantidad significativa de limitaciones, pero aún así, en mi opinión, es mejor que DTS y scripts.

La limitación número uno es que necesita acceso a través de MS SQL Management Studio a ambos servidores, antiguos y nuevos. Si esto no es posible, entonces debería ser posible en la máquina desde donde desea transferir la base de datos, instale la versión de SQL en la que necesita transferir la base de datos para transferir la base de datos primero a esta versión localmente, y luego arrástrela a través de la copia de seguridad o directamente a través de * df archivos de base de datos (a través de Separar / Adjuntar) a la nueva máquina (la versión de SQL Server en este caso ya coincidirá).

Otra limitación es que necesitará un script de esquema de base de datos (todos los objetos, incluidas tablas, índices, constantes, procedimientos almacenados, desencadenantes, etc.) sin datos, y las instrucciones para crear restricciones de clave externa deben ir a este script al final, separarse del guión para crear las propias tablas.

Describiré brevemente el algoritmo de transferencia de datos en sí. Todas las acciones se realizan en una sesión de Management Studio conectada al servidor al que desea transferir la base de datos.

1) En el nuevo servidor, cree una base de datos vacía con los mismos archivos y grupos de archivos que la base de datos portátil.

2) Usando el script de esquema de la base de datos, creamos todos los objetos de la base de datos (tablas, índices, vistas, disparadores, procedimientos y funciones almacenados), pero sin crear restricciones de clave externa. No puede crear FK en esta etapa, porque interferirán con la inserción de datos.

3) Conectamos la base de datos desde la cual transferiremos los datos como un servidor vinculado para que pueda utilizar la base de datos anterior en consultas a la nueva base de datos.

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 las estructuras de la base de datos son las mismas, usaremos el procedimiento almacenado incorporado sp_msforeachtable, que le permite realizar una consulta en cada tabla de la base de datos para generar un script para transferir datos de la base de datos anterior a la nueva a través de una consulta del formulario

 INSERT INTO ? SELECT * FROM ? 

En lugar de un signo de interrogación, sp_msforeachtable sustituye el nombre de cada tabla y ejecuta la consulta varias veces (una vez para cada tabla).

Aquí me encontré con la mayor cantidad de rastrillos.

a) El problema número uno es que para las tablas con campos IDENTITY, debe llamar a:

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

b) El problema número dos es que en las tablas que no tienen campos IDENTITY, no puede realizar esta llamada, por lo que debe determinar dinámicamente si la tabla tiene una columna IDENITY o no.

Esto se puede hacer con esta consulta:

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

c) El problema número tres es que, como resultó, en el modo IDENITY_INSERT ON, no puedes hacer

 INSERT INTO ... SELECT * FROM ... 

, pero debe enumerar campos específicos.

Puede enumerar los campos de la tabla en una fila con 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) Generamos un script de inserción para todas las tablas:


5) Ejecutamos el script de transferencia de datos generado

6) Ejecutamos un script para crear todas las restricciones de clave externa (ahora ya es posible).

7) ¡Listo! Transferió la base de datos del nuevo servidor SQL al antiguo, aunque esto se consideró imposible. Además, la transferencia se lleva a cabo solo una vez y media más lentamente que la velocidad de transferencia de datos a través de la red, es decir, Bastante rápido

8) Limpiamos después de nosotros mismos (desconectamos el servidor vinculado):

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

Limitaciones del método.

1) Utilizando un método similar, no será posible transferir tablas en las que hay columnas de tipo XML.
Seguramente hay muchas otras restricciones, como La base de datos que transferí de esta manera no usaba muchas de las características del servidor SQL. Puede escribir sobre restricciones en los comentarios, y les agregaré un artículo.

Gracias por su atencion! Espero que ayude a alguien.