COMMENT FAIRE : Déplacer des bases de données entre des ordinateurs qui exécutent SQL Server

Traductions disponibles Traductions disponibles
Numéro d'article: 314546 - Voir les produits auxquels s'applique cet article
Agrandir tout | Réduire tout

Sommaire

Résumé

Cet article explique étape par étape comment déplacer des bases de données d'utilisateurs SQL Server et les composants SQL Server les plus courants entre des ordinateurs qui exécutent SQL Server.

Les étapes décrites dans cet article supposent que vous allez déplacer les bases de données système master, model, tempdb ou msdb. Ces étapes vous proposent différentes options pour transférer les noms d'accès et les composants les plus courants contenus dans les bases de données master et msdb. Pour plus d'informations sur la procédure pour déplacer les bases de données système, consultez les articles de la Base de connaissances Microsoft répertoriés dans la section "Étape 1 : Comment faire pour déplacer des bases de données d'utilisateurs" de cet article. En outre, si vous transférez les bases de données système, ne suivez pas "Étape 2  Comment faire pour transférer des noms d'accès et des mots de passe" à "Étape 5 : Comment faire pour déplacer des lots DTS".

Pour plus d'informations sur les éléments spécifiques non transférés lorsque vous suivez les étapes de cet article, reportez-vous à la section "Plus d'informations" de cet article.

Étape 1 : Comment faire pour déplacer des bases de données d'utilisateurs

Vous pouvez déplacer des bases de données d'utilisateurs à partir de SQL Server 7.0 vers SQL Server 2000 ou entre des serveurs qui exécutent SQL Server 7.0 et SQL Server 2000 en utilisant l'une des méthodes suivantes : Une description de chaque méthode figure ci-dessous.

Sauvegarder et restaurer

Sauvegardez les bases de données d'utilisateurs sur le serveur source, puis restaurez les bases de données d'utilisateurs sur le serveur de destination.
  • La base de données peut être utilisée lors du processus de sauvegarde. Si les utilisateurs effectuent des instructions INSERT, UPDATE ou DELETE sur la base de données après la fin du processus de sauvegarde, la sauvegarde ne contiendra pas ces changements. Si vous devez transférer tous les changements, vous pouvez transférer les changements avec un temps d'indisponibilité minimal si vous effectuez à la fois une sauvegarde du journal des transactions et une sauvegarde de la base de données complète.

    1. Restaurez la sauvegarde de la base de données complète sur le serveur de destination et indiquez l'option WITH NORECOVERY.

      REMARQUE : pour empêcher des modifications supplémentaires sur la base de données, dites aux utilisateurs d'arrêter leur activité sur la base de données du serveur source.
    2. Effectuez une sauvegarde du journal des transactions et restaurez la sauvegarde du journal des transactions sur le serveur de destination en utilisant l'option WITH RECOVERY. Le temps d'indisponibilité se limite à la durée de la sauvegarde et de la restauration du journal des transactions. Pour plus d'informations, consultez la sous-rubrique "RESTORE" dans la rubrique "Référence de Transact-SQL" de la documentation en ligne de SQL Server.
  • La base de données sur le serveur de destination ne sera pas de la même taille que la base de données sur le serveur source. Pour réduire la taille de la base de données, vous devez soit rétrécir la base de données source avant d'effectuer la sauvegarde soit rétrécir la base de données de destination après la fin de la restauration. Pour plus d'informations, consultez la sous-rubrique "Réduction d'une base de données" dans la rubrique "Création et maintenance de bases de données" de la documentation en ligne de SQL Server.
  • Si vous restaurez la base de données vers un emplacement de fichier différent de celui de la base de données source, vous devez indiquer l'option WITH MOVE. Par exemple, sur le serveur source, la base de données est dans le dossier D:\Mssql\Data. Le serveur de destination ne possède pas de lecteur D et vous voulez restaurer la base de données vers le dossier C:\Mssql\Data. Pour plus d'informations sur la procédure de restauration d'une base de données vers un emplacement différent, cliquez sur les numéros ci-dessous pour afficher les articles correspondants dans la Base de connaissances :
    221465 INF : Utilisation de l'option WITH MOVE avec l'instruction RESTORE
    304692 INF : Déplacement de bases de données SQL Server vers un nouvel emplacement avec BACKUP et RESTORE
  • Si vous voulez remplacer une base de données préexistante sur le serveur de destination, vous devez indiquer l'option WITH REPLACE. Pour plus d'informations, consultez la sous-rubrique "RESTORE" dans la rubrique "Référence de Transact-SQL" de la documentation en ligne de SQL Server.
  • Selon la version de SQL Server vers laquelle vous restaurez, il se peut que le jeu de caractères, l'ordre de tri et le classement Unicode doivent être les mêmes sur les serveurs source et de destination. Pour plus d'informations, reportez-vous à la section "Remarque relative au classement" de cet article.

