Voici une petite procédure de restauration qui recherche les bons fichiers dans un fichier de sauvegarde pouvant contenir plusieurs sauvegardes et restaure la base à un emplacement précis en s’occupant de renommer les éventuels fichiers dont les noms physique pourrait devenir des doublons.
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
GO
Exemple d’utilisation :
EXEC dbo.SP_RESTORE @BACKUP_FILE = 'D:\! SARL SQLspot\! client\PresidenceRepublique\SQLProfiler\Traces20110110.bak',
@STORE_PATH = 'D:\! SARL SQLspot\! client\PresidenceRepublique\SQLProfiler\DATABASES\',
@DB_NAME = 'DB_SQLPRO_TRACES',
@DB_NEW_NAME = NULL,
@FILE_RENAME_TAG = 1;
--------
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 * * * * *
Parce que nous ne savons pas combien de sauvegardes figurent dans le fichier. C’est pourquoi on tente avec 1, 2, 3… et on arrete quand ça plante. C’est le BEGIN TRY / BEGIN CATCH qui gère cela !
Bonjour
Pourquoi « WHILE 1=1″ ?
Merci d’avance