Il est parfois nécessaire de déplacer plusieurs base sql server d’une instance à une autre sur le même serveur ou sur un autre serveur, dans le cadre :
* D’une migration
* D’un manque d’espace sur le serveur
* Pour une amélioration de performance .
* ect ..
0) pré-requis
Arrêter s’il existe la réplication sur l’instance .
1) détache les bases
le script ci-dessous permet de générer les commandes à exécuter si l’instance comporte plusieurs base .
SELECT DISTINCT’exec sp_detach_db »’ + DB_NAME(dbid) + »’;’
FROM master.dbo.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN (‘E’,’F’)
AND DATABASEPROPERTYEX( DB_NAME(dbid) , ‘Status’ ) = ‘ONLINE’
AND DB_NAME(dbid) NOT IN (‘master’,’tempdb’,’msdb’,’model’)
GO
exec sp_detach_db ‘BMC_ODS_FRONT’;
exec sp_detach_db ‘DATACACHE’;
exec sp_detach_db ‘distribution’;
exec sp_detach_db ‘DWH_UTILITY’;
3) attacher les bases
Il faut copier les fichiers des bases à déplacer sur le nouveau serveurs avant d’exécuter le script ci-dessous :
INSERT INTO #Attach
SELECT DISTINCT DB_NAME(dbid) AS dbname, fileid, filename, CONVERT(VARCHAR(MAX), ») AS TxtAttach
FROM master.dbo.sysaltfiles
WHERE dbid IN (SELECT dbid FROM master.dbo.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN (‘E’,’F’))
AND DATABASEPROPERTYEX( DB_NAME(dbid) , ‘Status’ ) = ‘ONLINE’
AND DB_NAME(dbid) NOT IN (‘master’,’tempdb’,’msdb’,’model’)
ORDER BY dbname, fileid, filename
UPDATE #Attach
SET @cmd = TxtAttach =
CASE WHEN dbname <> @prevdbname
THEN CONVERT(VARCHAR(200),’exec sp_attach_db @dbname = N »’ + dbname + » »)
ELSE @cmd
END +’,@filename’ + CONVERT(VARCHAR(10),fileid) + ‘=N »’ + filename + » »,
@prevdbname = CASE WHEN dbname <> @prevdbname THEN dbname ELSE @prevdbname END,
@dbname = dbname
FROM #Attach WITH (INDEX(0),TABLOCKX)
OPTION (MAXDOP 1)
SELECT TxtAttach
FROM
(SELECT dbname, MAX(TxtAttach) AS TxtAttach FROM #Attach
GROUP BY dbname) AS x
DROP TABLE #Attach
GO
4) il faut recréer les users dans la nouvelle instance :
Lorsqu’on déplace ou restaure une base d’une instance à une autre on peut avoir des users orphaned
se positionner sur la base master
–1 lists the orphaned users:
EXEC sp_change_users_login ‘Report’
–2 s’il on a des users orphaned il faut dans ce cas les fixer sur la nouvelle instance
EXEC sp_change_users_login ‘Auto_Fix’, ‘user_name’
–3 si on veut recrrer les nouveaux logins dans la nouvelle instance :
EXEC sp_change_users_login ‘Auto_Fix’, ‘user’, ‘login’, ‘password’