Voici une procédure générique de déplacement de fichiers pour une base de données Microsoft SQL Server.
FONCTIONNEMENT :
Après avoir vérifié les paramètres, la procédure exécute séquentiellement :
- un détachement de la base (par la procédure sp_detach_db) après être devenu l'utilisateur unique ( avec le commande ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE)
- un transfert de fichier (par la procédure xp_cmdshell)
- un rattachement des fichiers (par la commande CREATE DATABASE ... FOR ATTACH)
PARAMÈTRES :
@DB_NAME : nom de la base (doit exister, contrôle préventif effectué)
@DESTINATION : répertoire de destination (doit exister, contrôle préventif effectué)
@LOGICAL_FILE : fichier(s) à déplacer - Ce dernier paramètre peut porter sur un nom logique de fichier dans la base (voir name dans sys.database_files) ou alors, pour un déplacement générique :
[ALL] (crochets compris) pour tous les fichiers;
[DATA] (crochets compris) pour les fichiers de données;
[TRAN] (crochets compris) pour les fichiers du journal de transactions.
PROBLÈMES POSSIBLES :
- l'utilisateur qui lance cette procédure n'a pas les privilèges adéquats (CONTROL SERVER)
- le compte de service de l'instance SQL Server n'a pas les droits en lecture/ecriture sur le répertoire de destination
- le répertoire de destination n'a pas une capacité suffisante pour y stocker les fichiers à déplacer
- le répertoire est un répertoire mappé ou un disque amovible
- la base compte du stockage FILESTREAM ou des FILETABLE
REMÈDE :
En cas d'erreur, le résultat (paneau messages de SSMS) fournit la commande qui est partie en exception, mais aussi toute la partie déjà exécutée du script de migration.
NOTA : la procédure doit être créée de préférence dans une base système, par exemple msdb ou master.
CREATE PROCEDURE dbo.P_MOVE_DATABASE_FILES @DB_NAME sysname, -- nom de la base @DESTINATION NVARCHAR(256), -- répertoire de destination @LOGICAL_FILE NVARCHAR(128) -- fichier à déplacer. AS /****************************************************************************** * Procédure de déplacement des fichiers d'une base de données * ******************************************************************************* * Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2013-08-16 * ******************************************************************************* * Cette procédure permet de déplacer les fichier d'une base de données * * FONCTIONNEMENT : * * après avoir vérifié les paramètres, la procédure exécute séquentiellement : * * 1 - un détachement de la base après être devenu l'utilisateur unique * * 2 - un transfert de fichier (par la commande xp_cmdshell) * * 3 - un rattachement des fichiers par CREATE DATABASE ... FOR ATTACH * ******************************************************************************* * PARAMÈTRES : * * @DB_NAME : nom de la base (doit exister) * * @DESTINATION : répertoire de destination (doit exister) * * @LOGICAL_FILE : fichier(s) à déplacer * * ATTENTION : ce dernier paramètre peut porter sur un nom logique de * * fichier dans la base (voir name dans sys.database_files) * * ou alors, pour un déplacement générique : * * [ALL] (crochets compris) pour tous les fichiers * * [DATA] (crochets compris) pour les fichiers de données * * [TRAN] (crochets compris) pour les fichiers du journal de * * transactions * ******************************************************************************* * PROBLÈMES POSSIBLES : * * - l'utilisateur qui lance cette procédure n'a pas les privilèges adéquats * * - le compte de service de l'instance SQL Server n'a pas les droits en * * lecture/ecriture sur le répertoire de destination * * - le répertoire de destination n'a pas une capacité suffisante pour y * * stocker les fichiers à déplacer * * - la base compte du stockage FILESTREAM ou des FILETABLE * * REMÈDE : * * en cas d'erreur, le résultat (paneau messages de SSMS) fournit la commande * * qui est partie en exception, mais aussi toute la partie déjà exécutée du * * script de migration. * ******************************************************************************/ BEGIN -- variables locales DECLARE @T TABLE (existe BIT, repertoire BIT, parent BIT); DECLARE @F TABLE (emplacement NVARCHAR(384), destination NVARCHAR(384)); DECLARE @SQL NVARCHAR(max), @SCRIPT NVARCHAR(max), @ERROR NVARCHAR(102); -- vérifications diverses : -- la base de données existe t-elle ? IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DB_NAME) BEGIN RAISERROR('Base de données %s inexistante.', 16, 1, @DB_NAME); RETURN; END -- est-ce une base système ? IF @DB_NAME IN ('master', 'msdb', 'tempdb', 'model') BEGIN RAISERROR('Il n''est pas possible de déplacer les fichiers d''une base de données système (%s).', 16, 1); RETURN; END; -- la destination est-elle bien un répertoire ? IF RIGHT(@DESTINATION, 1) <> '\' SET @DESTINATION = @DESTINATION + N'\'; SET @SQL = 'EXEC sys.xp_fileexist ''' + @DESTINATION +''';' INSERT INTO @T EXEC (@SQL); IF NOT EXISTS(SELECT * FROM @T WHERE repertoire = 1) BEGIN RAISERROR('L''emplacement %s n''est pas un répertoire de données.', 16, 1, @DESTINATION); RETURN; END; -- le fichier concerné existe t-il ou s'agit t-il d'un générique ? IF @LOGICAL_FILE NOT IN ('[ALL]', '[DATA]', '[TRAN]') IF NOT exists(SELECT * FROM sys.master_files WHERE DB_NAME(database_id) = @DB_NAME AND name = @LOGICAL_FILE) BEGIN RAISERROR('Le fichier logique spécifié %s pour déplacement n''est ni un générique ni un fichier particulier de cette base.', 16, 1, @LOGICAL_FILE); RETURN; END; -- la procédure xp_cmdshell est-elle utilisable ? IF NOT EXISTS(SELECT * FROM sys.configurations WHERE name = 'xp_cmdshell' AND value_in_use = 1); BEGIN RAISERROR('La procédure xp_cmdshell nécessaire à l''utilisation de cette procédure est désactivée. Veuillez contacter votre adminisrateur de bases de données', 16, 1); RETURN; END; -- on stocke les matadonnées du déplacement dans @F INSERT INTO @F SELECT physical_name, CASE WHEN @LOGICAL_FILE = '[ALL]' THEN @DESTINATION WHEN @LOGICAL_FILE = '[DATA]' AND "type" = 0 THEN @DESTINATION WHEN @LOGICAL_FILE = '[TRAN]' AND "type" = 1 THEN @DESTINATION WHEN name = @LOGICAL_FILE THEN @DESTINATION ELSE NULL END + REVERSE(SUBSTRING(REVERSE(physical_name), 1 , CHARINDEX('\', REVERSE(physical_name)) -1)) FROM sys.master_files WHERE DB_NAME(database_id) = @DB_NAME; -- construction du script de déplacement et exécution par bribes SELECT @SCRIPT = N''; BEGIN TRY -- on se met dans le contexte de la base visée SET @SQL = N'USE [' + @DB_NAME +'];'; EXEC (@SQL); SET @SCRIPT = @SCRIPT + @SQL + NCHAR(13) + NCHAR(10) + N'GO' + NCHAR(13) + NCHAR(10); -- place la base en utilisateur unique et déconnexion des autres SET @SQL = N'ALTER DATABASE [' + @DB_NAME + N'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' EXEC (@SQL); SET @SCRIPT = @SCRIPT + @SQL + NCHAR(13) + NCHAR(10) + N'GO' + NCHAR(13) + NCHAR(10); -- on se met dans le contexte de la base mster SET @SQL = N'USE master;'; EXEC (@SQL); SET @SCRIPT = @SCRIPT + @SQL + NCHAR(13) + NCHAR(10) + N'GO' + NCHAR(13) + NCHAR(10); -- on détache la abse de données SET @SQL = N'EXEC sp_detach_db ''' + @DB_NAME +N''';'; EXEC (@SQL); SET @SCRIPT = @SCRIPT + @SQL + NCHAR(13) + NCHAR(10) + N'GO' + NCHAR(13) + NCHAR(10); -- on déplace le(s) fichier(s) concerné(s) SET @SQL = N''; SELECT @SQL = @SQL + N'EXEC xp_cmdshell ''MOVE "' + emplacement + N'" "' + destination + N'"'';' + NCHAR(13) + NCHAR(10) + NCHAR(13) + NCHAR(10) FROM @F WHERE destination IS NOT NULL AND emplacement <> destination; EXEC (@SQL); SET @SCRIPT = @SCRIPT + @SQL + NCHAR(13) + NCHAR(10) + N'GO' + NCHAR(13) + NCHAR(10); -- on rattache la base SET @SQL = N'CREATE DATABASE[' + @DB_NAME +'] ON '; SELECT @SQL = @SQL + NCHAR(13) + NCHAR(10) + N'(FILENAME = N''' + COALESCE(destination, emplacement) +'''),' FROM @F; SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) -1) + NCHAR(13) + NCHAR(10) + ' FOR ATTACH;'; EXEC (@SQL); SET @SCRIPT = @SCRIPT + @SQL + NCHAR(13) + NCHAR(10) + N'GO' + NCHAR(13) + NCHAR(10); END TRY -- en cas d'ereur... BEGIN CATCH SET @ERROR = ERROR_MESSAGE(); RAISERROR('Une erreur est survenur lors de la procédure dbo.P_MOVE_DATABASE_FILES.', 16, 1); -- renseigne sur la commande ayant levée l'exception RAISERROR('L''ereur est survenue lors de la commande : %s', 16, 1, @SQL); -- renvoie la partie du script déjà exécutée RAISERROR(@ERROR, 16, 1); PRINT '--- PARTIE DU SCRIPT DÉJÀ EXÉCUTÉE ---'; PRINT @SCRIPT; END CATCH END; GO
EXEMPLES :
-- déplacement de tous les fichiers composant la base : EXEC msdb.dbo.P_MOVE_DATABASE_FILES 'DB_TEST', 'C:\db_sql\', '[ALL]' -- déplacement des fichiers du journal de transactions : EXEC msdb.dbo.P_MOVE_DATABASE_FILES 'DB_TEST', 'C:\db_sql\', '[TRAN]' -- déplacement des fichiers de données : EXEC msdb.dbo.P_MOVE_DATABASE_FILES 'DB_TEST', 'C:\db_sql\', '[DATA]' -- déplacement d'un fichier identifié par son nom logique : EXEC msdb.dbo.P_MOVE_DATABASE_FILES 'DB_TEST', 'C:\db_sql\', 'DB_TEST'
* * *
Le site web sur le SQL et les SGBDR
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
L'entreprise SQL Spot