Cette procédure permet de déplacer les fichiers d’une base de données. LA procédure esr données « as is ».
CREATE PROCEDURE [dbo].[P_MOVE_FILE_DATABASE]
@DB_NAME sysname,
@DB_DATAFILE_DIR NVARCHAR(1024),
@DB_TRANFILE_DIR NVARCHAR(1024) = NULL
AS
/******************************************************************************
* Frédéric Brouard / alias SQLpro - Expert Microsoft SQL Server / SGBDR / SQL *
* Sté. SQL spot http://www.sqlspot.com - audit, tuning, conseil, formations *
* Enseignant Arts et Métiers, ISEN, EXIA (CESI) - Conférencier U. Toulouse II *
*******************************************************************************
* @DB_NAME : nom de la base *
* @DB_DATAFILE_DIR : nouveau répertoire des fichiers de données *
* @DB_TRANFILE_DIR : nouveau répertoire des fichiers de transactions *
*******************************************************************************
* © Frédéric Brouard / SQLspot - 2010-04-27 - http://sqlpro.developpez.com *
******************************************************************************/
SET NOCOUNT ON;
-- vérification de saisie du répertoire de destination
IF @DB_DATAFILE_DIR IS NULL
BEGIN
RAISERROR('Le répertoire de destination de la base n''a pas été indiqué.', 16, 1, @DB_NAME);
RETURN;
END;
-- teste si le répertoire est bien définit :
IF SUBSTRING(REVERSE(@DB_DATAFILE_DIR), 1, 1) <> '\'
SET @DB_DATAFILE_DIR = @DB_DATAFILE_DIR +'\';
-- si le repertoire de destination du JT n'est pas précisé, prendre celui des données
IF @DB_TRANFILE_DIR IS NULL
SET @DB_TRANFILE_DIR = @DB_DATAFILE_DIR
ELSE
IF SUBSTRING(REVERSE(@DB_TRANFILE_DIR), 1, 1) <> '\'
SET @DB_TRANFILE_DIR = @DB_TRANFILE_DIR +'\';
-- on vérifie si cette base existe bien
IF NOT EXISTS(SELECT *
FROM sys.databases
WHERE name = @DB_NAME)
BEGIN
RAISERROR('la base de données %s n''existe pas.', 16, 1, @DB_NAME);
RETURN;
END;
-- configuration du serveur pour l'activation des commandes OS
DECLARE @SQL NVARCHAR(max);
DECLARE @SHOW BIT, @SHEL BIT;
DECLARE @CONFIG TABLE (CFG_NAME sysname,
CFG_MIN INT,
CFG_MAX INT,
CFG_VALUE INT,
CFG_RUN INT);
INSERT INTO @CONFIG
EXEC sp_configure 'show advanced options';
SELECT @SHOW = CFG_RUN FROM @CONFIG;
IF @SHOW =0
BEGIN
SET @SQL = 'EXEC sp_configure ''show advanced options'', 1;'
EXEC (@SQL);
SET @SQL = 'RECONFIGURE'
EXEC (@SQL);
END;
DELETE FROM @CONFIG;
INSERT INTO @CONFIG
EXEC sp_configure 'xp_cmdshell';
SELECT @SHEL = CFG_RUN FROM @CONFIG;
IF @SHEL =0
BEGIN
SET @SQL = 'EXEC sp_configure ''xp_cmdshell'', 1;'
EXEC (@SQL);
SET @SQL = 'RECONFIGURE'
EXEC (@SQL);
END;
-- vérification de l'existence du répertoire
EXEC master.dbo.xp_subdirs @DB_DATAFILE_DIR
IF @@ERROR <> 0
BEGIN
RAISERROR('Le répertoire de stockage des données %s n''existe pas.', 16, 1, @DB_DATAFILE_DIR);
GOTO LBL_RESUME;
END;
EXEC master.dbo.xp_subdirs @DB_TRANFILE_DIR
IF @@ERROR <> 0
BEGIN
RAISERROR('Le répertoire de stockage du journal de transaction %s n''existe pas.', 16, 1, @DB_TRANFILE_DIR);
GOTO LBL_RESUME;
END;
-- on récupére l'emplacement, le nom et la taille des fichiers
DECLARE @FILES TABLE (FLS_ID INT,
FLS_PATH NVARCHAR(1024),
FLS_TYPE SMALLINT,
FLS_NAME NVARCHAR(256),
FLS_LOGIQUE sysname,
FLS_SIZE BIGINT,
FLS_OK BIT,
FLS_NEWPATH NVARCHAR(1024));
-- mise à jour des fichiers pour taille exacte
SET @SQL = 'DBCC UPDATEUSAGE (''' + @DB_NAME + ''')';
EXEC (@SQL);
INSERT INTO @FILES
SELECT "file_id",
REVERSE(SUBSTRING(REVERSE(physical_name), CHARINDEX('\', REVERSE(physical_name)), LEN(physical_name)+1)),
"type",
REVERSE(SUBSTRING(REVERSE(physical_name), 1, CHARINDEX('\', REVERSE(physical_name))-1)),
name, (size / 128) + 1, 1,
CASE
WHEN "type" = 0 THEN @DB_DATAFILE_DIR
WHEN "type" = 1 THEN @DB_TRANFILE_DIR
ELSE NULL
END
FROM sys.master_files
WHERE database_id = DB_ID(@DB_NAME);
-- test si "fichiers" non déplaçable
IF EXISTS(SELECT *
FROM @FILES
WHERE FLS_TYPE > 1)
BEGIN
RAISERROR('la base de données %s contient au moins un fichier non déplaçable (FILESTREAM par exemple).', 16, 1, @DB_NAME);
RETURN;
END;
-- vérification : si un seul fichier existe déjà dans les nouveaux répertoires alors on abandonne.
DECLARE @CMD NVARCHAR(1024);
DECLARE @F TABLE (F_EXISTS BIT,
F_DIR BIT,
F_ADIR BIT);
DECLARE C CURSOR
FOR
SELECT FLS_NEWPATH + FLS_NAME AS F
FROM @FILES
FOR UPDATE OF FLS_OK;
OPEN C;
FETCH C INTO @CMD;
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM @F;
INSERT INTO @F
EXEC master.dbo.xp_fileexist @CMD;
IF EXISTS(SELECT * FROM @F WHERE F_EXISTS = 1)
UPDATE @FILES
SET FLS_OK = 0
WHERE CURRENT OF C;
FETCH C INTO @CMD;
END;
CLOSE C;
DEALLOCATE C;
-- y a t-il des fichiers non OK ?
IF EXISTS(SELECT *
FROM @FILES
WHERE FLS_OK = 0)
BEGIN
SET @SQL = 'Déplacement impossible : des fichiers ne pourrons pas être créés car ils existent déjà . (';
SELECT @SQL = @SQL
+ CASE
WHEN FLS_TYPE = 0 THEN @DB_DATAFILE_DIR
WHEN FLS_TYPE = 1 THEN @DB_TRANFILE_DIR
END + FLS_NAME + ', '
FROM @FILES
WHERE FLS_OK = 0;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + ')';
RAISERROR(@SQL, 16, 1);
RETURN;
END;
-- si la capacité ne permet pas de recréer le fichier, alors on abandonne
DECLARE @U TABLE (U_DRIVE_LETTER NVARCHAR(128),
U_FREESIZE_MO BIGINT);
INSERT INTO @U
EXEC master.dbo.xp_fixeddrives;
IF EXISTS(SELECT U.U_DRIVE_LETTER
FROM (SELECT SUBSTRING(FLS_NEWPATH, 1, 1) AS FLS_U, SUM(FLS_SIZE) AS FLS_SZ
FROM @FILES
GROUP BY SUBSTRING(FLS_NEWPATH, 1, 1)) AS F
INNER JOIN @U AS U
ON F.FLS_U = U.U_DRIVE_LETTER
AND F.FLS_SZ > U.U_FREESIZE_MO)
BEGIN
SET @SQL = 'Déplacement impossible : la capacité d''une des unités de stockage ne permet pas le déplacement. (';
SELECT @SQL = @SQL + U_DRIVE_LETTER + ', '
FROM (SELECT SUBSTRING(FLS_NEWPATH, 1, 1) AS FLS_U, SUM(FLS_SIZE) AS FLS_SZ
FROM @FILES
GROUP BY SUBSTRING(FLS_NEWPATH, 1, 1)) AS F
INNER JOIN @U AS U
ON F.FLS_U = U.U_DRIVE_LETTER
AND F.FLS_SZ > U.U_FREESIZE_MO;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + ')';
RAISERROR(@SQL, 16, 1);
RETURN;
END;
-- on vire les utilisateurs de la base
SET @SQL = 'ALTER DATABASE [' + @DB_NAME +'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
EXEC (@SQL);
-- on détache la base
EXEC sp_detach_db @DB_NAME;
-- on transfère les fichiers
DECLARE C CURSOR
FOR
SELECT 'MOVE /Y "' + FLS_PATH + FLS_NAME + '" "'
+ FLS_NEWPATH + FLS_NAME + '"'
FROM @FILES
FOR READ ONLY;
OPEN C;
FETCH C INTO @CMD;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC master.dbo.xp_cmdshell @CMD;
FETCH C INTO @CMD;
END;
CLOSE C;
DEALLOCATE C;
-- on recrée la base à partir des fichiers déplacés.
SET @SQL = 'CREATE DATABASE [' + @DB_NAME +'] ON ('
SELECT @SQL = @SQL + ' NAME = [' + FLS_LOGIQUE +'], '
+ ' FILENAME = '''
+ FLS_NEWPATH + FLS_NAME + '''), ('
FROM @FILES;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) -3) + ' FOR ATTACH;';
EXEC (@SQL);
-- finalisation : remplacement des options du serveur à l'original
LBL_RESUME:
IF @SHEL = 0
BEGIN
SET @SQL = 'EXEC sp_configure ''xp_cmdshell'', 0;'
EXEC (@SQL);
SET @SQL = 'RECONFIGURE'
EXEC (@SQL);
END;
IF @SHOW = 0
BEGIN
SET @SQL = 'EXEC sp_configure ''show advanced options'', 0;'
EXEC (@SQL);
SET @SQL = 'RECONFIGURE'
EXEC (@SQL);
END;
--------
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 * * * * *