Les procédures stockées Sp_detach_db et Sp_attach_db

Pour utiliser les procédures stockées sp_detach_db et sp_attach_db, procédez comme suit :
  1. Détachez la base de données du serveur source en utilisant la procédure stockée sp_detach_db. Vous devez copier les fichiers .mdf, .ndf et .ldf associés à la base de données vers le serveur de destination. Consultez ce tableau pour une description des types de fichier :

    Réduire ce tableauAgrandir ce tableau
    Extension du nom de fichierDescription
    .mdf Fichier de données principal
    .ndf Fichier de données secondaire
    .ldf Fichier journal des transactions
  2. Attachez la base de données sur le serveur de destination en utilisant la procédure stockée sp_attach_db et en pointant sur les fichiers que vous avez copiés vers le serveur de destination à l'étape précédente.Pour plus d'informations sur l'utilisation de ces méthodes, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
    224071 INF : Déplacement de bases de données SQL Server vers un nouvel emplacement en utilisant Détacher/Attacher
  • La base de données est inaccessible après le détachement et vous ne pouvez pas l'utiliser pendant que vous copiez les fichiers. Toutes les données contenues dans la base de données au moment du détachement sont déplacées.
  • Il se peut que le jeu de caractères, l'ordre de tri et le classement Unicode doivent être les mêmes sur les deux serveurs lorsque vous utilisez la méthode Attacher ou Détacher. Pour plus d'informations, reportez-vous à la section "Remarque relative au classement" de cet article.

Remarque relative au classement

Si vous déplacez des bases de données entre des serveurs SQL Server 7.0 en utilisant les méthodes sauvegarder et restaurer ou Attacher et Détacher, le jeu de caractères, l'ordre de tri et le classement Unicode doivent être les mêmes sur les deux serveurs. Si vous déplacez des bases de données de SQL Server 7.0 vers SQL Server 2000 ou entre des serveurs SQL Server 2000, la base de données retient le classement de la base de données source. Cela signifie que si le serveur de destination qui exécute SQL Server 2000 a un classement différent de la base de données source, la base de données de destination a un classement différent des bases de données master, model, tempdb et msdb du serveur de destination. Pour plus d'informations, consultez la rubrique "Environnements de classements mixtes" de la documentation en ligne de SQL Server 2000.

Importer et exporter des données (copier des objets et des données entre des bases de données SQL Server)

Vous pouvez copier une base de données complète ou copier des objets et des données de façon sélective à partir de la base de données source vers la base de données de destination en utilisant l'Assistant Importation et exportation de données des services DTS (Data Transformation Services).
  • La base de données source peut être utilisée pendant le transfert. Le cas échéant, vous remarquerez certains blocages pendant le transfert.
  • Lorsque vous utilisez l'Assistant Importation et exportation de données, le jeu de caractères, l'ordre de tri et le classement peuvent être différents entre le serveur source et le serveur de destination.
  • Étant donné que l'espace non utilisé dans la base de données source ne bouge pas, la base de données de destination peut ne pas être aussi volumineuse que la base de données source. De même, si vous déplacez uniquement certains objets, la base de données de destination peut ne pas être aussi volumineuse que la base de données source.
  • Les services DTS (Data Transformation Services) SQL Server 7.0 peuvent ne pas transférer plus de 64 Ko de données de texte et d'image correctement. Ce problème ne s'applique pas à la version SQL Server 2000 des services DTS (Data Transformation Services). Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
    257425 CORRECTIF : Le transfert d'objets DTS ne transfère pas plus de 64 Ko de données BLOB

Étape 2 : Comment faire pour transférer des noms d'accès et des mots de passe

Si vous ne transférez pas les noms d'accès à partir du serveur source vers le serveur de destination, il se peut que vos utilisateurs SQL Server ne puissent pas ouvrir de session sur le serveur de destination. Vous pouvez transférer les noms d'accès et les mots de passe en utilisant les instructions de l'article suivant dans la Base de connaissances Microsoft :
246133 INF : Comment faire pour transférer des noms d'accès et des mots de passe entre des serveurs SQL
Les bases de données par défaut pour les noms d'accès sur le serveur de destination peuvent être différentes de la base de données par défaut pour les noms d'accès sur le serveur source. Vous pouvez modifier la base de données par défaut pour une ouverture de session avec la procédure stockée sp_defaultdb. Pour plus d'informations, consultez la sous-rubrique "sp_defaultdb" de la rubrique "Référence de Transact-SQL" dans la documentation en ligne de SQL Server.

