Procédure de restauration automatique à partir d’un métafichier de sauvegarde

Voici une procédure permettant de restaurer à partir d’un méta fichier de sauvegarde, c’est à dire un fichier contenant plusieurs sauvegardes SQL Server empilées. Le code est donné « as is ».


CREATE PROCEDURE [dbo].[SP_RESTORE] @BACKUP_FILE     NVARCHAR(256),  -- emplacement du fichier contenant la sauvegarde
                                @STORE_PATH      NVARCHAR(256),  -- emplacement de destination des fichiers pour la restauration
                                @DB_NAME         NVARCHAR(128),  -- nom original de la basee
                                @DB_NEW_NAME     NVARCHAR(128),  -- nouveau nom (facultatif)
                                @FILE_RENAME_TAG BIT = 0         -- renommage par ajout d'un TAG GUID des fichiers (facultatif)  
AS  
BEGIN  
 
SET NOCOUNT ON;
 
IF @BACKUP_FILE IS NULL OR @STORE_PATH IS NULL OR @DB_NAME IS NULL
   RETURN;  
 
-- validité SQL Server 2008  
 
DECLARE @I INT, @SQL NVARCHAR(max);  
 
-- table des "header" (sauvegardes contenues dans le fichier)  
DECLARE @H TABLE (  
BackupName              nvarchar(128) ,    
BackupDescription       nvarchar(255) ,  
BackupType              smallint ,  
ExpirationDate          datetime ,  
Compressed              bit,  
Position                int,  
DeviceType              tinyint ,  
UserName                nvarchar(128) ,  
ServerName              nvarchar(128),    
DatabaseName            nvarchar(128) ,  
DatabaseVersion         int ,  
DatabaseCreationDate    datetime ,  
BackupSize              numeric(20,0) ,  
FirstLSN                numeric(25,0) ,  
LastLSN                 numeric(25,0) ,  
CheckpointLSN           numeric(25,0) ,  
DatabaseBackupLSN       numeric(25,0) ,  
BackupStartDate         datetime,  
BackupFinishDate        datetime,    
SortOrder               smallint ,  
CodePage                smallint ,  
UnicodeLocaleId         int,    
UnicodeComparisonStyle  int ,  
CompatibilityLevel      tinyint ,  
SoftwareVendorId        int ,  
SoftwareVersionMajor    int ,  
SoftwareVersionMinor    int ,  
SoftwareVersionBuild    int ,  
MachineName             nvarchar(128) ,  
Flags                   int ,  
BindingID               uniqueidentifier ,  
RecoveryForkID          uniqueidentifier ,  
Collation               nvarchar(128) ,  
FamilyGUID              uniqueidentifier ,  
HasBulkLoggedData       bit ,  
IsSnapshot              bit ,  
IsReadOnly              bit ,  
IsSingleUser            bit,    
HasBackupChecksums      bit ,  
IsDamaged               bit ,  
BeginsLogChain          bit ,  
HasIncompleteMetaData   bit ,  
IsForceOffline          bit ,  
IsCopyOnly              bit ,  
FirstRecoveryForkID     uniqueidentifier ,  
ForkPointLSN            numeric(25,0),  
RecoveryModel           nvarchar(60) ,  
DifferentialBaseLSN     numeric(25,0),  
DifferentialBaseGUID    uniqueidentifier ,  
BackupTypeDescription   nvarchar(60) ,  
BackupSetGUID           uniqueidentifier ,  
CompressedBackupSize    bigint);  
 
-- table des fichiers de la sauvegarde  
DECLARE @F TABLE  
(LogicalName            nvarchar(128),  
PhysicalName            nvarchar(260) ,  
Type                    char(1) ,  
FileGroupName           nvarchar(128) ,  
Size                    numeric(20,0) ,  
MaxSize                 numeric(20,0),    
FileID                  bigint ,  
CreateLSN               numeric(25,0)  ,  
DropLSN                 numeric(25,0),  
UniqueID                uniqueidentifier ,  
ReadOnlyLSN             numeric(25,0),    
ReadWriteLSN            numeric(25,0),  
BackupSizeInBytes       bigint ,  
SourceBlockSize         int ,  
FileGroupID             int ,  
LogGroupGUID            uniqueidentifier ,  
DifferentialBaseLSN     numeric(25,0) ,  
DifferentialBaseGUID    uniqueidentifier ,  
IsReadOnly              bit ,  
IsPresent               bit,  
TDEThumbprint           varbinary(32))  
 
