🥇Migración de datos de una copia de seguridad de la nueva versión de MS SQL Server a una versión anterior

Antecedentes

Una vez, para reproducir un error, necesité una copia de seguridad de la base de producción.

Para mi sorpresa, me encontré con las siguientes limitaciones:

La copia de seguridad de la base se realizó en la versión SQL Server 2016 y no era compatible con mi SQL Server 2014.
En mi computadora de trabajo, el sistema operativo utilizado era Windows 7, por lo que no pude actualizar SQL Server a la versión 2016
El producto soportado era parte de un sistema más grande con una arquitectura heredada muy vinculada y también accedía a otros productos y bases de datos, por lo que su implementación en otra estación podría llevar mucho tiempo.

Teniendo en cuenta lo anterior, llegué a la conclusión de que era hora de algunas soluciones improvisadas.

Recuperación de datos de la copia de seguridad

Decidí usar una máquina virtual Oracle VM VirtualBox con Windows 10 (se puede obtener una imagen de prueba para el navegador Edge desde aquí). En la máquina virtual se instaló SQL Server 2016 y se restauró la base de datos de la aplicación desde la copia de seguridad (las instrucciones).

Configuración del acceso a SQL Server en la máquina virtual

A continuación, era necesario tomar algunos pasos para permitir el acceso a SQL Server desde el exterior:

Para el firewall, agregar una regla para permitir solicitudes en el puerto 1433.
Es preferible que el acceso al servidor no se realice mediante autenticación de Windows, sino a través de SQL con nombre de usuario y contraseña (es más fácil configurar el acceso). Sin embargo, en este caso, no se debe olvidar habilitar la opción de autenticación SQL en las propiedades de SQL Server.
En la configuración del usuario en SQL Server, en la pestaña User Mapping especificar para la base de datos restaurada el rol de usuario db_securityadmin.

Migración de datos

La migración de datos en sí consta de dos etapas:

Migración del esquema de datos (tablas, vistas, procedimientos almacenados, etc.)
Migración de los propios datos

Migración del esquema de datos

Realizamos las siguientes operaciones:

Seleccionamos Tasks -> Generate Scripts para la base a migrar.
Seleccionamos los objetos necesarios para la migración o dejamos el valor predeterminado (en este caso, se crearán scripts para todos los objetos de la base).
Especificamos la configuración para guardar el script. Lo más conveniente es guardar el script en un solo archivo en codificación Unicode. Así, en caso de fallo, no será necesario repetir todos los pasos.

Después de guardar el script, se puede ejecutar en el SQL Server de origen (versión anterior) para crear la base requerida.

Atención: Después de ejecutar el script, es necesario verificar la correspondencia entre los ajustes de la base de datos del respaldo y la base de datos creada por el script. En mi caso, la configuración de COLLATE estaba ausente en el script, lo que provocaba un error al transferir los datos y requería rehacer la base de datos utilizando un script modificado.

Migración de datos

Antes de transferir los datos, es necesario desactivar la verificación de todas las restricciones en la base de datos:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

La transferencia de datos se realiza mediante el asistente de importación de datos Tareas -> Importar datos en SQL Server, donde se encuentra la base de datos creada por el script:

Especificamos las configuraciones de conexión con la fuente (SQL Server 2016 en una máquina virtual). Usé la fuente de datos SQL Server Native Client y la autenticación SQL mencionada anteriormente.
Especificamos las configuraciones de conexión al destino (SQL Server 2014 en la máquina host).
A continuación, configuramos el mapeo. Es necesario seleccionar todos los no read-only objetos (por ejemplo, no es necesario seleccionar vistas). Como opciones adicionales, se debe elegir «Permitir inserción en columnas de identidad», si se utilizan.
Atención: si al intentar seleccionar varias tablas y asignarles la propiedad «Permitir inserción en columnas de identidad» la propiedad ya se había configurado previamente para al menos una de las tablas seleccionadas, se indicará en el diálogo que la propiedad ya está establecida para todas las tablas seleccionadas. Este hecho puede confundir y llevar a errores en la transferencia.
Iniciamos la transferencia.

Restauramos la verificación de las restricciones:

EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

Si hay algún error, verificamos los ajustes, eliminamos la base de datos creada con errores, la recreamos a partir del script, hacemos las correcciones y repetimos la transferencia de datos.

Conclusión

Esta tarea se encuentra relativamente rara vez y surge solo debido a las limitaciones mencionadas anteriormente. La solución más común implica actualizar SQL Server o conectarse a un servidor remoto, si la arquitectura de la aplicación lo permite. Sin embargo, nadie está a salvo del código legado y de las manos inexpertas de un desarrollo de mala calidad. Espero que esta guía no sea necesaria, y si surge la necesidad, ayude a ahorrar mucho tiempo y nervios. ¡Gracias por su atención!