Comment faire pour déplacer des bases de données SQL Server vers un nouvel emplacement à l'aide des fonctions Détacher et Attacher dans SQL Server

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

Sommaire

Résumé

Cet article explique comment modifier l'emplacement des données et des fichiers journaux d'une base de données SQL Server 2005, SQL Server 2000 ou SQL Server 7.0.

Pour plus d'informations sur le déplacement de bases de données système dans SQL Server 2005, consultez la rubrique « Moving System Databases » dans la documentation en ligne SQL Server. Pour consulter cette rubrique, reportez-vous au site Web MSDN (Microsoft Developer Network) à l'adresse suivante :
http://msdn2.microsoft.com/fr-fr/library/ms345408.aspx

Plus d'informations

Les étapes à suivre pour modifier l'emplacement de certaines bases de données système SQL Server diffèrent des étapes à suivre pour modifier l'emplacement des bases de données utilisateur. Ces cas particuliers sont décrits séparément.

Remarque Les bases de données système SQL Server 7.0 sont incompatibles avec SQL Server 2000. N'attachez pas les bases de données SQL Server 7.0 master, model, msdb ou de distribution à SQL Server 2000. Si vous utilisez SQL Server 2005, vous pouvez attacher uniquement des bases de données SQL Server 2005 à une instance. Tous les exemples de cet article supposent que SQL Server est installé dans le répertoire D:\Mssql7, et que tous les fichiers de bases de données et les fichiers journaux se trouvent dans le répertoire D:\Mssql7\Data. Dans ces exemples, les fichiers de données et les fichiers journaux de toutes les bases de données sont déplacés vers le répertoire E:\Sqldata.

Conditions préalables

  • Effectuez une sauvegarde de toutes les bases de données, en particulier master, à partir de leurs emplacements actuels.
  • Vous devez disposer d'autorisations d'administrateur système.
  • Vous devez connaître le nom et l'emplacement actuels de tous les fichiers de données et journaux de la base de données.

    Remarque Vous pouvez déterminer le nom et l'emplacement actuels de tous les fichiers utilisés par une base de données à l'aide de la procédure stockée sp_helpfile :
    use <database_name>
    go
    sp_helpfile
    go
  • Vous devez avoir l'accès exclusif à la base de données que vous déplacez. Si vous rencontrez des problèmes durant le processus et ne parvenez pas à accéder à une base de données que vous avez déplacée ou à démarrer SQL Server, consultez le journal des erreurs SQL Server et la documentation en ligne de SQL Server pour plus d'informations sur les erreurs rencontrées.

Déplacement de bases de données utilisateur

L'exemple suivant déplace une base de données nommée mydb. Cette base de données contient un fichier de données (Mydb.mdf) et un fichier journal (Mydblog.ldf). Si la base de données que vous déplacez contient d'autres fichiers de données ou fichiers journaux, spécifiez-les dans une liste séparée par des virgules dans la procédure stockée sp_attach_db. La procédure sp_detach_db ne change pas, quel que soit le nombre de fichiers que contient la base de données, car elle ne les répertorie pas.
  1. Détachez la base de données de la manière suivante :
    use master
       go
       sp_detach_db 'mydb'
       go
  2. Copiez ensuite les fichiers de données et fichiers journaux de leur emplacement actuel (D:\Mssql7\Data) vers le nouvel emplacement (E:\Sqldata).
  3. Rattachez la base de données. Pointez vers les fichiers au nouvel emplacement en procédant comme suit :
    use master
      go
      sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
      go
    Vous pouvez vérifier le changement d'emplacement des fichiers à l'aide de la procédure stockée sp_helpfile :
    use mydb
       go
       sp_helpfile
       go
    Les valeurs de la colonne nom de fichier doivent refléter les nouveaux emplacements.