-- insertion de l'ensemble des sauvegardes dans la table    
SET @I = 1;  
SET @SQL = 'RESTORE HEADERONLY FROM DISK = ''' + @BACKUP_FILE    
         + ''' WITH FILE = ' + CAST(@I AS NVARCHAR(16));  
INSERT INTO @H EXEC (@SQL);  
IF @@ERROR <> 0 RETURN;  
 
BEGIN TRY  
WHILE 1=1  
BEGIN  
   SET @I = @I + 1;  
   SET @SQL = 'RESTORE HEADERONLY FROM DISK = ''' + @BACKUP_FILE    
            + ''' WITH FILE = ' + CAST(@I AS NVARCHAR(16));  
   INSERT INTO @H EXEC (@SQL);  
END;      
END TRY  
BEGIN CATCH  
END CATCH;  
 
SET @I = NULL;  
 
-- recherche de la sauvegarde de la base  
SELECT @I = Position FROM @H WHERE DatabaseName = @DB_NAME;  
 
IF @I IS NULL  
BEGIN  
   RAISERROR('Il n''y a pas de base de nom "%s" dans le fichier de sauvegarde.', 16, 1);  
   RETURN;  
END      
 
-- insertion des fichiers de la base à restaurer  
SET @SQL = 'RESTORE FILELISTONLY FROM DISK = ''' + @BACKUP_FILE    
         + ''' WITH FILE = ' + CAST(@I AS NVARCHAR(16));  
INSERT INTO @F EXEC (@SQL);  
IF @@ERROR <> 0 RETURN;  
 
 
-- suppression des path des fichiers physiques  
UPDATE @F  
SET    PhysicalName = REVERSE(SUBSTRING(REVERSE(PhysicalName),    
                              1,    
                              CHARINDEX('\', REVERSE(PhysicalName) )  -1));  
 
SET @I = 0;  
-- renommage des fichiers physiques si doublons (noms identiques dans des path différents)  
WITH T AS (SELECT LogicalName,    
                  RANK() OVER(PARTITION BY PhysicalName    
                              ORDER BY LogicalName) - 1 AS N  
           FROM   @F)  
UPDATE F  
SET    F.PhysicalName = F.PhysicalName + CAST(N AS NVARCHAR(16))  
FROM   @F AS F  
       INNER JOIN T  
             ON F.LogicalName = T.LogicalName  
WHERE  N > 0;  
SET @I = @@ROWCOUNT;
 
IF @FILE_RENAME_TAG = 1
   UPDATE @F  
   SET    PhysicalName = 'F_' +  REPLACE(CAST(NEWID() AS NVARCHAR(38)), '-', '_') + '_' + PhysicalName;  
   
-- restauration  
-- test si le @STORE_PATH contient un \ final, sinon le rajoute  
IF SUBSTRING(REVERSE(@STORE_PATH), 1, 1) <> '\'  
   SET @STORE_PATH = @STORE_PATH +'\';  
 
SET @SQL = 'RESTORE DATABASE [' + COALESCE(@DB_NEW_NAME, @DB_NAME)    
         + '] FROM DISK = N''' + @BACKUP_FILE +''' WITH '  
SELECT @SQL = @SQL + ' MOVE N''' + LogicalName + ''' TO '''    
            + @STORE_PATH + PhysicalName +''', '  
FROM   @F  
 
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) +';';  
 
EXEC (@SQL)  
 
IF @I > 0
   RAISERROR('Avertissement : certains fichiers (%i) ont du être renommés pour éviter des doublons de noms', 0, 1, @I)
 
END

--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *

MVP Microsoft SQL Server

Laisser un commentaire