Étape 3 : Comment faire pour résoudre les problèmes d'utilisateurs orphelins

Après avoir transféré les noms d'accès et les mots de passe vers le serveur de destination, il se peut que vos utilisateurs ne puissent pas accéder à la base de données. Les noms d'accès sont associés aux utilisateurs par l'identificateur de sécurité (SID) et si le SID est incohérent après le déplacement d'une base de données, SQL Server peut refuser à l'utilisateur l'accès à la base de données. Ce problème est connu sous l'expression utilisateur orphelin. Si vous transférez les noms d'accès et les mots de passe en utilisant la fonction Transférer les noms d'accès des services DTS SQL Server 2000, vous obtiendrez probablement des utilisateurs orphelins. En outre, l'accès accordé à des noms d'accès intégrés sur un serveur de destination dans un domaine différent de celui du serveur source engendre des utilisateurs orphelins.
  1. Rechercher les utilisateurs orphelins. Ouvrez l'Analyseur de requêtes sur le serveur de destination, puis exécutez le code suivant dans la base de données d'utilisateurs que vous avez déplacée :
    exec sp_change_users_login 'Report'
    					
    Cette procédure répertorie tous les utilisateurs orphelins qui ne sont pas liés à une ouverture de session. Si aucun utilisateur n'est répertorié, passez les étapes 2 et 3 et continuez à l'étape 4.

  2. Résoudre les problèmes d'utilisateurs orphelins. S'il existe un utilisateur orphelin, les utilisateurs de la base de données peuvent ouvrir une session sur le serveur mais ils n'ont pas le droit d'accéder à la base de données. Si vous essayez d'accorder l'accès d'ouverture de session à la base de données, le message d'erreur suivant s'affiche car l'utilisateur existe déjà :
    Microsoft SQL-DMO (ODBC SQLState : 42000) Erreur 15023 : L'utilisateur ou le rôle '%s' existe déjà dans la base de données.
    Pour plus d'informations sur la procédure pour résoudre ces problèmes d'utilisateurs orphelins, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
    240872 INF : Comment faire pour résoudre les problèmes d'autorisations lorsqu'une base de données est déplacée entre des serveurs SQL
    L'article ci-dessus contient des instructions sur le mappage des noms d'accès sur les utilisateurs de la base de données et la résolution des problèmes d'utilisateurs orphelins à partir de noms d'accès SQL Server standard et de noms d'accès intégrés.
    - ou -

    274188 La rubrique "Dépannage des utilisateurs orphelins" dans la documentation en ligne est incomplète
    L'article ci-dessus explique comment utiliser la procédure stockée sp_change_users_login pour corriger les problèmes d'utilisateurs orphelins un par un. La procédure stockée sp_change_users_login résout uniquement les problèmes d'utilisateurs orphelins à partir de noms d'accès SQL Server standard.
  3. Si le propriétaire de la base de données (dbo) est répertorié comme orphelin, exécutez ce code dans la base de données d'utilisateurs :
    exec sp_changedbowner 'sa'  
    						
    La procédure stockée modifie le propriétaire de la base de données en dbo et corrige le problème. Pour modifier le propriétaire de la base de données vers un autre utilisateur, exécutez sp_changedbowner à nouveau avec l'utilisateur de votre choix. Pour plus d'informations, consultez la sous-rubrique "sp_changedbowner" dans la rubrique "Référence de Transact-SQL" de la documentation en ligne de SQL Server.
  4. Si votre serveur de destination exécute le Service Pack 1 SQL Server 2000, l'utilisateur propriétaire de la base de données peut ne pas apparaître dans la liste du dossier Utilisateurs dans Enterprise Manager après l'attachement ou la restauration (ou les deux). Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
    305711 BOGUE : L'utilisateur propriétaire de la base de données ne s'affiche pas dans Enterprise Manager
  5. Le message d'erreur suivant peut s'afficher si vous essayez de modifier le mot de passe de l'administrateur système (sa) par l'intermédiaire de Enterprise Manager si l'ouverture de session qui a été mappée sur dbo sur le serveur source n'existe pas sur le serveur de destination :
    Erreur 21776 : [SQL-DMO] Nom 'propriétaire de la base de données' introuvable dans la collection Utilisateurs. Si le nom est un nom complet, utilisez [] pour séparer les diverses parties du nom, puis réessayez.
    Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
    218172 PROBLÈME : Impossible de modifier le mot de passe de l'administrateur système dans Enterprise Manager
