Migration d'une base de données vers une ancienne version de MS SQL Server

Vous disposez d'une base de données MS SQL Server que vous devez transférer vers un autre ordinateur physique. Vous avez déjà effectué une sauvegarde et vous êtes heureux de vous lancer dans une récupération. Mais il s'avère que sur l'ordinateur sur lequel vous souhaitez transférer la base de données, une ancienne version de MS SQL Server est installée. Stack Overflow vous assure que tout va mal. Mais est-ce vraiment le cas?


Bien sûr, le transfert d'une base de données d'une version plus récente vers une ancienne n'est pas un scénario classique et pas le plus correct. Mais souvent, les bases de données sont créées de manière à prendre en charge de plus en plus de nouvelles versions de SQL, à commencer par certaines, par exemple, 2008 R2, car la compatibilité directe avec MS SQL est plus qu'excellente. Et, par exemple, votre client a déjà installé MS SQL 2016 pour lui-même, et vous avez MS SQL 2014 installé sur votre serveur de test pour le développement. Et vous souhaitez étendre votre base de clients pour savoir où il y a confusion des données.

Microsoft a renié le problème - ils disent qu'ils n'ont pas de compatibilité descendante et qu'il n'y a pas de faillite. La sauvegarde créée sur le serveur le plus récent ne peut pas être restaurée sur l'ancien serveur. Oui, ils ont des outils comme DTS, copier une base de données, exporter-importer, etc. Mais ils sont si gênants et encombrants que le transfert normal d'une grande base de données avec de nombreuses tables pour les utiliser n'est pas particulièrement pratique. En tout cas, personnellement, je n'ai pas réussi.

Oui, vous pouvez générer des scripts SQL pour la base de données entière, y compris les données. Mais imaginez, vous avez un tas de champs d'objets blob contenant des données volumineuses dans la base de données, et en général la taille de la base de données entière est de 500+ Go. Imaginez combien un tel script prendra, combien de temps il sera généré et exécuté.

Ainsi, la tâche consiste à recréer avec précision la base de données (structure et données) à partir de la nouvelle version de MS SQL Server dans l'ancienne version. J'ai trouvé une solution assez simple que je veux partager. Bien sûr, cette solution a un nombre important de limitations, mais à mon avis, elle est toujours meilleure que DTS et les scripts.

La limitation numéro un est que vous avez besoin d'accéder via MS SQL Management Studio aux deux serveurs - anciens et nouveaux. Si cela n'est pas possible, cela devrait être possible sur la machine à partir de laquelle vous souhaitez transférer la base de données, installez la version de SQL dans laquelle vous devez transférer la base de données afin de transférer d'abord la base de données localement vers cette version, puis faites-la glisser via la sauvegarde ou directement via * df fichiers de base de données (via Détacher / Attacher) à la nouvelle machine (la version de SQL Server dans ce cas correspondra déjà).

Une autre limitation est que vous aurez besoin d'un script de schéma de base de données (tous les objets, y compris les tables, les index, les constantes, les procédures stockées, les déclencheurs, etc.) sans données, et les instructions pour créer des contraintes de clé étrangère doivent aller dans ce script à la toute fin, séparé du script pour créer les tables elles-mêmes.

Je décrirai brièvement l'algorithme de transfert de données lui-même. Toutes les actions sont effectuées dans une session Management Studio connectée au serveur vers lequel vous souhaitez transférer la base de données.

1) Sur le nouveau serveur, créez une base de données vide avec les mêmes fichiers et groupes de fichiers que la base de données portable.

2) En utilisant le script de schéma de base de données, nous créons tous les objets de base de données (tables, index, vues, déclencheurs, procédures et fonctions stockées), mais sans créer de contraintes de clé étrangère. Vous ne pouvez pas créer FK à ce stade, car ils interféreront avec l'insertion des données.

3) Nous connectons la base de données à partir de laquelle nous transférerons les données en tant que serveur lié, afin que vous puissiez utiliser l'ancienne base de données dans les requêtes vers la nouvelle base de données.

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

4) Parce que les structures de base de données sont les mêmes, nous utiliserons la procédure stockée intégrée sp_msforeachtable, qui vous permet d'effectuer une requête sur chaque table de base de données pour générer un script pour transférer des données de l'ancienne base de données vers la nouvelle via une requête du formulaire

 INSERT INTO ? SELECT * FROM ? 

Au lieu d'un point d'interrogation, sp_msforeachtable remplace le nom de chaque table et exécute la requête plusieurs fois (une fois pour chaque table).

Ici, je suis tombé sur le plus grand nombre de râteaux.

a) Le problème numéro un est que pour les tables avec des champs IDENTITY, vous devez appeler:

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

b) Le problème numéro deux est que vous ne pouvez pas effectuer cet appel sur des tables qui n'ont pas de champs IDENTITY, vous devez donc déterminer dynamiquement si la table a une colonne IDENITY ou non.

Cela peut être fait avec cette requête:

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

c) Le problème numéro trois est que, en fin de compte, en mode IDENITY_INSERT ON, vous ne pouvez pas

 INSERT INTO ... SELECT * FROM ... 

, mais vous devez répertorier des champs spécifiques.

Vous pouvez répertorier les champs de table dans une ligne avec cette requête:

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

4) Nous générons un script d'insertion pour toutes les tables:


5) Nous exécutons le script de transfert de données généré

6) Nous exécutons un script pour créer toutes les contraintes de clé étrangère (maintenant c'est déjà possible).

7) C'est fait! Vous avez transféré la base de données du nouveau serveur SQL vers l'ancien, bien que cela ait été considéré comme impossible. De plus, le transfert n'est effectué qu'une fois et demi plus lentement que le taux de transfert de données sur le réseau, c'est-à-dire assez vite.

8) Nous nettoyons après nous-mêmes (nous déconnectons le serveur lié):

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

Limitations de la méthode.

1) En utilisant une méthode similaire, il ne sera pas possible de transférer des tables contenant des colonnes de type XML.
Il existe certainement de nombreuses autres restrictions, comme la base de données que j'ai transférée de cette manière n'utilisait pas beaucoup des fonctionnalités du serveur SQL. Vous pouvez écrire sur les restrictions dans les commentaires, et j'y ajouterai un article.

Merci de votre attention! J'espère que cela aide quelqu'un.