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 * * * * *