AVERTISSEMENT : Si vous restaurez ou si vous attachez à nouveau la base de données, les utilisateurs de la base de données peuvent devenir orphelins à nouveau et vous devrez répéter l'étape 3.

Étape 4 : Comment faire pour déplacer des travaux, des alertes et des opérateurs

L'étape 4 est facultative. Vous pouvez générer des scripts pour tous les travaux, les alertes et les opérateurs sur le serveur source, puis exécuter les scripts sur le serveur de destination.
  • Pour créer des scripts pour les travaux, les alertes et les opérateurs :

    1. Ouvrez SQL Server Enterprise Manager, puis développez le dossier Gestion.
    2. Développez Agent SQL Server, puis cliquez avec le bouton droit sur Alertes, Travaux ou Opérateurs.
    3. Cliquez sur Toutes les tâches, puis cliquez sur Générer un script SQL. Pour SQL Server 7.0, cliquez sur Utiliser un script pour tous les travaux ou Alertes ou Opérateurs.
    Vous pourrez choisir de générer des scripts pour Toutes les alertes, Tous les travaux ou Tous les opérateurs selon l'élément sur lequel vous cliquerez avec le bouton droit.
  • Vous pouvez déplacer des travaux, des alertes et des opérateurs à partir de SQL Server 7.0 vers SQL Server 2000 ou entre des serveurs qui exécutent SQL Server 7.0 et SQL Server 2000.
  • Si des opérateurs sont configurés pour la notification par SQLMail sur le serveur source, vous devez configurer SQLMail sur le serveur de destination pour obtenir la même fonctionnalité. Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
    263556 INF : Procédures pour configurer SQL Mail

Étape 5 : Comment faire pour déplacer des lots DTS

L'étape 5 est facultative. Si des lots DTS sont stockés sur le serveur source dans SQL Server ou le référentiel, vous pouvez les déplacer si vous le souhaitez. Pour déplacer des lots DTS entre des serveurs :
  1. Enregistrez le lot DTS sur le serveur source vers un fichier, puis ouvrez le fichier du lot DTS sur le serveur de destination.
  2. Enregistrez le lot sur le serveur de destination vers SQL Server ou vers le référentiel. REMARQUE : Vous devez déplacer chaque lot l'un après l'autre dans des fichiers séparés.
- ou -
  1. Ouvrez chaque lot DTS dans le Concepteur DTS.
  2. Dans le menu Lot, cliquez sur Enregistrer sous.
  3. Indiquez le serveur de destination SQL Server.
REMARQUE : le lot peut ne pas fonctionner correctement sur le nouveau serveur. Il se peut que vous deviez éditer le lot et modifier les références du lot aux connexions, aux fichiers, aux sources de données, aux profils et autres informations situées sur l'ancien serveur source, pour que celui-ci fasse référence au serveur de destination. Vous devez apporter ces modifications à la conception de chaque lot, l'un après l'autre.

PLUS D'INFORMATIONS

Vous pouvez également déplacer d'autres éléments tels que la réplication, l'envoi de journaux, les catalogues de texte intégral, les périphériques de sauvegarde nommés, les plans de maintenance et les serveurs liés. Vérifiez ces configurations sur le serveur source et prenez des mesures pour les configurer manuellement sur le serveur de destination, si vous le souhaitez.

Pour plus d'informations sur la procédure pour déplacer des composants de texte intégral, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
240867 INF : Comment faire pour déplacer, copier et sauvegarder des dossiers et fichiers de catalogues de texte intégral
Les schémas de base de données et l'historique de sauvegarde et restauration ne sont pas déplacés si vous suivez les étapes de cet article. Si vous devez déplacer ces informations, déplacez la base de données système msdb. Pour plus d'informations sur la procédure pour déplacer la base de données msdb, consultez les articles de la Base de connaissances Microsoft répertoriés dans la section "Étape 1 : Comment faire pour déplacer des bases de données d'utilisateurs" de cet article. Si vous déplacez la base de données msdb, vous n'avez pas à suivre "Étape 4 : Comment faire pour déplacer des travaux, des alertes et des opérateurs" ou "Étape 5 : Comment faire pour déplacer des lots DTS".


Références

Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
320125 COMMENT FAIRE : Déplacer un schéma de base de données

Propriétés

Numéro d'article: 314546 - Dernière mise à jour: vendredi 12 juillet 2013 - Version: 6.1
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2000 Standard
  • Microsoft SQL Server 2000 Édition 64 bits
  • Microsoft SQL Server 7.0 Standard
Mots-clés : 
kbsqlserverengine kbhowtomaster KB314546
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.

Envoyer des commentaires

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com