Déplacer des bases de données système

  • Article

Cette rubrique décrit comment déplacer des bases de données système dans SQL Server. Le déplacement des bases de données système peut être utile dans les cas suivants :

  • Récupération après défaillance. Par exemple, la base de données est en mode suspect ou a été fermée en raison d'une défaillance matérielle.

  • Déplacement prévu.

  • Déplacement en vue d'une maintenance de disque planifiée.

Les procédures ci-dessous s'appliquent au déplacement des fichiers de base de données au sein de la même instance de SQL Server. Pour déplacer une base de données vers une autre instance de SQL Server ou vers un autre serveur, utilisez les opérations de sauvegarde et restauration ou de détachement et attachement.

Les procédures de cette rubrique requièrent le nom logique des fichiers de base de données. Pour obtenir ce nom, interrogez la colonne name dans l'affichage catalogue sys.master_files.

Important

Si vous déplacez une base de données système et que vous recréez ultérieurement la base de données master, vous devez redéplacer la base de données système car l'opération de recréation installe toutes les bases de données système à leur emplacement par défaut.

Dans cette rubrique

  • Procédure de réadressage planifié et de maintenance de disque planifiée

  • Procédure de récupération après défaillance

  • Déplacement de la base de données master

  • Déplacement de la base de données Resources

  • Suivi : après le déplacement de toutes les bases de données système

  • Exemples

Procédure de réadressage planifié et de maintenance de disque planifiée

Pour déplacer des données ou un fichier journal d'une base de données système dans le cadre d'un réadressage planifié ou d'une opération de maintenance planifiée, suivez la procédure ci-dessous. Cette procédure s'applique à toutes les bases de données système à l'exception des bases de données master et Resource.

  1. Pour chaque fichier à déplacer, exécutez la commande suivante.

    ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

  2. Arrêtez l'instance de SQL Server ou arrêtez le système pour effectuer la maintenance. Pour plus d'informations, consultez Démarrer, arrêter, suspendre, reprendre, redémarrer le moteur de base de données, SQL Server Agent ou le service SQL Server Browser.

  3. Déplacez le ou les fichiers vers le nouvel emplacement.

  4. Redémarrez l'instance de SQL Server ou le serveur. Pour plus d'informations, consultez Démarrer, arrêter, suspendre, reprendre, redémarrer le moteur de base de données, SQL Server Agent ou le service SQL Server Browser.

  5. Vérifiez le changement de fichier en exécutant la requête suivante.

    SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');

Si la base de données msdb est déplacée et si l'instance de SQL Server est configurée pour la Messagerie de base de données, effectuez ces opérations supplémentaires.

  1. Assurez-vous que Service Broker est activé pour la base de données msdb en exécutant la requête ci-dessous.

    SELECT is_broker_enabled 
FROM sys.databases
WHERE name = N'msdb';

    Pour plus d'informations sur l'activation de Service Broker, consultez ALTER DATABASE (Transact-SQL).

  2. Vérifiez le bon fonctionnement de la messagerie de base de données en envoyant un message électronique de test.

Icône de flèche utilisée avec le lien Retour en haut[Haut de la page]

Procédure de récupération après défaillance

Si un fichier doit être déplacé dans un nouvel emplacement en raison d'une défaillance matérielle, suivez la procédure décrite ci-dessous. Cette procédure s'applique à toutes les bases de données système à l'exception des bases de données master et Resource.

Important

Si la base de données ne démarre pas, à savoir en mode suspect ou dans un état non récupéré, seuls les membres du rôle fixe sysadmin peuvent déplacer le fichier.

  1. Arrêtez l'instance de SQL Server si elle est démarrée.

  2. Démarrez l'instance de SQL Server en mode de récupération de la base de données master uniquement en entrant l'une des commandes ci-dessous à l'invite de commandes. Les paramètres spécifiés dans ces commandes respectent la casse. Les commandes échouent lorsque les paramètres ne sont pas spécifiés comme indiqué.

    • Dans le cas d'une instance par défaut (MSSQLSERVER), exécutez la commande ci-dessous :

      NET START MSSQLSERVER /f /T3608

    • Dans le cas d'une instance nommée, exécutez la commande ci-dessous :

      NET START MSSQL$instancename /f /T3608

    Pour plus d'informations, consultez Démarrer, arrêter, suspendre, reprendre, redémarrer le moteur de base de données, SQL Server Agent ou le service SQL Server Browser.

  3. Pour chaque fichier à déplacer, utilisez les commandes sqlcmd ou SQL Server Management Studio pour exécuter l'instruction suivante.

    ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

    Pour plus d'informations sur l'utilisation de l'utilitaire sqlcmd, consultez Utiliser l'utilitaire sqlcmd.

  4. Fermez l'utilitaire sqlcmd ou SQL Server Management Studio.

  5. Arrêtez l'instance de SQL Server. Par exemple, exécutez NET STOP MSSQLSERVER.

  6. Déplacez le ou les fichiers vers le nouvel emplacement.

  7. Redémarrez l'instance de SQL Server. Par exemple, exécutez NET START MSSQLSERVER.

  8. Vérifiez le changement de fichier en exécutant la requête suivante.

    SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');

Icône de flèche utilisée avec le lien Retour en haut[Haut de la page]

Déplacement de la base de données master

