Migration SQL – Save / Copy / Restore

Voici les scripts utilisés pour faire une migration de base de données

Etape 1

  • Save BDD dans un répertoire
  • Compactage (Format cab)
  • Copy des BDD sur le nouveau serveur

Etape 2

  • Extract des Backups SQL

Etape 3

  • Save / Restore SQL USER (avec mdp)

Etape 4

  • Restauration sur le nouveau serveur SQL

Etape 1 – script Save + Copy

@ECHO OFF
for /F "tokens=1,2,3 delims=/ " %%i in ('date /t') do set date=%%k.%%j.%%i
for /F "tokens=1,2 delims=: " %%i in ('time /t') do set time=%%ih%%j
SET LOG=C:\logs\%date%_%time%_backupSQL_reloc.txt
echo +++++ initialisation du log +++++ > %log%
 
ECHO Save Backup SQL - utilisation SP_SaveBDD >> %log%
ECHO ACTION: sqlcmd -E -S OldServeur -d master -Q "EXEC dbo.SP_SaveBDD 0,1,'e:\BACKUP'" >> %log%
sqlcmd -E -S OldServeur -d master -Q "EXEC dbo.SP_SaveBDD 0,1,'e:\backup'" >> %log%
 
ECHO ACTION: Copie des backup  >> %log% 
SET SRC5=\\OldServer\BACKUP
SET DST5=E:\Destination
robocopy /E /TEE /XO /R:1 /W:1 %SRC5% %DST5% /LOG+:%LOG%

Etape 2 – Script Extract des backup SQL format cab

@ECHO OFF
for /F "tokens=1,2,3 delims=/ " %%i in ('date /t') do set date=%%k.%%j.%%i
for /F "tokens=1,2 delims=: " %%i in ('time /t') do set time=%%ih%%j
SET LOG=c:\logs\%date%_%time%_extract_backup_sql.txt
SET BCKDIR=D:\BACKUPSQL
echo +++++ initialisation du log +++++ > %log%
PUSHD %BCKDIR%
ECHO Extraction des backups SQL  >> %log%
FOR /F %%i in ('dir /b') do extrac32 /Y /L %BCKDIR%\extract %%i >> %log%
POPD

Etape 2 – Restore BDD from dir

@ECHO OFF
for /F "tokens=1,2,3 delims=/ " %%i in ('date /t') do set date=%%k.%%j.%%i
for /F "tokens=1,2 delims=: " %%i in ('time /t') do set time=%%ih%%j
SET LOG=E:\exploitation\logs\%date%_%time%_restore_backup_sql.txt
SET BCKDIR=D:\BACKUPSQL
echo +++++ initialisation du log +++++ > %log%
ECHO Restore Backup SQL - utilisation SP_RESTORE_DB_FROM_BACKUP_FILE >> %log%
ECHO ACTION: sqlcmd -E -S NewServeur -d master -Q "EXEC dbo.SP_RESTORE_DB_FROM_BACKUP_FILE '%BCKDIR%\extract'" >> %log%
sqlcmd -E -S NewServeur -d master -Q "EXEC dbo.SP_RESTORE_DB_FROM_BACKUP_FILE '%BCKDIR%\extract'" >> %log%

Voici les procédures stockées utilisées pour ces scripts

Etape 1 – Procédure stockée SQL – SaveBDD

-----------------------------------------------------------------
CREATE PROCEDURE dbo.SP_SaveBDD
/*
Permet de sauvegarder toutes les bases et logs et de les compresser (CAB)
de toutes les bases du serveur SQL Server
*/
@SauvegardeLogs AS INT,
@SauvegardeBases AS INT,
@RepertoireSauvegarde AS VARCHAR(8000)
AS
----------------------------------------------------------------
-- Déclaration des Variables
DECLARE @name sysname
DECLARE @STATUS INT
DECLARE @TestAutoTruncate bit
DECLARE @LaRequette VARCHAR(8000)
DECLARE @DateJour VARCHAR(20)
DECLARE @Repertoire VARCHAR(8000)
DECLARE @RepertoireDefaut VARCHAR(100)
 
-----------------------------------------------------------------
-- Initialisation des Variables déclarées
SET @DateJour = REPLACE(CONVERT(VARCHAR, GetDate(), 102), '.', '_')
SET @RepertoireDefaut = 'C:\BACKUPBASES\'
 
-----------------------------------------------------------------
PRINT '-----------------------------------------------------'
PRINT ' DATE DE LA SAUVEGARDE LANCEE : '+ @DateJour
PRINT '-----------------------------------------------------'
 
