Déplacement de base sql server

8 août

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’

 

 

 

 

 

 

Pas encore de commentaire

Laisser une réponse

Unblog.fr | Créer un blog | Annuaire | Signaler un abus