La galère des RESTORE DATABASE WITH MOVE …

Dans la vie d’un DBA vient fatalement le moment où il faut penser à changer de serveur. Trop vieux, hors garantie, pas assez puissant … Que de bonnes raisons de passer sur du matériel dernier cri.

Et puis le jour J approchant on commence à préparer la migration. Et là, horreur, j’avais presque oublié qu’il y avait près de 200 bases à déplacer … Gloups.

Heuuu, comment faire. Sachant que tout le matériel est renouvelé, pas question de monter les nouveaux serveurs avec une configuration disque identique de manière à couper le service SQL au dernier moment et ensuite de changer le zoning des disques afin de les présenter à la nouvelle machine (cluster ou pas, il n’y a pas de différence). reste ensuite la possibilité de détacher une à une les base, de copier tous les fichiers et de les attacher sur le nouveau serveur. Pfff un peu long, et puis surtout il ne faut pas se tromper dans l’ordre des fichiers lors de l’attachement. Oui, si vous utilisez le wizard de SQL Server Management Studio, il retrouve tous les fichiers et au besoin vous corrigez les chemins. mais là, pas question de répéter cette opération 200 fois sans compter avec le temps de coupure de production que cela implique.

Finalement, j’ai opté pour la bonne vieille méthode du BACKUP/RESTORE. D’accord mais encore faut il essayer d’automatiser la chose. Et puis reste le problème des disques qui ne portent pas forcément la même lettre de volume.