-----------------------------------------------------------------
-- Vérification du paramètre du répertoire des Bases et Logs
IF RTRIM(@RepertoireSauvegarde) != ''
BEGIN
PRINT 'Répertoire Fourni à la SP'
IF RIGHT(@RepertoireSauvegarde, 1) != '\'
SET @Repertoire = @RepertoireSauvegarde + '\'
ELSE
SET @Repertoire = @RepertoireSauvegarde
END
ELSE
BEGIN
PRINT 'Répertoire Non Fourni à la SP - Création du répertoire par défaut : '+ @RepertoireDefaut
SET @LaRequette = 'master.dbo.xp_cmdshell ''MKDIR '+ @RepertoireDefaut +''', NO_OUTPUT '
PRINT 'Requette : '+ @LaRequette
EXECUTE (@LaRequette)
SET @Repertoire = @RepertoireDefaut
END
 
PRINT 'Répertoire de Sauvegarde des Bases et Logs : '+ @Repertoire
 
-----------------------------------------------------------------
-- Déclaration du Curseur sur la liste des Bases de données
DECLARE TESTCURSEUR CURSOR
FOR SELECT master.dbo.sysdatabases.name, master.dbo.sysdatabases.status
FROM master.dbo.sysdatabases WHERE master.dbo.sysdatabases.status = 24
 
-- WHERE (master.dbo.sysdatabases.status & 8)!= 8
-- WHERE master.dbo.sysdatabases.name NOT IN ('tempdb', 'model', 'pubs')
 
-- Ouverture du Curseur
OPEN TESTCURSEUR
FETCH NEXT FROM TESTCURSEUR
INTO @name, @status
 
-- Bouclage sur le curseur défini
WHILE @@FETCH_STATUS = 0
 
BEGIN
-- Vérifie que la base de transaction n'est pas en mode simple (donc pas de log à sauver)
SET @TestAutoTruncate = @STATUS & 8
 
/*
-- Suivi des valeurs pour tests
PRINT 'BASE : '+ @name
PRINT 'TEST TRUNCATE : '+ CAST(@TestAutoTruncate AS VARCHAR)
PRINT 'Valeur Status : '+ CAST(@status AS VARCHAR)
*/
 
-----------------------------------------------------------------
-- Vérification de la sauvegarde des Logs de Bases demandée
IF (@SauvegardeLogs = 1) AND (@TestAutoTruncate = 0)
BEGIN
 
PRINT ''
PRINT '-----------------------------------------------------'
PRINT 'BACKUP DU LOG DE LA BASE DE DONNEES : '+ @name
 
----------------- LOGS -----------------------------
-- Sauvegarde du Log de transaction courant dans le Curseur
SET @LaRequette = 'USE '+ @name +' EXEC sp_addumpdevice ''DISK'', '''+ @name +'Log'', '''+ @Repertoire + @name +'Log.BAK'' BACKUP LOG '+ @name +' TO '+ @name +'Log'
PRINT 'Requette : '+ @LaRequette
EXECUTE (@LaRequette)
 
-- Suppression du device déclaré dans la table Master.Sysdevices
SET @LaRequette = 'USE '+ @name +' EXEC sp_dropdevice '''+ @name +'Log'' '
PRINT 'Requette : '+ @LaRequette
EXECUTE (@LaRequette)
 
-- Archivage du fichier BAK avec le format Windows CAB
SET @LaRequette = 'master.dbo.xp_cmdshell ''C:\WINDOWS\system32\makecab /V[1] /L '+ @Repertoire +' '+ @Repertoire + @name +'Log.BAK '+ @name +'Log_'+ @DateJour +'.cab'', NO_OUTPUT '
PRINT 'Requette : '+ @LaRequette
EXECUTE (@LaRequette)
 
-- Suppression du fichier BAK
SET @LaRequette = 'master.dbo.xp_cmdshell ''DEL '+ @Repertoire + @name +'Log.BAK'', NO_OUTPUT '
PRINT 'Requette : '+ @LaRequette
EXECUTE (@LaRequette)
END
 
-----------------------------------------------------------------
-- Vérification de la sauvegarde des Bases de données demandée
IF @SauvegardeBases = 1
BEGIN
PRINT ''
PRINT '-----------------------------------------------------'
PRINT 'BACKUP DE LA BASE DE DONNEES : '+ @name
----------------- BASES ----------------------------
-- Sauvegarde de la base courante dans le Curseur
SET @LaRequette = 'USE '+ @name +' BACKUP DATABASE '+ @name +' TO DISK ='''+ @Repertoire + @name +'.BAK'''
PRINT 'Requette : '+ @LaRequette
EXECUTE (@LaRequette)
 
-- Archivage du fichier BAK avec le format Windows CAB
SET @LaRequette = 'master.dbo.xp_cmdshell ''C:\WINDOWS\system32\makecab /V[1] /L '+ @Repertoire +' '+ @Repertoire + @name +'.BAK '+ @name +'_'+ @DateJour +'.cab'', NO_OUTPUT '
PRINT 'Requette : '+ @LaRequette
EXECUTE (@LaRequette)
 
-- Suppression du fichier BAK
SET @LaRequette = 'master.dbo.xp_cmdshell ''DEL '+ @Repertoire + @name +'.BAK'', NO_OUTPUT '
PRINT 'Requette : '+ @LaRequette
EXECUTE (@LaRequette)
END
 