Remarque L'article 922804 de la Base de connaissances Microsoft décrit un problème concernant les bases de données SQL Server 2005 résidant sur un emplacement de stockage réseau. Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
922804 CORRECTIF : une fois que vous avez détaché une base de données Microsoft SQL Server 2005 qui réside sur un stockage connecté au réseau, vous ne pouvez plus rattacher cette base de données SQL Server.
Envisagez le problème suivant. De plus, prenez en compte les autorisations qui sont appliquées à une base de données lorsqu'elle est détachée dans SQL Server 2005. Pour plus d'informations, consultez la section « Detaching and Attaching a Database » de la rubrique « Securing Data and Log Files » dans la document SQL Server en ligne. Pour consulter cette rubrique, reportez-vous au site Web MSDN (Microsoft Developer Network) à l'adresse suivante :
http://msdn2.microsoft.com/fr-fr/library/ms189128.aspx

Exemple de déplacement de bases de données

Pour déplacer les exemples de bases de données Pubs et Northwind dans SQL Server 2000 ou dans SQL Server 7.0, ou pour déplacer les exemples de bases de données Adventure Works et Adventure WorksDW dans SQL Server 2005, procédez de la même manière que pour déplacer des bases de données utilisateur.

Déplacement de la base de données model

SQL Server 7.0

  1. Assurez-vous que l'Agent SQL Server n'est pas en cours d'exécution.
  2. Appliquez la même procédure pour déplacer des bases de données utilisateur.

SQL Server 2005 et SQL Server 2000

Dans SQL Server 2005 et dans SQL Server 2000, vous ne pouvez pas détacher les bases de données système à l'aide de la procédure stockée sp_detach_db. Lorsque vous tentez d'exécuter l'instruction sp_detach_db 'model', le message d'erreur suivant peut s'afficher :
Serveur : Msg 7940, Niveau 16, État 1, Ligne 1
Impossible de détacher les bases de données système master, model, msdb et tempdb.
Pour déplacer la base de données model SQL Server doit être démarré avec les options -c et -m ainsi qu'avec l'indicateur de suivi 3608 afin de ne récupérer aucune base de données à l'exception de master.

Remarque Après cela, vous ne serez plus en mesure d'accéder à une quelconque base de données utilisateur. N'effectuez aucune opération autre que celles décrites dans la procédure qui suit lorsque vous utilisez cet indicateur de suivi. Pour ajouter l'indicateur de suivi 3608 en tant que paramètre de démarrage de SQL Server, procédez comme suit :
  1. Dans SQL Server Enterprise Manager, cliquez avec le bouton droit sur le nom du serveur, puis cliquez sur Propriétés.
  2. Sous l'onglet Général, cliquez sur Paramètres de démarrage.
  3. Ajoutez le nouveau paramètre suivant :
    -c -m -T3608
Si vous utilisez SQL Server 2005, vous pouvez utiliser le Gestionnaire de configuration SQL Server pour modifier les paramètres de démarrage du service SQL Server. Pour plus d'informations sur la façon de modifier les paramètres de démarrage, reportez-vous au site Web MSDN (Microsoft Developer Network) à l'adresse suivante :
http://msdn2.microsoft.com/fr-fr/library/ms190737.aspx
Après avoir ajouté les options -c, -m et l'indicateur de suivi 3608, procédez comme suit :
  1. Arrêtez, puis redémarrez SQL Server.
  2. Détachez la base de données model à l'aide des commandes suivantes :
    use master
       go
       sp_detach_db 'model'
       go
  3. Déplacez les fichiers Model.mdf et Modellog.ldf du répertoire D:\Mssql8\Data vers le répertoire E:\Sqldata.
  4. Rattachez la base de données model à l'aide des commandes suivantes :
    use master
       go
       sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
       go
  5. Retirez -c -m -T3608 des paramètres de démarrage du service dans SQL Server Entreprise Manager ou dans le gestionnaire de configuration SQL Server.
  6. Arrêtez, puis redémarrez SQL Server. Vous pouvez vérifier le changement d'emplacement des fichiers à l'aide de la procédure stockée sp_helpfile. Par exemple, utilisez la commande suivante :
    use model
       go
       sp_helpfile
       go

Déplacement de la base de données MSDB

SQL Server 7.0

Remarque Si vous appliquez cette procédure en même temps que vous déplacez les bases de données msdb et model, vous devez rattacher d'abord la base de données model, puis la base de données msdb. Procédez comme suit :
  1. Assurez-vous que l'Agent SQL Server n'est pas en cours d'exécution.
  2. Appliquez la même procédure pour déplacer des bases de données utilisateur.
