Procédure pas à pas : comparaison des schémas de deux bases de données

Article
08/10/2011

Cette rubrique s'applique à :

Visual Studio Ultimate	Visual Studio Premium	Visual Studio Professional	Visual Studio Express

Dans cette procédure pas à pas, vous comparez les schémas de deux bases de données en utilisant Visual Studio. L'action de comparaison de schémas génère également un script de langage de définition de données (DDL) à partir des différences. Vous utilisez ce fichier pour synchroniser le schéma de la cible et celui de la source. Pour plus d'informations, consultez Comparer et synchroniser des schémas de base de données.

Vous suivrez les procédures suivantes dans cette procédure pas à pas :

Création d'une base de données unique. Si vous avez suivi Procédure pas à pas : comparaison des schémas d'une base de données et d'un projet de base de données, vous pouvez utiliser la base de données obtenue et ignorer cette partie de la procédure pas à pas.
Création d'une base de données vide. La base de données CompareProjectDB fait office de source et vous créez une base de données vide pour qu'elle serve de cible.
Comparaison des schémas de deux bases de données. En comparant les schémas, vous trouvez les différences structurelles entre les bases de données, affichez les différences dans une table et générez un script DDL qui exprime les différences.
Examen du script de synchronisation. Vous vérifiez le script DDL que vous pouvez modifier avant de l'exécuter.
Mise à jour de la base de données cible. Vous exécutez le script DDL pour modifier le schéma de la cible.

Composants requis

Les produits suivants doivent être installés :

SQL Server 2008
Autorisations pour créer et mettre à jour des bases de données sur le serveur de base de données cible
Visual Studio Premium ou Visual Studio Ultimate

Création d'une base de données unique

Pour créer une base de données unique, procédez comme suit :

Créer un script qui contient un schéma de base de données
Créer un projet de base de données et importer le schéma
Déployer le projet de base de données dans un environnement de développement isolé

Créer un script qui contient un schéma de base de données

Pour créer un script duquel vous pouvez importer un schéma

Dans le menu Fichier, pointez sur Nouveau, puis cliquez sur Fichier.

La boîte de dialogue Nouveau fichier s'affiche.
Dans la liste Catégories, cliquez sur la catégorie Général si elle n'est pas déjà sélectionnée.
Dans la liste Modèles, cliquez sur Fichier Sql, puis sur Ouvrir.

L'éditeur Transact-SQL s'ouvre.

Copiez le code Transact-SQL suivant et collez-le dans l'éditeur Transact-SQL.

PRINT N'Creating Sales...';
GO
CREATE SCHEMA [Sales]
    AUTHORIZATION [dbo];
GO
PRINT N'Creating Sales.Customer...';
GO
CREATE TABLE [Sales].[Customer] (
    [CustomerID]   INT IDENTITY (1, 1) NOT NULL,
    [CustomerName] NVARCHAR (40) NOT NULL,
    [YTDOrders] INT NOT NULL,
    [YTDSales] INT NOT NULL
);
GO
PRINT N'Creating Sales.Orders...';
GO
CREATE TABLE [Sales].[Orders] (
    [CustomerID] INT NOT NULL,
    [OrderID] INT IDENTITY (1, 1) NOT NULL,
    [OrderDate] DATETIME NOT NULL,
    [FilledDate] DATETIME NULL,
    [Status] CHAR (1) NOT NULL,
    [Amount] INT NOT NULL
);
GO
PRINT N'Creating Sales.Def_Customer_YTDOrders...';
GO
ALTER TABLE [Sales].[Customer]
    ADD CONSTRAINT [Def_Customer_YTDOrders] DEFAULT 0 FOR [YTDOrders];
GO
PRINT N'Creating Sales.Def_Customer_YTDSales...';
GO
ALTER TABLE [Sales].[Customer]
    ADD CONSTRAINT [Def_Customer_YTDSales] DEFAULT 0 FOR [YTDSales];
