SQL Server : Déplacer les bases de données
Dans l’article « Installation de SQL server en mode IaaS », nous avons vu qu’une des bonnes pratiques d’installation de SQL consiste à utiliser plusieurs disques pour les fichiers de données et les fichiers de transactions. Cependant, dans le cas où l’installation a déjà été faite par défaut, et que tous les fichiers se trouvent au même endroit, comment est-il possible de déplacer les fichiers ?
Il existe plusieurs manières de faire, en utilisant des techniques manuelles ou automatiques via des requêtes T-SQL :
- Sauvegarde puis restauration des bases sur un emplacement différent
- Copie des fichiers dans le nouvel emplacement puis « detach » et « attach » des bases
- Copie des fichiers dans le nouvel emplacement puis utilisation de la commande T-SQL « ALTER DATABASE »
Dans cet article, nous allons utiliser la dernière méthode, et de manière automatisée via une requête T-SQL. Voici les étapes que nous allons suivre :
-
- Arrêt du service SQL Server
- Copie des fichiers vers les nouveaux emplacements
- Modification du service SQL Server pour redéfinir l’emplacement des fichiers de la base Master
- Redémarrage du service SQL Server
- Exécution de la requête T-SQL dans SQL Server Management Studio (SSMS)
- Redémarrage du service SQL Server
1/ Arrêt du service SQL Server (MSSQLSERVER)
Dans « SQL Server Configuration manager », sur la gauche, cliquez sur « SQL Server Services ».
Faîtes un clic droit sur le service SQL que vous souhaitez arrêter et cliquez sur « Stop ».
2/ Copie des fichiers vers les nouveaux emplacements
Copiez les fichiers vers les nouveaux emplacements. Dans mon exemple, je copie les fichiers de données (.mdf et ndf) vers E:\SQLData et les fichiers des transactions vers F:\SQLLogs.
Dans cet exemple, les fichiers d’origines se trouvent à l’emplacement par défaut :
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA
3/ Modification du Service SQL pour redéfinir les emplacements des fichiers de la base Master
Retournez dans « SQL Server Configuration Manager » et faîtes un clic droit sur le service MSSQLSERVER.
Cliquez sur « Properties ».
Dans La fenêtre des propriétés du service MSSQLSERVER, cliquez sur l’onglet « Startup Parameters ».
Dans la rubrique « Existing parameters », selectionnez le chemin vers le fichier de données de la base Master « master.mdf ».
Mettez le chemin à jour et cliquez sur le bouton « Update ».
Répétez l’opération pour le fichier « mastlog.ldf ». La fenêtre ci-dessous illustre le résultat dans mon environnement.
4/ Redémarrage du service SQL Server (MSSQLSERVER)
Redémarrer le service MSSQLSERVER en cliquant droit dessus et en sélectionnant « Start ».
5/ Exécution de la requête T-SQL dans SQL Server Management Studio (SSMS)
Dans SSMS, exécutez la requête suivante afin de vérifier les nouveaux chemins de la base Master.
SELECT physical_name FROM sys.master_files.
Le résultat affiche les chemins des différents fichiers utilisés par toutes les bases de données.
Maintenant, nous allons exécuter la requête suivante afin de modifier les chemins de toutes les bases de données de notre instance.
/ ** ATTENTION, avant d’exécuter la requête, faîtes des tests au préalable et sauvegardez vos bases de données. **\
Dans SSMS, connectez-vous à votre instance de base de données. Puis cliquez sur bouton « New Query ».
Dans la fenêtre de requête, copiez la requête ci-dessous :
-- Déclaration des variables
DECLARE @DB_Name nvarchar(255)
DECLARE @DataPath nvarchar(255) = 'E:\SQLData\'
DECLARE @LogPath nvarchar(255) = 'F:\SQLLogs\'
DECLARE @LogicalFileName nvarchar(MAX)
DECLARE @FileType nvarchar(255)
DECLARE @FileExtension nvarchar(255)
DECLARE @FranmerQuery nvarchar(MAX)
DECLARE @PhysicalFileName nvarchar(MAX)
--Création d'une table temporaire pour stocker les informations des fichiers de données et logs.
--Le préfixe # devant le nom de la table (MyDB dans cet exemple) permet de créer la table dans la base TempDB.
CREATE Table #MyDB (MyDB_DBName nvarchar(255), MyDB_DBLogicalFileName nvarchar(255), MyDB_DBFileType nvarchar(255),MyDB_DBFileExtension nvarchar(255),MyDB_DBPhysicalFileName nvarchar(255))
-- Insertion des données dans la table temporaire à partir des tables systèmes sys.master_files et sys.databases
INSERT INTO #MyDB (MyDB_DBName , MyDB_DBLogicalFileName , MyDB_DBFileType ,MyDB_DBFileExtension, MyDB_DBPhysicalFileName )
Select b.name as DBName, a.name as BdLogicalFileName, a.type_desc as DBFileType, RIGHT(physical_name,CHARINDEX('\',physical_name)) as Extension, reverse(left(reverse(physical_name), charindex('\', reverse(physical_name)) -1)) as PhysicalFileName from
master.sys.master_files a inner join sys.databases b
ON a.database_id = b.database_id
-- Déclaration d'un curseur afin de parcourir les lignes de la table temporaire
DECLARE MyCursor CURSOR FOR select MyDB_DBName , MyDB_DBLogicalFileName , MyDB_DBFileType ,MyDB_DBFileExtension,MyDB_DBPhysicalFileName FROM #MyDB;
OPEN MyCursor;
-- Création de la requête ALTER DATABASE en récupérant, ligne par ligne, les informations de la table temporaire afin de remplir les différentes variables
FETCH NEXT FROM MyCursor INTO @DB_Name, @LogicalFileName , @FileType, @FileExtension, @PhysicalFileName;
-- Balayage ligne par ligne de la table temporaire jusqu'à la fin
WHILE @@FETCH_STATUS = 0
BEGIN
-- Test du type de fichier pour orienter les fichiers de données (ROWS) sur un disque et les fichiers des transactions sur un autre disque
IF @Filetype = 'ROWS'
SET @FranmerQuery = 'ALTER DATABASE [' + @DB_Name + '] MODIFY FILE (NAME = ''' + @LogicalFileName +''', FILENAME = ''' + @DataPath + @PhysicalFileName + ''')'
ELSE
SET @FranmerQuery = 'ALTER DATABASE [' + @DB_Name + '] MODIFY FILE (NAME = ''' + @LogicalFileName +''', FILENAME = ''' + @LogPath + @PhysicalFileName +''')'
-- L'utilisation du PRINT sert à vérifier la bonne syntaxe de la commande
-- Si la syntaxe est correcte, alors on remplacera PRINT par EXECUTE
PRINT (@FranmerQuery)
FETCH NEXT FROM MyCursor INTO @DB_Name, @LogicalFileName , @FileType, @FileExtension, @PhysicalFileName;
END
CLOSE MyCursor;
DEALLOCATE MyCursor;
DROP TABLE #MyDB
Une fois la requête copiée, modifiez les variables « @DataPath » et « @LogPath » en fonction de votre environnement.
Cliquez sur le bouton « Execute » pour tester la requête.
La requête va retourner le résultat suivant via l’instruction PRINT.
Cela va nous permettre de vérifier la bonne syntaxe des chemins de destination. Une fois la requête validée, remplacez l’instruction PRINT par EXECUTE
Après l’exécution de la requête, le message suivant est affiché :
6/ Redémarrage du service SQL Server (MSSQLSERVER)
Dans SSMS, au niveau de l’instance de votre base de données, faîtes un clic droit et cliquez sur « Restart ».
Dans la fenêtre de confirmation, Cliquez sur « Yes ».
Une fois le service redémarré, vérifiez le bon changement des emplacements des fichiers avec la requête suivante.
SELECT Physical_name from sys.master_files
Vous pouvez aussi vérifier si les changements ont bien été pris en compte, en faisant un clic droit sur une base de données, puis en cliquant sur « Properties »
Sur la gauche de la fenêtre « Database Properties », cliquez sur Files. Vérifiez alors que les nouveaux emplacements ont bien été validés.
Voilà, vos fichiers de base de données viennent d’être déplacés.
Au plaisir de vous voir lors d’un IT Camp!
Pour tester Windows Server 2012, Windows 8, SQL Server 2012 vous pouvez télécharger gratuitement la version d’évaluation disponible sous la forme :
- Windows Server 2012 :
- d'une image ISO : https://aka.ms/jeveuxwindows2012
- d'un fichier VHD avec un système préinstallé : https://aka.ms/jeveuxwindows2012
- SQL Server 2012
- Inscription SQL Server 2014 CTP1 :