Remarque Si l'Agent SQL Server est en cours d'exécution, la procédure stockée sp_detach_db échoue et le message suivant s'affiche :
Serveur : Msg 3702, Niveau 16, État 1, Ligne 0
Impossible de supprimer la base de données 'msdb', parce qu'elle est présentement utilisée.
Exécution de DBCC terminée. Si DBCC vous a adressé des messages d'erreur, contactez l'administrateur du système.

SQL Server 2005 et SQL Server 2000

Pour déplacer la base de données, SQL Server doit être démarré avec les options -c et -m ainsi qu'avec l'indicateur de suivi 3608 afin de ne récupérer aucune base de données à l'exception de master. Pour ajouter les options -c, -m et l'indicateur de suivi 3608, suivez les indications de la section « Déplacement de la base de données model ». Après avoir ajouté les options -c, -m et l'indicateur de suivi 3608, procédez comme suit :
  1. Arrêtez, puis redémarrez SQL Server.
  2. Assurez-vous que le service de l'Agent SQL Server n'est pas en cours d'exécution.
  3. Détachez la base de données msdb de la manière suivante :
    use master
    go
    sp_detach_db 'msdb'
    go
  4. Déplacez les fichiers Msdbdata.mdf et Msdblog.ldf de leur emplacement actuel (D:\Mssql8\Data) vers le nouvel emplacement (E:\Mssql8\Data).
  5. Supprimez -c -m -T3608 de la zone des paramètres de démarrage dans Enterprise Manager.
  6. Arrêtez, puis redémarrez SQL Server.

    Remarque Si vous essayez de rattacher la base de données msdb en démarrant SQL Server avec les options -c , -m et avec l'indicateur de suivi -T3608, le message d'erreur suivant peut s'afficher :
    Serveur : Msg 615, Niveau 21, État 1, Ligne 1
    Table de base de données ID 3, nom 'modèle' introuvable.
  7. Rattachez la base de données msdb en procédant comme suit :
    use master
    go 
    sp_attach_db 'msdb','E:\Mssql8\Data\msdbdata.mdf','E:\Mssql8\Data\msdblog.ldf' 
    go
Remarque Si vous appliquez cette procédure en même temps que vous déplacez la base de données model, vous tentez de détacher simultanément les bases de données msdb et model, Dans ce cas, vous devez rattacher d'abord model, puis msdb. Si vous rattachez la base de données msdb en premier, vous recevrez le message d'erreur suivant quand vous tenterez de rattacher la base de données model :
Msg 0, Niveau 11, État 0, Ligne 0
Une erreur sérieuse s'est produite sur la commande actuelle. Le résultat, s'il y en a un, doit être ignoré.
Dans ce cas, vous devez détacher la base de données msdb, rattacher la base de données model, puis rattacher la base de données msdb.

Après avoir déplacé la base de données msdb, le message d'erreur suivant peut s'afficher :
Erreur 229 : Autorisation EXECUTE refusée sur l'objet 'Nom_objet', base de données 'master', propriétaire 'dbo'.
Ce problème se produit car la chaîne de propriétés a été rompue. Le propriétaire de la base de données msdb et de la base de données master n'est pas le même. Dans ce cas, l'appartenance de la base de données msdb avait été modifiée. Pour contourner ce problème, exécutez les instructions Transact-SQL suivantes. Pour cela, utilisez sur la ligne de commande l'utilitaire Osql.exe ( SQL Server 7.0 et SQL Server 2000) ou Sqlcmd.exe (SQL Server 2005) :
USE MSDB 
Go 
EXEC sp_changedbowner 'sa' 
Go
Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
272424 Vérification des chaînes de propriétés d'objets d'une base de données à une autre en fonction des connexions qui sont mappées aux propriétaires d'objets