GO
PRINT N'Creating Sales.Def_Orders_OrderDate...';
GO
ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [Def_Orders_OrderDate] DEFAULT GetDate() FOR [OrderDate];
GO
PRINT N'Creating Sales.Def_Orders_Status...';
GO
ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [Def_Orders_Status] DEFAULT 'O' FOR [Status];
GO
PRINT N'Creating Sales.PK_Customer_CustID...';
GO
ALTER TABLE [Sales].[Customer]
    ADD CONSTRAINT [PK_Customer_CustID] PRIMARY KEY CLUSTERED ([CustomerID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
GO
PRINT N'Creating Sales.PK_Orders_OrderID...';
GO
ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [PK_Orders_OrderID] PRIMARY KEY CLUSTERED ([OrderID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
GO
PRINT N'Creating Sales.FK_Orders_Customer_CustID...';
GO
ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [FK_Orders_Customer_CustID] FOREIGN KEY ([CustomerID]) REFERENCES [Sales].[Customer] ([CustomerID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
PRINT N'Creating Sales.CK_Orders_FilledDate...';
GO
ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [CK_Orders_FilledDate] CHECK ((FilledDate >= OrderDate) AND (FilledDate < '01/01/2010'));
GO
PRINT N'Creating Sales.CK_Orders_OrderDate...';
GO
ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [CK_Orders_OrderDate] CHECK ((OrderDate > '01/01/2005') and (OrderDate < '01/01/2020'));
GO
PRINT N'Creating Sales.uspCancelOrder...';
GO
CREATE PROCEDURE [Sales].[uspCancelOrder]
@OrderID INT
AS
BEGIN
DECLARE @Delta INT, @CustomerID INT
BEGIN TRANSACTION
    SELECT @Delta = [Amount], @CustomerID = [CustomerID]
     FROM [Sales].[Orders] WHERE [OrderID] = @OrderID;

UPDATE [Sales].[Orders]
   SET [Status] = 'X'
WHERE [OrderID] = @OrderID;

UPDATE [Sales].[Customer]
   SET
   YTDOrders = YTDOrders - @Delta
    WHERE [CustomerID] = @CustomerID
COMMIT TRANSACTION
END
GO
PRINT N'Creating Sales.uspFillOrder...';
GO
CREATE PROCEDURE [Sales].[uspFillOrder]
@OrderID INT, @FilledDate DATETIME
AS
BEGIN
DECLARE @Delta INT, @CustomerID INT
BEGIN TRANSACTION
    SELECT @Delta = [Amount], @CustomerID = [CustomerID]
     FROM [Sales].[Orders] WHERE [OrderID] = @OrderID;

UPDATE [Sales].[Orders]
   SET [Status] = 'F',
       [FilledDate] = @FilledDate
WHERE [OrderID] = @OrderID;

UPDATE [Sales].[Customer]
   SET
   YTDSales = YTDSales - @Delta
    WHERE [CustomerID] = @CustomerID
COMMIT TRANSACTION
END
GO
PRINT N'Creating Sales.uspNewCustomer...';
GO
CREATE PROCEDURE [Sales].[uspNewCustomer]
@CustomerName NVARCHAR (40)
AS
BEGIN
INSERT INTO [Sales].[Customer] (CustomerName) VALUES (@CustomerName);
SELECT SCOPE_IDENTITY()
END
GO
PRINT N'Creating Sales.uspPlaceNewOrder...';
GO
CREATE PROCEDURE [Sales].[uspPlaceNewOrder]
@CustomerID INT, @Amount INT, @OrderDate DATETIME, @Status CHAR (1)='O'
AS
BEGIN
DECLARE @RC INT
BEGIN TRANSACTION
INSERT INTO [Sales].[Orders] (CustomerID, OrderDate, FilledDate, Status, Amount) 
     VALUES (@CustomerID, @OrderDate, NULL, @Status, @Amount)
SELECT @RC = SCOPE_IDENTITY();
UPDATE [Sales].[Customer]
   SET
   YTDOrders = YTDOrders + @Amount
    WHERE [CustomerID] = @CustomerID
COMMIT TRANSACTION
RETURN @RC
END
GO

Dans le menu Fichier, cliquez sur Enregistrer SqlQuery_1.sql sous.

La boîte de dialogue Enregistrer le fichier sous apparaît.
Dans Nom de l'objet, tapez SampleImportScript.sql.

Vous pouvez enregistrer le fichier dans un emplacement quelconque sur votre ordinateur. Prenez note de l'emplacement car vous allez utiliser ce script dans la procédure suivante.
Cliquez sur Enregistrer.
Dans le menu Fichier, cliquez sur Fermer la solution.

Ensuite, créez un projet de base de données et importez le schéma depuis le script que vous avez créé.

Créer un projet de base de données et importer un schéma

Pour créer un projet de base de données

Dans le menu Fichier, pointez sur Nouveau, puis cliquez sur Projet.

La boîte de dialogue Nouveau projet s'affiche.
Sous Modèles installés, développez le nœud Base de données, puis cliquez sur SQL Server.
Dans la liste des modèles, cliquez sur Projet de base de données SQL Server 2008.
Dans la zone Nom, tapez CompareProjectDB.
Dans la liste Solution, cliquez sur Créer la solution si l'option n'est pas déjà sélectionnée.
Activez la case à cocher Créer le répertoire pour la solution si elle ne l'est pas déjà.
Désactivez la case à cocher Ajouter au contrôle de code source si elle ne l'est pas déjà, puis cliquez sur OK.

Le projet de base de données est créé et s'affiche dans l'Explorateur de solutions. Vous importez ensuite le schéma de base de données à partir d'un script.

Pour importer un schéma de base de données à partir d'un script

Dans le menu Projet, cliquez sur Importer le script.
Cliquez sur Suivante après avoir lu la page d'accueil.
Cliquez sur Parcourir et indiquez le chemin d'accès où vous avez enregistré le fichier SampleImportScript.sql.
Double-cliquez sur le fichier SampleImportScript.sql et cliquez sur Terminer.

Le script est importé, et les objets définis dedans sont ajoutés à votre projet de base de données.
Examinez le résumé, puis cliquez sur Terminer pour finaliser l'opération.

Notes

La procédure Sales.uspFillOrder contient une erreur de codage intentionnelle que vous détectez et corrigez dans la procédure pas à pas de test unitaire.

Pour examiner le projet résultant

Dans l'Explorateur de solutions, développez le nœud enfant Objets de schéma.
Accédez aux sous-nœuds du nœud Objets de schéma dans la hiérarchie.

L'Explorateur de solutions contient les fichiers qui définissent les objets de base de données.
Dans le menu Affichage, cliquez sur Vue Schéma de base de données.
Dans Vue Schéma, développez le nœud CompareProjectDB.
Explorez les sous-nœuds situés sous le nœud CompareProjectDB dans la hiérarchie.

La vue Schéma contient les objets définis dans les fichiers qui apparaissent dans l'Explorateur de solutions.

Déploiement dans un environnement de développement isolé

Ensuite, déployez le projet pour créer une base de données qui contient le schéma importé, mais aucune donnée. Créez cette base de données dans un environnement de développement isolé ou dans un bac à sable (sandbox). Par conséquent, vous pouvez développer et tester la base de données, sans interférence avec les autres opérations.

Pour configurer et générer le projet de base de données

Dans l'Explorateur de solutions, cliquez sur le projet de base de données CompareProjectDB.
Dans le menu Projet, cliquez sur Propriétés CompareProjectDB.

La boîte de dialogue des propriétés du projet s'affiche.
Cliquez sur l'onglet Déployer.
Dans la liste Configurer les paramètres de déploiement pour, cliquez sur Mon environnement de développement isolé. En configurant des paramètres pour votre environnement de développement isolé, vous pouvez appliquer d'autres paramètres de déploiement que ceux qui seront utilisés dans vos autres environnements, tels que le serveur de test ou de production.
Dans la liste Action de déploiement, cliquez sur Créer un script de déploiement (.sql) et le déployer vers la base de données.
Dans Paramètres de la base de données cible, cliquez sur Modifier.

La boîte de dialogue Propriétés de connexion s'affiche.
Définissez les propriétés de connexion pour la base de données que vous voulez créer, puis cliquez sur OK.

Dans la zone Connexion cible, la chaîne de connexion correcte s'affiche.

Avertissement

Vous devez créer la base de données sur un serveur de test, un serveur de développement ou sur votre ordinateur local. Vous ne devez pas spécifier votre serveur de production.
Dans Nom de la base de données cible, tapez CompareProjectDB.
En regard de Fichier de configuration de déploiement, cliquez sur Modifier.
Désactivez la case à cocher Bloquer le déploiement incrémentiel si une perte de données peut se produire.

Notes

Pour cette procédure pas à pas, vous allez tester les procédures stockées en les comparant avec une base de données vide que vous déployez dans le cadre du test unitaire de base de données. Vous n'allez pas conserver de données existantes dans la mesure où vous allez tester les procédures stockées dans votre environnement de développement isolé.
Dans le menu Fichier, cliquez sur Enregistrer tout.
Dans le menu Générer, cliquez sur Générer la solution.

Les propriétés que vous venez de définir déterminent la façon dont le script de déploiement est généré. L'état de la génération apparaît dans la fenêtre Sortie, et Génération : 1 a réussi ou est à jour doit apparaître sur la dernière ligne.

Pour déployer le projet de base de données

Dans l'Explorateur de solutions, cliquez sur le projet de base de données CompareProjectDB.
Dans le menu Générer, cliquez sur Déployer CompareProjectDB.

Avertissement

Vous devez exécuter ce déploiement sur un serveur de test, un serveur de développement ou votre ordinateur local. Vous ne devez pas spécifier votre serveur de production.

Le projet de base de données est déployé vers une nouvelle base de données. L'état du déploiement s'affiche dans la fenêtre Sortie, et Le déploiement a réussi doit apparaître sur la dernière ligne. Créez ensuite une base de données vide que vous pouvez comparer à la base de données déployée.

Création d'une base de données vide

Pour créer une base de données vide

Dans le menu Fichier, pointez sur Nouveau, puis cliquez sur Fichier.

La boîte de dialogue Nouveau projet s'affiche.
Dans la liste Modèles installés, développez le nœud Base de données, puis cliquez sur SQL Server.
Cliquez sur Assistant SQL Server 2008.
Dans Nom, tapez EmptyDB.
Acceptez les valeurs par défaut pour les champs restants, puis cliquez sur OK.
Dans l'Assistant Nouveau projet de base de données, cliquez sur Configurer la génération/le déploiement.
Dans la liste Action de déploiement, cliquez sur Créer un script de déploiement (.sql) et le déployer vers la base de données.
Dans Connexion cible, cliquez sur le bouton Parcourir.
Spécifiez la connexion au serveur de base de données sur lequel vous souhaitez créer la base de données vide, puis cliquez sur Terminer.

Un projet de base de données nommé EmptyDB est alors créé et apparaît dans l'Explorateur de solutions.
Dans l'Explorateur de solutions, cliquez sur le projet de base de données EmptyDB.
Dans le menu Générer, cliquez sur Déployer EmptyDB.

Le projet de base de données est généré et déployé vers le serveur spécifié.

Comparaison des schémas de deux bases de données

Pour comparer les schémas de deux bases de données

Dans le menu Données, pointez sur Comparaison de schémas, puis cliquez sur Nouvelle comparaison de schémas.

La boîte de dialogue Nouvelle comparaison de schémas apparaît afin que vous puissiez spécifier la source et la cible. Dans cette boîte de dialogue, vous identifiez les serveurs sur lesquels la source et la cible résident, le nom de chaque base de données et le type d'authentification à utiliser lorsque vous vous connectez à chaque base de données.

De plus, la fenêtre Comparaison de schémas s'ouvre en arrière-plan et Visual Studio lui assigne automatiquement un nom, tel que SchemaCompare1.
Sous Schéma source, cliquez sur Base de données, puis sur la connexion qui correspond à la base de données CompareProjectDB.

Si aucune connexion de ce type ne s'affiche dans la liste, cliquez sur Nouvelle connexion. Dans la boîte de dialogue Propriétés de connexion, identifiez le serveur sur lequel se trouve la base de données CompareProjectDB et le type d'authentification à utiliser lorsque vous vous y connectez, ainsi que la base de données elle-même. Lorsque vous avez terminé, cliquez sur OK.

Notes

Une fois une connexion établie, elle apparaît dans l'Explorateur de serveurs sous Connexions de données.
Sous Schéma cible, cliquez sur Base de données, puis sur la connexion qui correspond à la base de données EmptyDB.

Si aucune connexion de ce type ne s'affiche dans la liste, cliquez sur Nouvelle connexion. Dans la boîte de dialogue Propriétés de connexion, identifiez le serveur sur lequel se trouve la base de données EmptyDB et le type d'authentification à utiliser lorsque vous vous y connectez, ainsi que la base de données elle-même. Lorsque vous avez terminé, cliquez sur OK.
Cliquez sur Options pour spécifier les objets à comparer, les types de différences à ignorer et le contenu du script de mise à jour généré.

Notes

Lorsque vous comparez des bases de données, vous ne spécifiez pas de variables SQLCMD.
Dans l'onglet Général, développez le nœud Options de comparaison.
Sélectionnez Ignorer les noms de fichiers et les chemins d'accès pour les fichiers et fichiers journaux.
Cliquez sur OK.
Cliquez sur OK.

La comparaison de schémas commence.

Notes

Pour arrêter une comparaison en cours, vous pouvez ouvrir le menu Données, pointer sur Comparaison de schémas, puis cliquer sur Arrêter la comparaison de schémas.

Vous pouvez configurer des options pour spécifier ce qui est considéré comme une différence et modifier le mode de création du script de mise à jour. Pour plus d'informations, consultez Comment : définir des options pour comparer des schémas de base de données.

Lorsque la comparaison est terminée, les différences structurelles entre les deux bases de données apparaissent dans la table de la fenêtre Comparaison de schémas. La table affiche une ligne pour chaque objet de base de données qui existe dans l'une des bases de données. Les objets de base de données sont classés par type : tables, vues, procédures stockées, rôles, etc.

Mise à jour de la base de données cible

Pour mettre à jour le schéma de la cible, vous avez deux possibilités. Vous pouvez mettre à jour le schéma directement depuis la fenêtre Comparaison de schémas ou à l'aide de l'éditeur Transact-SQL. Cette section décrit les deux possibilités.

Après avoir exécuté la comparaison de schémas, les différences structurelles apparaissent dans la table de la fenêtre Comparaison de schémas. Pour chaque objet de la colonne CompareProjectDB (base de données source), la table affiche une action dans la colonne Mettre à jour l'action nécessaire à la synchronisation de cet objet dans les deux bases de données. Dans ce cas, dans la mesure où la cible est vide à l'exception des objets par défaut, la colonne État contient principalement des états Nouveau et la colonne Mettre à jour l'action contient principalement des actions Créer.

Écrire les mises à jour dans la cible

Vous pouvez mettre à jour le schéma de la cible à l'aide des actions de mise à jour répertoriées dans la fenêtre Comparaison de schémas. Pour effectuer cette tâche, suivez les étapes d'Écrire des mises à jour dans une base de données cible.

Afficher le script, puis écrire les mises à jour dans la cible

Vous pouvez exporter un script de mise à jour, l'examiner, éventuellement le modifier, puis l'utiliser pour synchroniser la base de données cible. Pour effectuer ces tâches, suivez les étapes d'Examiner et exécuter le script de synchronisation.

Écriture de mises à jour dans la base de données cible

Notes

Avant d'écrire des mises à jour sur un serveur de production ou de développement, vous devez envisager de sauvegarder votre base de données cible. Étant donné que certaines modifications de schémas ne peuvent pas être exécutées dans une transaction unique, vous pouvez perdre des données si vous démarrez puis annulez une opération de mise à jour. Par exemple, une table de la base de données cible a pu être supprimée au cours de la préparation de sa recréation. Si vous annulez la mise à jour à ce moment-là, vous pouvez perdre la table. Dans cette procédure pas à pas, vous mettez à jour une base de données de développement vide. Par conséquent, vous ne sauvegardez pas la base de données cible.

Pour écrire des mises à jour dans la base de données cible

Dans la liste de résultats de la comparaison, faites défiler jusqu'à atteindre le nœud Fichiers SQL.
Pour les fichiers CompareProjectDB et CompareProjectDB_Log, faites passer la valeur de Mettre à jour l'action de Créer à Ignorer.

Notes

Si vous comparez deux bases de données présentes sur le même serveur de base de données, la synchronisation échoue car les données cible et les fichiers journaux existent déjà et sont utilisés par la base de données source. Pour cette procédure pas à pas, vous ignorez les mises à jour des fichiers et synchronisez le contenu de la base de données.
Pour les fichiers EmptyDB et EmptyDB_log, faites passer la valeur de Mettre à jour l'action de Supprimer à Ignorer.

Notes

Étant donné que vous ne créez pas les fichiers à partir de la base de données source, vous ne devez pas supprimer les fichiers pour la base de données cible. Pour cette procédure pas à pas, vous ignorez les mises à jour des fichiers et synchronisez le contenu de la base de données.
Dans la barre d'outils Comparaison de schémas, cliquez sur Écrire les mises à jour.

Les actions de mise à jour répertoriées dans la fenêtre Comparaison de schémas sont exécutées. Cette synchronisation modifie le schéma de la cible pour correspondre à celui de la source.

Notes

Pendant le déroulement de l'action de mise à jour, vous pouvez annuler l'opération en ouvrant le menu Données, en pointant sur Comparaison de schémas, puis en cliquant sur Arrêter l'écriture dans la cible.

Si vous souhaitez réexécuter la comparaison pour vérifier que les mises à jour sélectionnées ont été appliquées, vous pouvez cliquer sur Actualiser dans la barre d'outils Comparaison de schémas.

Examen et exécution du script de synchronisation

Pour examiner le script de synchronisation

Dans le menu Données, pointez sur Comparaison de schémas, sur Exporter vers, puis cliquez sur Éditeur.

Vous pouvez également cliquer sur Exporter vers l'éditeur dans la barre d'outils Comparaison de schémas.

L'éditeur Transact-SQL s'ouvre en mode déconnecté et affiche le script de synchronisation Transact-SQL. Cette fenêtre possède un nom tel que Server.CompareProjectDB - SchemaUpdate_EmptyDB_1.sql et affiche le script Transact-SQL. Dans la mesure où vous disposez d'un accès en écriture et en lecture à cette fenêtre, vous pouvez modifier le script. Si vous le modifiez, ouvrez le menu Fichier, puis cliquez sur Enregistrer. Lorsque vous enregistrez le fichier, vous pouvez spécifier ses nom et chemin d'accès.
Dans la barre d'outils Éditeur Transact-SQL, cliquez sur Mode SQLCMD.

Si vous n'activez pas le mode SQLCMD, les erreurs apparaissent lorsque vous tentez d'exécuter le script.
Pour synchroniser les schémas des deux bases de données, exécutez ce script d'un simple clic sur Exécuter SQL dans la barre d'outils Éditeur Transact-SQL ou d'une simple pression sur F5.

La boîte de dialogue Se connecter à la base de données s'affiche.
Cliquez sur la connexion qui correspond à la base de données EmptyDB, puis sur OK.

Avertissement

Si vous tentez d'exécuter le script de mise à jour par rapport à une autre base de données, il se peut que vous provoquiez des résultats non souhaités.

La comparaison n'est pas actualisée automatiquement. Si vous souhaitez réexécuter la comparaison pour vérifier que les mises à jour sélectionnées ont été appliquées, vous devez cliquer sur Actualiser dans la barre d'outils Comparaison de schémas.

Étapes suivantes

Vous pouvez à présent comparer les données dans les deux bases de données. Pour plus d'informations, consultez Comment : comparer et synchroniser les données de deux bases de données.