PRINT ''
PRINT '-----------------------------------------------------'
-----------------------------------------------------------------
-- Avance le curseur d'un Cran
FETCH NEXT FROM TESTCURSEUR
INTO @name, @STATUS
END
-----------------------------------------------------------------
-- Fermeture du Curseur
CLOSE TESTCURSEUR
-- Libération de la mémoire prise par le Curseur
DEALLOCATE TESTCURSEUR
-----------------------------------------------------------------
GO

Etape 3 – Procédure stockée SQL – SP_HelpRevLogin

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_help_revlogin]    Script Date: 13/05/2015 16:06:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[sp_help_revlogin] @login_name sysname = NULL AS
DECLARE @name    sysname
DECLARE @xstatus INT
DECLARE @binpwd  varbinary (256)
DECLARE @txtpwd  sysname
DECLARE @tmpstr  VARCHAR (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string VARCHAR(256)
 
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR 
    SELECT sid, name, xstatus, password FROM master..sysxlogins 
    WHERE srvid IS NULL AND name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR 
    SELECT sid, name, xstatus, password FROM master..sysxlogins 
    WHERE srvid IS NULL AND name = @login_name
OPEN login_curs 
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs 
  DEALLOCATE login_curs 
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script ' 
PRINT @tmpstr
SET @tmpstr = '** Generated ' 
  + CONVERT (VARCHAR, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr 
    IF (@xstatus & 4) = 4
    BEGIN -- NT authenticated account/group
      IF (@xstatus & 1) = 1
      BEGIN -- NT login is denied access
        SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
        PRINT @tmpstr 
      END
      ELSE BEGIN -- NT login has access
        SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
        PRINT @tmpstr 
      END
    END
    ELSE BEGIN -- SQL Server authentication
      IF (@binpwd IS NOT NULL)
      BEGIN -- Non-null password
        EXEC sp_hexadecimal @binpwd, @txtpwd OUT
        IF (@xstatus & 2048) = 2048
          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
        ELSE
          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
        PRINT @tmpstr
             EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
          + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
      END
      ELSE BEGIN 
        -- Null password
             EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
          + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
      END
      IF (@xstatus & 2048) = 2048
        -- login upgraded from 6.5
        SET @tmpstr = @tmpstr + '''skip_encryption_old''' 
      ELSE 
        SET @tmpstr = @tmpstr + '''skip_encryption'''
      PRINT @tmpstr 
    END
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
  END
CLOSE login_curs 
DEALLOCATE login_curs 
RETURN 0
 
GO

Etape 4 – Procédure stockée SQL – SP_RESTORE_DB_FROM_BACKUP_FILE

USE master
GO
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[SP_RESTORE_DB_FROM_BACKUP_FILE]
@RepertoireSauvegarde AS VARCHAR(8000)
 
AS
 
-- Déclaration des Variables
DECLARE @counter INT
DECLARE @Maxbd INT
DECLARE @dbname sysname
DECLARE @bd sysname
DECLARE @Req VARCHAR(8000)
DECLARE @cmd NVARCHAR(500)
DECLARE @MaxbdNull INT
 
SET @cmd = 'dir /b '+@RepertoireSauvegarde+' | c:\windows\script\unix\sed -e "s/.BAK//" '
 
-- creation d'une table temporaire contenant la liste des fichier BAK
CREATE TABLE #dbname(line VARCHAR(2000))
INSERT INTO #dbname
EXEC xp_cmdshell @cmd
--SELECT line FROM #dbname WHERE line IS NOT NULL ORDER BY 1
 
SET @Maxbd = (SELECT COUNT(*) FROM #dbname)
PRINT 'nb ligne total :'+CAST(@Maxbd AS VARCHAR)
SET @MaxbdNull = ( SELECT COUNT(*) FROM #dbname WHERE line IS NOT NULL)
PRINT 'nb ligne sans NULL :'+CAST(@MaxbdNull AS VARCHAR)
 
SET @counter = 1
 
-- Preparation du Cursor pour la boucle
DECLARE TESTCURSEUR CURSOR
FOR SELECT line FROM #dbname WHERE line IS NOT NULL
 
OPEN TESTCURSEUR
FETCH NEXT FROM TESTCURSEUR INTO @bd
 
WHILE @counter <= @MaxbdNull
BEGIN
SET @Req = 'RESTORE DATABASE ['+@bd+'] FROM DISK = N'''+@RepertoireSauvegarde+'\'+@bd+'.BAK'' WITH REPLACE'
PRINT 'Commande '+CAST(@counter AS VARCHAR)+' : '+@Req
PRINT 'EXECUTE Requete'
EXECUTE (@Req)
FETCH NEXT FROM TESTCURSEUR INTO @bd
SET @counter = @counter +1
END
-- Fermeture du Curseur
CLOSE TESTCURSEUR
 
-- Libération de la mémoire prise par le Curseur
DEALLOCATE TESTCURSEUR
GO

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

*

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>