Déplacement de la base de données master

  1. Modifiez le chemin d'accès aux fichiers journaux et de données master dans SQL Server Enterprise Manager.

    Remarque Vous pouvez également modifier l'emplacement du journal des erreurs.
  2. Cliquez avec le bouton droit sur le serveur SQL Server dans Enterprise Manager, puis cliquez sur Propriétés.
  3. Cliquez sur Paramètres de démarrage pour afficher les entrées suivantes :
    -dD:\MSSQL7\data\master.mdf
       -eD:\MSSQL7\log\ErrorLog
       -lD:\MSSQL7\data\mastlog.ldf
    -d est le chemin d'accès complet au fichier de données de la base de données master.

    -e est le chemin d'accès complet au fichier journal des erreurs.

    -l est le chemin d'accès complet au fichier journal de la base de données master.
  4. Modifiez ces valeurs comme suit :
    1. Supprimez les entrées actuelles correspondant aux fichiers Master.mdf et Mastlog.ldf.
    2. Ajoutez de nouvelles entrées qui indiquent le nouvel emplacement :
      -dE:\SQLDATA\master.mdf
            -lE:\SQLDATA\mastlog.ldf
  5. Arrêtez SQL Server.
  6. Copiez les fichiers Master.mdf et Mastlog.ldf au nouvel emplacement (E:\Sqldata).
  7. Redémarrez SQL Server.
Remarque Si vous utilisez SQL Server 2005, vous pouvez utiliser le Gestionnaire de configuration SQL Server pour modifier le chemin d'accès aux fichiers journaux et de données master.

Déplacement de la base de données tempdb

Vous pouvez déplacer les fichiers tempdb à l'aide de l'instruction ALTER DATABASE.
  1. Déterminez les noms de fichiers logiques pour la base de données tempdb en utilisant sp_helpfile comme suit :
    use tempdb
    go
    sp_helpfile
    go
    Le nom logique de chaque fichier est répertorié dans la colonne nom. Cet exemple utilise les noms de fichiers par défaut tempdev et templog.
  2. Utilisez l'instruction ALTER DATABASE, en spécifiant le nom de fichier logique comme suit :
    use master
    go
    Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
    go
    Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
    go
    Les messages suivants doivent s'afficher pour confirmer la modification :
    Message 1
    Fichier 'tempdev' modifié dans sysaltfiles. Redémarrez SQL Server et supprimez l'ancien fichier.
    Message 2
    Fichier 'templog' modifié dans sysaltfiles. Redémarrez SQL Server et supprimez l'ancien fichier.
  3. Si vous utilisez sp_helpfile dans tempdb, ces modifications ne seront confirmées qu'après le redémarrage de SQL Server.
  4. Arrêtez, puis redémarrez SQL Server.

Déplacement de la base de données master et de la base de données Resource

Pour plus d'informations sur la base de données master et sur la base de données Resource, consultez le site Web MSDN (Microsoft Developer Network) à l'adresse suivante :
http://msdn2.microsoft.com/fr-fr/library/ms345408.aspx

Références

Pour plus d'informations, cliquez sur les numéros ci-dessous pour afficher les articles correspondants dans la Base de connaissances Microsoft.
912397 Le service SQL Server ne peut pas démarrer lorsque vous modifiez un paramètre de démarrage d'une instance en cluster de SQL Server 2000 ou de SQL Server 2005 en lui affectant une valeur qui n'est pas valide.
274188 Rubrique « Troubleshooting orphaned users » (en anglais uniquement) incomplète dans la documentation en ligne
246133 Comment faire pour transférer des noms d'accès et des mots de passe entre instances de SQL Server
168001 Risque que les ouvertures de session et les autorisations utilisateur sur une base de données soient incorrectes une fois cette base de données restaurée

Pour plus d'informations, reportez-vous aux manuels suivants :
Microsoft Corporation
Microsoft SQL Server 7.0 System Administration Training Kit (en anglais uniquement)
Microsoft Press, 2001
Microsoft Corporation
Kit de formation MCSE Microsoft SQL Server 2000 : Administrateur système
Microsoft Press, 2001
Microsoft Corporation
Microsoft SQL Server 2000 Resource Kit (en anglais uniquement)
Microsoft Press, 2001

Propriétés

Numéro d'article: 224071 - Dernière mise à jour: vendredi 12 juillet 2013 - Version: 17.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 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2000 Standard
  • Microsoft SQL Server 7.0 Standard
Mots-clés : 
kbsqlserverengine kbinfo KB224071
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