Pour déplacer la base de données master, procédez comme suit.

  1. Dans le menu Démarrer, pointez successivement sur Tous les programmes, sur Microsoft SQL Server et sur Outils de configuration, puis cliquez sur Gestionnaire de configuration SQL Server.

  2. Dans le nœud Services SQL Server, cliquez avec le bouton droit sur l'instance de SQL Server (par exemple, SQL Server (MSSQLSERVER)) puis cliquez sur Propriétés.

  3. Dans la boîte de dialogue Propriétés de SQL Server (instance_name), cliquez sur l'onglet Paramètres de démarrage.

  4. Dans la zone Paramètres existants, sélectionnez le paramètre –d pour déplacer le fichier des données de master. Cliquez sur Mettre à jour pour enregistrer les modifications.

    Dans la zone Spécifiez un paramètre de démarrage, modifiez le paramètre par le nouveau chemin d'accès de la base de données master.

  5. Dans la zone Paramètres existants, sélectionnez le paramètre –l pour déplacer le fichier journal de master. Cliquez sur Mettre à jour pour enregistrer les modifications.

    Dans la zone Spécifiez un paramètre de démarrage, modifiez le paramètre par le nouveau chemin d'accès de la base de données master.

    La valeur du paramètre pour le fichier de données doit suivre le paramètre -d et la valeur pour le fichier journal doit suivre le paramètre -l. L'exemple suivant montre les valeurs des paramètres pour l'emplacement par défaut des fichiers de données de la base de données master.

    -dC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf

    -lC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

    Si le nouvel emplacement planifié pour les fichiers de données de la base de données master correspond à E:\SQLData, les valeurs des paramètres sont modifiées comme suit :

    -dE:\SQLData\master.mdf

    -lE:\SQLData\mastlog.ldf

  6. Arrêtez l'instance de SQL Server en cliquant avec le bouton droit sur le nom d'instance et en choisissant Arrêter.

  7. Déplacez les fichiers master.mdf et mastlog.ldf vers le nouvel emplacement.

  8. Redémarrez l'instance de SQL Server.

  9. Vérifiez que la modification des fichiers a bien eu lieu pour la base de données master en exécutant la requête ci-dessous.

    SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('master');
GO

Déplacement de la base de données Resources

L'emplacement de la base de données Resource est <drive>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\. La base de données ne peut pas être déplacée.

Icône de flèche utilisée avec le lien Retour en haut[Haut de la page]

Suivi : après le déplacement de toutes les bases de données système

Si vous avez déplacé toutes les bases de données système vers un même lecteur ou volume ou vers un autre serveur utilisant une lettre de lecteur différente, effectuez les mises à jour suivantes.

  • Modifiez le chemin d'accès du journal de l'Agent SQL Server. Si vous ne mettez pas à jour ce chemin d'accès, l'Agent SQL Server ne démarre pas.

  • Modifiez l'emplacement par défaut de la base de données. La création d'une base de données peut échouer si la lettre de lecteur et le chemin d'accès spécifiés comme emplacement par défaut n'existent pas.

Modifier le chemin d'accès du journal de l'Agent SQL Server

  1. Dans SQL Server Management Studio, dans l'Explorateur d'objets, développez Agent SQL Server.

  2. Cliquez avec le bouton droit sur Journaux d'erreurs, puis cliquez sur Configurer.

  3. Dans la boîte de dialogue Configurer les journaux d'erreurs de l'Agent SQL Server, spécifiez le nouvel emplacement du fichier SQLAGENT.OUT. L'emplacement par défaut est C:\Program Files\Microsoft SQL Server\MSSQL11.<nom_instance>\MSSQL\Log\.

Modifier l'emplacement par défaut de la base de données

  1. Dans SQL Server Management Studio, dans l'Explorateur d'objets, cliquez avec le bouton droit sur le serveur SQL Server, puis cliquez sur Propriétés.

  2. Dans la boîte de dialogue Propriétés du serveur, sélectionnez Paramètres de base de données.

  3. Sous Emplacements de la base de données par défaut, accédez au nouvel emplacement des fichiers de données et des fichiers journaux.

  4. Arrêtez et démarrez le service SQL Server pour terminer la modification.

Icône de flèche utilisée avec le lien Retour en haut[Haut de la page]

Exemples

A.Déplacement de la base de données tempdb

Dans l'exemple suivant, les fichiers de données et les fichiers journaux de la base de données tempdb sont déplacés vers un nouvel emplacement dans le cadre d'une opération planifiée.

[!REMARQUE]

Dans la mesure où la base de données tempdb est recréée à chaque démarrage de l'instance de SQL Server, vous n'avez pas à déplacer physiquement les fichiers de données et les fichiers journaux. Les fichiers sont créés au nouvel emplacement lorsque le service est redémarré à l'étape 3. Tant que le service n'a pas redémarré, tempdb continue à utiliser les fichiers de données et les fichiers journaux situés à l'emplacement existant.

  1. Déterminez les noms de fichiers logiques de la base de données tempdb et leur emplacement actuel sur le disque.

    SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

  2. Modifiez l'emplacement de chaque fichier à l'aide de ALTER DATABASE.

    USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
GO

  3. Arrêtez et redémarrez l'instance de SQL Server.

  4. Vérifiez que la modification des fichiers a bien eu lieu.

    SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

  5. Supprimez les fichiers tempdb.mdf et templog.ldf de l'emplacement d'origine.

Icône de flèche utilisée avec le lien Retour en haut[Haut de la page]

Voir aussi

Référence

ALTER DATABASE (Transact-SQL)

Concepts

Base de données Resource

Base de données tempdb

Base de données master

Base de données msdb

Base de données model

Déplacer des bases de données utilisateur

Démarrer, arrêter, suspendre, reprendre, redémarrer le moteur de base de données, SQL Server Agent ou le service SQL Server Browser

Reconstruire des bases de données système

Autres ressources

Déplacer des fichiers de bases de données