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

-- 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'