Cet article présente un script permettant de migrer d’un seul coup toutes les base de données d’un serveur d’un emplacement à l’autre.
Pour déplacer les fichiers des bases de données, il suffit de faire un détachement (sp_detach_db) puis un rattachement de ces mêmes fichiers par un CREATE DATABASE … FOR ATTACH.
Pour copier des fichiers il suffit d’utiliser xp_cmdshell.
Ce script utilise une table pour stocker toutes les commandes SQL intermédiaires afin de faciliter un retour en arrière…
le script :
/******************************************************************************
* MIGRATION DE BASE DE DONNÉES *
*******************************************************************************
* Ce script permet de migrer les fichiers d'une base de données *
******************************************************************************/
-- 1) paramétrage de SQL Server pour ce faire :
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
USE msdb;
GO
IF NOT EXISTS(SELECT * FROM msdb.INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'S_COPY')
EXECUTE ('CREATE SCHEMA S_COPY');
GO
IF NOT EXISTS(SELECT * FROM msdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T_COPY_DATABASE_CDB' AND TABLE_SCHEMA = 'S_COPY')
EXECUTE ('
CREATE TABLE S_COPY.T_COPY_DATABASE_CDB
(CDB_ID INT IDENTITY NOT NULL PRIMARY KEY,
CDB_NATURE VARCHAR(128),
CDB_ACTION SMALLINT,
CDB_ORDRE SMALLINT,
CDB_SQL NVARCHAR(max));')
GO
-------------------------------------------------------------------------------
DECLARE @PATH_TO_GO NVARCHAR(256); -- nouvel emplacement de toutes les bases
SET @PATH_TO_GO = '???'; -- exemple : D:\DATABASES\ ... doit comporter un \ final !
-------------------------------------------------------------------------------
DECLARE @T TABLE (fileOK BIT, DirOK BIT, DirParentOK BIT);
INSERT INTO @T
EXEC master.sys.xp_fileexist @PATH_TO_GO;
IF NOT EXISTS(SELECT * FROM @T WHERE DirOK = 1) OR SUBSTRING(@PATH_TO_GO, LEN(@PATH_TO_GO), 1) <> '\'
BEGIN
RAISERROR('Le répertoire passé en argument %s n''est pas correct. Abandon du script.', 16, 1, @PATH_TO_GO);
RETURN;
END;
-- 1) requête de rattachement à l'origine (a compléter manuellement)
WITH T0
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY DB_NAME(database_id) ORDER BY name) AS UP,
DB_NAME(database_id) AS NOM_DB,
'(NAME = ''' + name +''', FILENAME = ''' + physical_name +''')' AS FICHIER_DB,
ROW_NUMBER() OVER(PARTITION BY DB_NAME(database_id) ORDER BY name DESC) AS DOWN,
ROW_NUMBER() OVER(ORDER BY DB_NAME(database_id), name) AS RNK
FROM sys.master_files
WHERE DB_NAME(database_id) NOT IN ('master', 'model', 'msdb', 'tempdb')
AND DB_NAME(database_id) NOT LIKE 'ReportServer%'
),
T1 AS
(
SELECT CASE WHEN UP= 1 THEN 'CREATE DATABASE [' + NOM_DB +'] ON ' ELSE '' END AS DEBUT,
FICHIER_DB AS CORPS, CASE WHEN DOWN=1 THEN ' FOR ATTACH;' ELSE ',' END AS FIN,
NOM_DB, UP, RNK
FROM T0
)
INSERT INTO msdb.S_COPY.T_COPY_DATABASE_CDB
SELECT 'Rattachement à la source', 4, RNK, DEBUT + CORPS + FIN
FROM T1
ORDER BY RNK;
-- 2) requête de rattachement à destination
WITH T0 AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY DB_NAME(database_id) ORDER BY name) AS UP,
DB_NAME(database_id) AS NOM_DB,
'(NAME = '''+ name +''', FILENAME = ''' + @PATH_TO_GO
+ REVERSE(SUBSTRING(REVERSE(physical_name), 1, CHARINDEX('\', REVERSE(physical_name)) - 1 )) +''')' AS FICHIER_DB,
ROW_NUMBER() OVER(PARTITION BY DB_NAME(database_id) ORDER BY name DESC) AS DOWN,
ROW_NUMBER() OVER(ORDER BY DB_NAME(database_id), name) AS RNK
FROM sys.master_files
WHERE DB_NAME(database_id) NOT IN ('master', 'model', 'msdb', 'tempdb')
AND DB_NAME(database_id) NOT LIKE 'ReportServer%'
),
T1 AS
(
SELECT CASE WHEN UP= 1 THEN 'CREATE DATABASE [' + NOM_DB +'] ON ' ELSE '' END AS DEBUT,
FICHIER_DB AS CORPS, CASE WHEN DOWN=1 THEN ' FOR ATTACH;' ELSE ',' END AS FIN,
NOM_DB, UP, RNK
FROM T0
)
INSERT INTO msdb.S_COPY.T_COPY_DATABASE_CDB
SELECT 'Rattachement à la destination', 3, RNK, DEBUT + CORPS + FIN
FROM T1
ORDER BY RNK;
-- 3) requête de copie des fichiers
WITH T0 AS
(
SELECT DB_NAME(database_id) AS nom, 'EXEC xp_cmdshell ''COPY "' + physical_name +'", "' + + @PATH_TO_GO
+ REVERSE(SUBSTRING(REVERSE(physical_name), 1, CHARINDEX('\', REVERSE(physical_name)) - 1 )) +'"'';' AS ORDRE,
ROW_NUMBER() OVER(ORDER BY DB_NAME(database_id), name) AS RNK
FROM sys.master_files
WHERE DB_NAME(database_id) NOT IN ('master', 'model', 'msdb', 'tempdb')
AND DB_NAME(database_id) NOT LIKE 'ReportServer%'
)
INSERT INTO msdb.S_COPY.T_COPY_DATABASE_CDB
SELECT 'Copie des fichiers', 2, RNK, ORDRE
FROM T0
ORDER BY nom;
-- 4) requête de détachement des bases
INSERT INTO msdb.S_COPY.T_COPY_DATABASE_CDB
SELECT 'Détachement des bases à l''origine', 1, ROW_NUMBER() OVER(ORDER BY DB_NAME(database_id), name),
'USE [' + name +']; ALTER DATABASE [' + name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; USE master; EXEC sp_detach_db ''' + name +''';'
FROM sys.databases
WHERE DB_NAME(database_id) NOT IN ('master', 'model', 'msdb', 'tempdb')
AND DB_NAME(database_id) NOT LIKE 'ReportServer%';
DECLARE @SQL NVARCHAR(max);
-- exécution du déttachement
SET @SQL = '';
SELECT @SQL = @SQL + CDB_SQL
FROM S_COPY.T_COPY_DATABASE_CDB
WHERE CDB_ACTION = 1;
EXEC (@SQL);
-- exécution de la copie
SET @SQL = '';
SELECT @SQL = @SQL + CDB_SQL
FROM S_COPY.T_COPY_DATABASE_CDB
WHERE CDB_ACTION = 2;
EXEC (@SQL);
-- éxécution du rattachement à destination
SET @SQL = '';
SELECT @SQL = @SQL + CDB_SQL
FROM S_COPY.T_COPY_DATABASE_CDB
WHERE CDB_ACTION = 3;
EXEC (@SQL);
-- EN CAS DE PLANTAGE :
-- RATTACHEMENT DES BASES A L'ORIGINE
/*
DECLARE @SQL2 NVARCHAR(max);
SET @SQL2 = '';
SELECT @SQL2 = @SQL2 + CDB_SQL
FROM S_COPY.T_COPY_DATABASE_CDB
WHERE CDB_ACTION = 4;
EXEC (@SQL2);
*/
2 – Fonctionnement…
0) par prudence, sauvegardez toutes vos bases.
1) remplacer dans ce script le contenu de la variable @PATH_TO_GO par le chemin de destination, devant se terminer par \.
Exemple : D:\DATABASES\
2) lancer le script.
Ceci détache les bases, conserve les fichiers, copie les fichiers, rattache les bases avec les fichiers à destination.
Toutes les commandes SQL passées figurent dans une table accessible par :
SELECT * FROM msdb.S_COPY.T_COPY_DATABASE_CDB
les fichiers des bases ne sont pas détruits de leur emplacement d’origine.
En cas de plantage du script, exécuter la dernière partie du script qui est entre commentaires :
DECLARE @SQL2 NVARCHAR(max);
SET @SQL2 = '';
SELECT @SQL2 = @SQL2 + CDB_SQL
FROM S_COPY.T_COPY_DATABASE_CDB
WHERE CDB_ACTION = 4;
EXEC (@SQL2);
*/
Ceci rattache les bases comme à l’initial.
4) vérifiez que toutes vos bases sont en production
5) si tout est OK, vous pouvez supprimez la table msdb.S_COPY.T_COPY_DATABASE_CDB
(mais attendez quelques jours avant).
Le même script peut être utilisé pour un retour arrière, mais supprimez avant les fichiers résiduels de l’emplacement d’origine !
--------
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 * * * * *
