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

  • Article
  • 12 minutes de lecture

S’applique à :SQL Server

Cet article explique 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 suivantes s’appliquent au déplacement de 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 l’opération de sauvegarde et de restauration.

Les procédures de cet article nécessitent 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.

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

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. Cela comprend les bases de données système model, msdb et tempdb.

Important

Cette procédure s'applique à toutes les bases de données système à l'exception des bases de données master et Resource. Découvrez comment déplacer la base de données master plus tard dans cet article. La base de données Resource ne peut pas être déplacée.

  1. Enregistrez l’emplacement existant des fichiers de base de données que vous souhaitez déplacer en examinant l’affichage catalogue sys.master_files .

  2. Vérifiez que le compte de service du moteur de base de données SQL Server dispose des autorisations complètes sur le nouvel emplacement des fichiers. Pour plus d’informations, consultez Configurer les comptes de service Windows et les autorisations. Si le compte de service Moteur de base de données ne peut pas contrôler les fichiers dans leur nouvel emplacement, l’instance SQL Server ne démarre pas.

  3. Pour chaque fichier de base de données à déplacer, exécutez la commande suivante.

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

    Tant que le service n'a pas redémarré, la base de données continue à utiliser les fichiers de données et les fichiers journaux situés à l'emplacement existant.

  4. Arrêtez l’instance de SQL Server pour effectuer la maintenance. Pour plus d'informations, consultez Démarrer, arrêter, suspendre, reprendre, redémarrer les services SQL Server.

  5. Copiez le ou les fichiers de base de données vers le nouvel emplacement. Notez qu’il ne s’agit pas d’une étape nécessaire pour la base de données système tempdb. Ces fichiers seront créés automatiquement dans le nouvel emplacement.

  6. Redémarrez l'instance de SQL Server ou le serveur. Pour plus d'informations, consultez Démarrer, arrêter, suspendre, reprendre, redémarrer les services SQL Server.

  7. Vérifiez le changement de fichier en exécutant la requête suivante. Les bases de données système doivent signaler les nouveaux emplacements physiques des fichiers.

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

  8. Étant donné qu’à l’étape 5, vous avez copié les fichiers de base de données au lieu de les déplacer, vous pouvez maintenant supprimer en toute sécurité les fichiers de base de données inutilisés à partir de leur emplacement précédent.

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

Si la base de données msdb est déplacée et que Database Mail est configurée, effectuez les étapes supplémentaires suivantes.

  1. Vérifiez que Service Broker est activé pour la msdb base de données en exécutant la requête suivante.

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

    Si Service Broker n’est pas activé pour msdb, il doit être réactivé pour que Database Mail fonctionne. Pour plus d'informations, consultez OPTIONS ALTER DATABASE ... SET ENABLE_BROKER.

    ALTER DATABASE msdb 
SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

    Vérifiez que la valeur de is_broker_enabled est désormais définie sur 1.

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

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. Les exemples suivants utilisent l’invite de ligne de commande Windows et l'utilitaire sqlcmd.

Important

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

  1. Vérifiez que le compte de service du moteur de base de données SQL Server dispose des autorisations complètes sur le nouvel emplacement des fichiers. Pour plus d’informations, consultez Configurer les comptes de service Windows et les autorisations. Si le compte de service Moteur de base de données ne peut pas contrôler les fichiers dans leur nouvel emplacement, l’instance SQL Server ne démarre pas.

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

  3. Démarrez l'instance de SQL Server en mode de récupération de la base de données master uniquement en tapant une des commandes suivantes à l'invite de commandes. L’utilisation du paramètre de démarrage 3608 empêche SQL Server de démarrer et de récupérer automatiquement une base de données à l’exception de la base de données master. Pour plus d’informations, consultez Paramètres de démarrage et TF3608.

    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 les services SQL Server.

  4. Rapidement après le démarrage du service avec l’indicateur de trace 3608 et /f, lancez une connexion sqlcmd au serveur, pour revendiquer la connexion unique qui est disponible. Par exemple, lors de l’exécution de sqlcmd localement sur le même serveur que l’instance par défaut (MSSQLSERVER) et pour établir une connexion avec l’authentification d’intégration Active Directory, exécutez la commande suivante :

    sqlcmd

    Pour vous connecter à une instance nommée sur le serveur local, avec l’authentification de l’intégration Active Directory :

    sqlcmd -S localhost\instancename

    Pour plus d’informations sur la syntaxe sqlcmd, consultez l’utilitaire sqlcmd.

    Pour chaque fichier à déplacer, utilisez les commandes sqlcmd ou SQL Server Management Studio pour exécuter l’instruction suivante. Pour plus d’informations sur l’utilisation de l’utilitaire sqlcmd , consultez Utiliser l’utilitaire sqlcmd. Une fois la session sqlcmd ouverte, exécutez l’instruction suivante une fois pour chaque fichier à déplacer :

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

  5. Quittez l’utilitaire sqlcmd ou SQL Server Management Studio.

  6. Arrêtez l'instance de SQL Server. Par exemple, exécutez NET STOP MSSQLSERVER dans l’invite de ligne de commande.

  7. Copiez le ou les fichiers vers le nouvel emplacement.

  8. Redémarrez l'instance de SQL Server. Par exemple, exécutez NET START MSSQLSERVER dans l’invite de ligne de commande.

  9. 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>');

  10. Étant donné qu’à l’étape 7, vous avez copié les fichiers de base de données au lieu de les déplacer, vous pouvez maintenant supprimer en toute sécurité les fichiers de base de données inutilisés à partir de leur emplacement précédent.