Voici donc une stratégie possible :

  • l’ancien server SQL est toujours en service et réponds toujours aux requêtes
  • on désactive tous les jobs de backup planifiés
  • on fait une sauvegarde complète de toutes les bases (backup database …)
  • on restaure toutes les bases en NORECOVERY sur le nouveau serveur avec option MOVE sur les fichiers
  • une fois que c’est OK, le plus gros du travail est fait.  On prends un café (ben oui, le DBA travaille souvent de nuit sur ce genre d’opérations)
  • on fait une sauvegarde différentielle des bases les plus actives pour ne pas a avoir à restaurer trop de fichiers de journaux de transaction (backup database with differential …)
  • une fois restaurées ces différentielles (NORECOVERY), il est préférable de diminuer au maximum l’activité sur le serveur SQL, la pré-retraite a sonné pour lui.
  • une dernière sauvegarde du journal des transactions (backup log..)
  • A ce moment là, soit
    • on met offline l’ancienne base
    • on garde online mais un profiler trace toutes les requêtes afin de les rejouer sur le nouveau serveur si elles sont jugées vraiment importantes
  • on restaure tous les derniers journaux de transaction avec l’option RECOVERY et le nouveau serveur est opérationnel (avec comme pré-requis que tous les logins aient été transférés si possible avec les même SID de manière a éviter les fastidieux sp_change_users_login.
  • on coupe l’ancien server SQL
  • on ajoute l’adresse IP de l’ancien serveur sur le nouveau serveur afin que toutes les applications n’aient pas a être reconfigurées (si vous avez opté pour cette stratégie car il est tout a fait envisageable de changer les chaines de connexion des applications, ou bien de changer un alias DNS).
  • on peut prendre un nouveau café et se détendre, l’affaire est bouclée.

Ceux qui ont réussi à me suivre jusque là (ok, c’était pas très dur non plus) ont peut être remarqué que je parlais de l’option MOVE sur la restauration initiale.

La page du site Microsoft Technet dédiée à la restauration (c’est pour la version SQL Server 2005, c’est pareil pour SQL Server 2008) parle de cette option, je ne vais pas faire de copier-coller mais en voici un exemple :

image

Cette option permet de déplacer les fichier de bases de données. Mais mon but est d’automatiser cette restauration. Galère en vue.

Et bien, finalement, non, grâce à un petit script, il est possible de générer du code SQL à exécuter sur le serveur source afin d’obtenir les ordres SQL de RESTAURE DATABASE :

DECLARE @tmpstr  varchar (256)
DECLARE @db_name  varchar (256)

DECLARE db_curs CURSOR FOR
    SELECT name FROM master..sysdatabases
    WHERE dbid > 4

OPEN db_curs
FETCH NEXT FROM db_curs INTO @db_name
    PRINT ‘SET NOCOUNT ON’
    WHILE (@@fetch_status = 0)
    BEGIN
            PRINT ‘USE ‘ + @db_name
            PRINT ‘GO’
            PRINT ‘PRINT  »RESTORE DATABASE ‘ + @db_name + ‘  » ‘
            PRINT ‘PRINT  »FROM DISK= » »c:\’ + @db_name + ‘.bak » »  » ‘
            PRINT ‘PRINT  »WITH » ‘
            PRINT ‘SELECT  »MOVE  » » » + rtrim(ltrim(name)) +  » » » TO  » »J:\ » + reverse(substring(reverse(ltrim(rtrim(filename))),1,charindex( »\ »,reverse(ltrim(rtrim(filename))))-1)) +  » » »,  » from sysfiles’
            PRINT ‘PRINT  »NORECOVERY; » ‘
            PRINT ‘PRINT  »GO » ‘
            PRINT ‘GO’
            PRINT  »
            FETCH NEXT FROM db_curs INTO @db_name
      END
CLOSE db_curs
DEALLOCATE db_curs

Le code SQL généré (à exécuter aussi sur le serveur source) :

SET NOCOUNT ON
USE Base1
GO
PRINT ‘RESTORE DATABASE Base1 ‘
PRINT ‘FROM DISK= »c:\Base1.bak » ‘
PRINT ‘WITH’
SELECT ‘MOVE  »’ + rtrim(ltrim(name)) +  »’ TO  »J:\’ + reverse(substring(reverse(ltrim(rtrim(filename))),1,charindex(‘\’,reverse(ltrim(rtrim(filename))))-1)) +  »’, ‘ from sysfiles
PRINT ‘NORECOVERY;’
PRINT ‘GO’
GO
USE Base2
GO
PRINT ‘RESTORE DATABASE Base2 ‘
PRINT ‘FROM DISK= »c:\Base2.bak » ‘
PRINT ‘WITH’
SELECT ‘MOVE  »’ + rtrim(ltrim(name)) +  »’ TO  »J:\’ + reverse(substring(reverse(ltrim(rtrim(filename))),1,charindex(‘\’,reverse(ltrim(rtrim(filename))))-1)) +  »’, ‘ from sysfiles
PRINT ‘NORECOVERY;’
PRINT ‘GO’
GO

L’exécution de ce code génère les ordres de RESTORE DATABASE a exécuter sur le serveur destination cette fois ci …

RESTORE DATABASE Base1
FROM DISK=’c:\Base1.bak’
WITH

MOVE ‘Base1_Data’ TO ‘J:\Base1_Data.MDF’,
MOVE ‘Base1_Log’ TO ‘J:\Base1_Log.LDF’,
MOVE ‘Base1_Data2’ TO ‘J:\Base1_Data2.NDF’,
MOVE ‘Base1_Data3’ TO ‘J:\Base1_Data3.NDF’,
MOVE ‘Base1_Data4’ TO ‘J:\Base1_Data4.NDF’,
MOVE ‘Base1_Data5’ TO ‘J:\Base1_Data5.NDF’,
MOVE ‘Base1_Data6’ TO ‘J:\Base1_Data6.NDF’,
MOVE ‘Base1_Index_Data’ TO ‘J:\Base1_Index_Data.ndf’,
MOVE ‘Base1_Index2’ TO ‘J:\Base1_Index2.NDF’,
MOVE ‘Base1_Index3’ TO ‘J:\Base1_Index3.NDF’,
MOVE ‘Base1_Index4’ TO ‘J:\Base1_Index4.NDF’,
MOVE ‘Base1_Index5’ TO ‘J:\Base1_Index5.NDF’,
MOVE ‘Base1_Index6’ TO ‘J:\Base1_Index6.NDF’,

NORECOVERY;
GO
RESTORE DATABASE Base2
FROM DISK=’c:\Base2.bak’
WITH

MOVE ‘Base2_Data’ TO ‘J:\Base2_Data.MDF’,
MOVE ‘Base2_Log’ TO ‘J:\Base2_Log.LDF’,

NORECOVERY;
GO

Les lettres des disques doivent être adaptés à vos besoins mais c’est une bonne base de travail. Il est très simple d’adapter cet exemple pour faire les backup de toutes les bases du serveur source, les backup différentiels, les restaure log, etc …

RESTAURE DATABASE WITH MOVE, une galère, assurément pas !!!!

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Cet article a été publié dans SQL Server. Ajoutez ce permalien à vos favoris.

Un commentaire pour La galère des RESTORE DATABASE WITH MOVE …

  1. Génial merci, ça m’a fait gagner quelque heure de recherche.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s