Il est parfois nécessaire de déplacer des bases de données, soit après l’achat de disques supplémentaires, soit pour augmenter les performances en tirant profit de la parallélisation des écritures sur de multiples disques, comme par exemple pour la base de données TempDB.
Si le déplacement de bases de données utilisateur peut sembler simple, il en va tout autrement pour le déplacement des bases de données système que sont TempDB, Model, MSDB, et la moins « visible » d’entre-elles Resource qui est étroitement liée à la base de données Master.
Voyons comment déplacer ces bases de données …
Notez que dans tous les cas de déplacements de bases de données, qu’elles soient système ou pas, vous devrez relever les noms logiques des fichiers qui la constituent.
En effet SQL Server ne se base qu’au nom de ceux-ci pour démarrer une base de données, car le choix :
– de l’emplacement et du nom physique des fichiers,
– de l’extension des fichiers
Рdu nombre de fichiers constituant la base de donn̩es
sont laissés libre à l’utilisateur.
C’est ce que l’on peut en outre constater lorsqu’on souhaite restaurer une base de données à partir d’un fichier de sauvegarde :
on doit connaître le nom logique des fichiers, qui nous est donné par la commande RESTORE FILELISTONLY.
Dans le cas présent, on peut connaître le nom logique des fichiers avant de les déplacer à partir de la commande suivante :
1 2 3 4 5 6 | --------------------------------- -- Nicolas SOUQUET - 11/05/2009 - --------------------------------- SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('maBD') |
Pour le déplacement de bases de données système, nous devons avoir recours au redémarrage du service de l’instance SQL Server concernée, ce qui entraîne inévitablement la perte du cache de données.
=>=>=> En ce qui concerne la base de données TempDB, on peut profiter de ce que celle-ci est recrée à chaque redémarrage du service de l’instance SQL Server concernée.
Cela évite d’avoir à déplacer physiquement les fichiers. Il suffit donc d’exécuter :
1 2 3 4 5 6 7 8 9 10 11 | --------------------------------- -- Nicolas SOUQUET - 11/05/2009 - --------------------------------- USE Master GO ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'UNCDuNouvelEmplacementDuFichierDeDonnees.mdf') GO ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'UNCDuNouvelEmplacementDuFichierDuJournalDesTransaction.ldf') GO |
tempdev et templog étant les noms logiques par défaut de TempDB.
Il suffit ensuite de redémarrer le service SQL Server de l’instance concernée, et TempDB est alors déplacée.
=>=>=> Pour master, c’est un peu plus compliqué : vous devez aller dans SQL Server Configuration Manager (SQLServerManager.msc) pour configurer le futur emplacement des fichiers de cette BD, et arrêter deux fois l’instance de SQL Server.
Il est en plus à noter que vous devez également déplacer les fichiers de la base de données Resource, car ceux-ci doivent impérativement se trouver dans le même dossier que ceux de master.
=> Lorsque vous êtes dans SQL Server Configuration Manager :
– Double-cliquez sur la node « Services SQL Server 2005″ dans le panneau de gauche
– Cliquez droit sur l’instance de SQL Server pour laquelle vous souhaitez déplacer les fichier, puis choisissez « Propriétés »
– Dans l’onglet avancé, repérez le paramètre « Paramètres de démarrage »
=> indiquez derrière :
– « -d » le nouvel emplacement du fichier de données (par défaut il porte l’extension .mdf) de la BD master,
– « -e » le nouvel emplacement des fichiers de journaux d’erreur
– « -l » le nouvel emplacement du fichier de journal des transactions de master (par défaut il porte l’extention .ldf)
– arrêtez le service SQL Server de l’instance concernée
Рd̩placez les fichiers master.mdf et mastlog.ldf vers leur nouveau dossier
– ouvrez une console DOS
=> si vous déplacez :
Рune instance par d̩faut, tapez NET START MSSQLSERVER /f /T3608
– une autre instance, tapez NET START MSSQL$monInstance /f /T3608
Cette commande permet de démarrer une instance de SQL Server en mode de récupération de la BD master
=> Passons maintenant au déplacement de la BD système Resource. Dans Management Studio, exécutez :
1 2 3 4 5 6 7 8 9 | --------------------------------- -- Nicolas SOUQUET - 11/05/2009 - --------------------------------- ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=DATA, FILENAME= 'nouvelleUNCDuDossierDeMaster\mssqlsystemresource.mdf'); GO ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'nouvelleUNCDuDossierDeMaster\mssqlsystemresource.ldf'); GO |
РD̩placez les fichiers mssqlsystemresource.mdf et mssqlsystemresource.ldf vers le nouveau dossier de master
РDans Management Studio, ex̩cutez : ALTER DATABASE mssqlsystemresource SET READ_ONLY;
– Fermez Management Studio puis retournez dans SQL Server Configuration Manager, et redémarrez le service de l’instance SQL Server concernée
=>=>=> Pour les autres bases de données système, le plus efficace est de procéder comme pour déplacer une base de données utilisateur :
Рeffectuer une sauvegarde compl̬te (BACKUP DATABASE sans options) de la base de donn̩es puis de la restaurer (RESTORE DATABASE) avec les options MODIFY FILE et REPLACE
– procéder à un détachement puis rattachement de la base de données (directement depuis Management Studio ou en utilisant sp_detach_db puis CREATE DATABASE avec l’option FOR ATTACH ou FOR ATTACH_REBUILD_LOG, en déplaçant les fichiers de base de données entre le détachement et l’attachement)
Seule MSDB fait exception : on doit, préalablement au détachement de celle-ci, arrêter le service Agent SQL Server de l’instance concernée pour pouvoir la déplacer.
Retenons que lors :
– de l’arrêt du service SQL Server
– de la restauration d’une base de données
– du détachement d’une base de données
Le cache des inévitablement perdu.
ElSuket