Déplacement de la base de données master

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

  1. Vérifiez que le compte de service du moteur de base de données SQL Server dispose des autorisations complètes sur le nouvel emplacement des fichiers. Pour plus d’informations, consultez Configurer les comptes de service Windows et les autorisations. Si le compte de service Moteur de base de données ne peut pas contrôler les fichiers dans leur nouvel emplacement, l’instance SQL Server ne démarre pas.

  2. Dans le menu Démarrer, lancez le Gestionnaire de configuration SQL Server. Pour plus d’informations sur l’emplacement attendu, consultez Gestionnaire de configuration SQL Server.

  3. Dans le nœud services SQL Server, cliquez avec le bouton droit sur l’instance de SQL Server (par exemple, SQL Server (MSSQLSERVER)) et choisissez Propriétés.

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

  5. Dans la zone Paramètres existants, sélectionnez le paramètre -d. Dans la zone Spécifier un paramètre de démarrage, remplacez le paramètre par le nouveau chemin d’accès du master fichier de données. Sélectionnez Mettre à jour pour enregistrer les modifications.

  6. Dans la zone Paramètres existants, sélectionnez le paramètre -l. Dans la zone Spécifier un paramètre de démarrage, remplacez le paramètre par le nouveau chemin d’accès du master fichier journal. Sélectionnez Mettre à jour pour enregistrer les modifications.

    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 master.

    -dC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\master.mdf

    -lC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

    Si le nouvel emplacement planifié pour les fichiers 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

  7. Sélectionnez OK pour enregistrer les changements de façon définitive et fermez la boîte de dialogue Propriétés SQL Server (instance_name) .

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

  9. Copiez les master.mdf fichiers et mastlog.ldf dans le nouvel emplacement.

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

  11. 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');

  12. À ce stade, SQL Server doit fonctionner normalement. Toutefois Microsoft recommande d’ajuster également l’entrée de registre sur HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_ID\Setup, où instance_ID est similaire à MSSQL13.MSSQLSERVER. Dans cette ruche, remplacez la valeur par SQLDataRoot le nouveau chemin d’accès du nouvel emplacement des fichiers de base de master données. L’échec de la mise à jour du registre peut entraîner l’échec de la mise à jour corrective et de la mise à niveau.

  13. Étant donné qu’à l’étape 9, vous avez copié les fichiers de base de données au lieu de les déplacer, vous pouvez maintenant supprimer en toute sécurité les fichiers de base de données inutilisés à partir de leur emplacement précédent.

Déplacement de la base de données resource

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

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

Si vous avez déplacé toutes les bases de données système vers un nouveau volume ou si vous avez migré vers un autre serveur avec une autre lettre de lecteur, et que le chemin d’accès du fichier SQLAGENT.OUT journal des erreurs sql Agent n’existe plus, effectuez les mises à jour suivantes.

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

  2. Cliquez avec le bouton droit sur Journaux d'erreurs, puis sélectionnez 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\MSSQL\<version>.<instance_name>\MSSQL\Log\\.

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

  1. À partir de SQL Server Management Studio, dans Explorateur d'objets, connectez-vous à l’instance SQL Server souhaitée. Cliquez avec le bouton droit sur l’instance et sélectionnez 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.

Exemples

R. 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.

Conseil

Profitez de cette occasion pour passer en revue vos fichiers tempdb pour une taille et un positionnement optimaux. Pour plus d’informations, consultez Optimisation des performances de tempdb dans SQL Server.

Comme tempdb est recréé chaque fois que l’instance de SQL Server est démarrée, vous n’avez pas besoin de 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 4. 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. Vérifiez que le compte de service du moteur de base de données SQL Server dispose des autorisations complètes sur le nouvel emplacement des fichiers. Pour plus d’informations, consultez Configurer les comptes de service Windows et les autorisations. Si le compte de service Moteur de base de données ne peut pas contrôler les fichiers dans leur nouvel emplacement, l’instance SQL Server ne démarre pas.

  3. 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

    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.

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

  5. 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');

  6. Supprimer les fichiers tempdb inutilisés de leur emplacement d’origine.

Voir aussi

Étapes suivantes