dimanche 12 février 2012

[SMO][PowerShell] Automatiser la restauration des bases de données avec fichiers de données multiples

Bien sûr, il est tout à fait possible d’utiliser Transact-SQL pour restaurer une base de données, néanmoins lorsque vous souhaiterez restaurer une base qui dispose d’un nombre pluriel et variable de fichiers de données sur un environnement différent que celui où le backup a été réalisé, la restauration risque d’être un peu laborieuse du fait de devoir réallouer les fichiers vers un emplacement existant du serveur cible.

Comme je vous le disais, l’automatisation en Transact-SQL reste et Christophe Laporte le prouve ici :

http://conseilit.wordpress.com/2009/06/24/la-galere-des-restore-database-with-move-%E2%80%A6/

Cependant, cette méthode oblige à avoir accès sur le serveur où sont déployés les bases à backuper et à utiliser un curseur pour générer le scripts de restore.

En mission chez un client, j’ai dû trouver une solution pour répondre au besoin d’ « automatiser la restauration de bases de données sur un environnement de DEV à partir de fichiers .bak sans avoir accès au serveur de PROD. » En effet, on cherche à cloisonner l’environnement de PROD de celui de développement donc pas d’accès en PROD à partir de la DEV.

Pour répondre à ce besoin, j’ai fait le choix d’utiliser SMO via un script PowerShell en exécutant le fichier ps1 partir d'une step PowerShell de l’agent SQL.

Le gros avantage que j’ai trouvé à utiliser SMO est de pouvoir lire le contenu d’un fichier de backup et de pouvoir réallouer tous les fichiers avant restauration, grâce aux classes Restore et RelocateFile. Ainsi, si la base à restaurer évolue, que de nouveaux fichiers sont ajoutés ou supprimés, du fait de l'allocation dynamique la base sera restaurer sans devoir modifier le script.

Attention, par défaut, le processus de restore généré par le script PowerShell est considéré comme processus distant et le Timeout par défaut de 600 secondes (10min) s'applique. Ce qui amène les requêtes de restauration à être interrompu et à laisser les bases de données dans l'état Restoring.

La parade est de forcer la propriété StatementTimeout à 0 grâce à la commande.$server.ConnectionContext.set_StatementTimeout(0). Le script ci-dessous applique ce paramètre.

param(
        ## Serveur où l'on souhaite restaurer le backup
        [string]$ServerDestination = $(throw "-ServerDestination est requis")
       ## La restauration se fera à partir de cette emplacement
       ,[string]$BackupLocalPath = $(throw "-BackupLocalPath est requis")
       ## Listes des base à restaurer
       ,[array]$DBtoRestore = $(throw "-DBtoRestore est requis")
)

##Chargement des assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
  
## Fonction de restauration
## Prends en charge les fichiers multiple et les relocalise en fonction du DataPath et LogPath par défaut du serveur

function Restore([string]$srv,[string]$bck)
{

try{
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $srv
$backupDevice = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem $bck, "File"
$smoRestore = New-Object Microsoft.SqlServer.Management.Smo.Restore

## permet d'outrepasser le timeout du serveur afin de pouvoir restaurer les grosses bases
$server.ConnectionContext.set_StatementTimeout(0)

## Paramétrage du restore
$smoRestore.NoRecovery = $false;
$smoRestore.ReplaceDatabase = $true;
$smoRestore.Action = "Database"

$smoRestore.Devices.Add($backupDevice)

#Gestion de la relocalisation des fichiers
$filelist=$smoRestore.ReadFileList($server)
foreach( $file in $filelist.get_DefaultView())
{
       if($file.PhysicalName -like "*mdf")
       {
             $physicalpath=$server.DefaultFile + "\" + $file.LogicalName + ".mdf"
             $relocateFile = new-object Microsoft.SqlServer.Management.Smo.RelocateFile $file.LogicalName, $physicalpath
             $smoRestore.RelocateFiles.Add($relocateFile)
       }
       elseif($file.Type -like "D")
       {
             $physicalpath=$server.DefaultFile + "\" + $file.LogicalName + ".ndf"
             $relocateFile = new-object Microsoft.SqlServer.Management.Smo.RelocateFile $file.LogicalName, $physicalpath
             $smoRestore.RelocateFiles.Add($relocateFile)
       }
       else{
             $physicalpath=$server.DefaultLog + "\" + $file.LogicalName + ".ldf"
             $relocateFile = new-object Microsoft.SqlServer.Management.Smo.RelocateFile $file.LogicalName, $physicalpath
             $smoRestore.RelocateFiles.Add($relocateFile)
       }
}

# On lit le contenu du backup
$smoRestoreDetails = $smoRestore.ReadBackupHeader($server)

#Afin de donner à la base que l'on va restaurer le même nom qu'elle avait sur le serveur source
$smoRestore.Database =$smoRestoreDetails.Rows[0]["DatabaseName"]
  
#Commande de restauration de la base
$smoRestore.SqlRestore($server)

"La base " + $smoRestoreDetails.Rows[0]["DatabaseName"] + " a été restaurée avec succès"
}
catch {
           $ex = $_.Exception
           Write-Output $ex.message
           $ex = $ex.InnerException
           while ($ex.InnerException)
           {
               Write-Output $ex.InnerException.message
               $ex = $ex.InnerException
           };
           continue
       }
}

$bckpath = New-Object System.IO.DirectoryInfo $BackupLocalPath

foreach($file in $bckpath.GetFiles())
{
       $exist=$false
       $bck=""
       foreach($value in $DBtoRestore)
       {
             if($file.Name -eq $value)
             {
                    $exist=$true
                    $bck=$file.FullName
                    break
             }
       }
       if($exist)
       {     
             Restore -srv $ServerDestination -bck $bck
       }
}

Aucun commentaire:

Publier un commentaire