Transférer les données de sauvegarde d'une nouvelle version de MS SQL Server vers une ancienne version

Contexte

Une fois, pour reproduire un bug, j'avais besoin d'une sauvegarde de la base de production.

À ma grande surprise, je suis tombé sur les limitations suivantes:

1. La sauvegarde de la base de données a été effectuée sur la version de SQL Server 2016 et n'était pas compatible avec mon SQL Server 2014 .
2. Sur mon ordinateur de travail, Windows 7 a été utilisé comme système d'exploitation, donc je n'ai pas pu mettre à niveau SQL Server vers la version 2016
3. Le produit pris en charge faisait partie d'un système plus vaste doté d'une architecture Legacy solide et accédait également à d'autres produits et bases, de sorte que son déploiement sur une autre station pouvait prendre très longtemps.

Compte tenu de ce qui précède, je suis arrivé à la conclusion que le temps était venu pour les béquilles de solutions non standard.

Récupération de données à partir d'une sauvegarde

J'ai décidé d'utiliser Oracle VM VirtualBox avec Windows 10 (vous pouvez prendre une image de test pour le navigateur Edge à partir d'ici ). SQL Server 2016 a été installé sur la machine virtuelle et la base de données d'application a été restaurée sur celle-ci à partir de la sauvegarde ( instructions ).

Configurer l'accès à SQL Server dans une machine virtuelle

De plus, il était nécessaire de prendre certaines mesures pour permettre d'accéder à SQL Server de l'extérieur:

1. Pour un pare-feu, ajoutez une règle pour ignorer les demandes sur le port 1433 .
2. Il est souhaitable que l'accès au serveur ne passe pas par l'authentification Windows, mais par SQL avec un nom d'utilisateur et un mot de passe (il est plus facile de configurer l'accès). Cependant, dans ce cas, vous devez vous rappeler d'activer l'authentification SQL dans les propriétés de SQL Server.
3. Dans les paramètres utilisateur sur SQL Server, sous l'onglet Mappage utilisateur , spécifiez le rôle utilisateur db_securityadmin pour la base de données restaurée.

Transfert de données

En fait, le transfert de données lui-même comprend deux étapes:

1. Transfert de schéma de données (tables, vues, procédures stockées, etc.)
2. Transférer les données elles-mêmes

Migration du schéma de données

Nous effectuons les opérations suivantes:

1. Sélectionnez Tâches -> Générer des scripts pour la base de données portable.
2. Nous sélectionnons les objets nécessaires au transfert ou laissons la valeur par défaut (dans ce cas, des scripts seront créés pour tous les objets de la base de données).
3. Spécifiez les paramètres d'enregistrement du script. Il est plus pratique d'enregistrer le script dans un seul fichier codé en Unicode. Ensuite, en cas de panne, il n'est pas nécessaire de répéter à nouveau toutes les étapes.

Après avoir enregistré le script, il peut être exécuté sur le serveur SQL d'origine (ancienne version) pour créer la base de données requise.

Attention: après avoir exécuté le script, vous devez vérifier la conformité des paramètres de la base de données à partir de la sauvegarde et de la base de données créée par le script. Dans mon cas, il n'y avait aucun paramètre pour COLLATE dans le script, ce qui a conduit à un crash lors du transfert de données et de la danse avec un tambourin pour recréer la base de données à l'aide d'un script augmenté.

Transfert de données

Avant de transférer des données, vous devez désactiver la vérification de toutes les restrictions en fonction:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all' 

Nous transférons des données à l'aide de l'assistant d'importation de données Tâches -> Importer des données sur SQL Server, où se trouve la base de données créée par le script:

1. Spécifiez les paramètres de connexion source (SQL Server 2016 sur la machine virtuelle). J'ai utilisé Data Source SQL Server Native Client et l'authentification SQL susmentionnée.
2. Spécifiez les paramètres de connexion à la destination (SQL Server 2014 sur la machine hôte).
3. Ensuite, configurez le mappage. Vous devez sélectionner tous les objets non en lecture seule (par exemple, vous n'avez pas besoin de sélectionner des vues). En tant qu'options supplémentaires, sélectionnez "Autoriser l'insertion dans les colonnes d'identité" , si utilisé.
   Attention: si, lorsque vous essayez de sélectionner plusieurs tables et que vous définissez la propriété "Autoriser l'insertion dans les colonnes d'identité" , la propriété a déjà été définie pour au moins une des tables sélectionnées, la boîte de dialogue indiquera que la propriété est déjà définie pour toutes les tables sélectionnées. Ce fait peut prêter à confusion et conduire à des erreurs de transfert.
4. Nous commençons le transfert.
5. Restaurer la vérification des contraintes:
   

    EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all' 

Si des erreurs se produisent, vérifiez les paramètres, supprimez la base de données créée avec les erreurs, recréez-la à partir du script, apportez des corrections et répétez le transfert de données.

Conclusion

Cette tâche est assez rare et ne survient qu'en raison des limitations ci-dessus. La solution la plus courante consiste à mettre à niveau SQL Server ou à se connecter à un serveur distant si l'architecture de l'application le permet. Cependant, personne n'est à l'abri du code hérité et des mains tordues d' un développement de mauvaise qualité. J'espère que vous n'avez pas besoin de cette instruction, mais si vous en avez toujours besoin, cela vous fera gagner beaucoup de temps et de nerfs. Merci de votre attention!

Liste des sources utilisées

• Comment gérer les contraintes FK lors de l'importation de données à l'aide de l'assistant d'importation / exportation DTS?
• La colonne «Colonne 2» ne peut pas être traitée car plusieurs pages de codes (65001 et 1252) sont spécifiées pour elle.
• Comment puis-je me connecter à SQLServer exécuté sur VirtualBox à partir de mon Macbook hôte.
• SERVEUR SQL - Activer l'insertion d'identité - Assistant expert d'importation
• Dépannage de l'erreur 18456 de Microsoft SQL Server, échec de la connexion pour l'utilisateur