[SQL Server] Générer les scripts de restauration d'une BD pour une instance SQL
-- =============================================
-- Author: Zouhaier KHARROUBI
-- Create date: 16/02/2019
-- Description: Générer les scripts de restauration d'une BD pour une instance SQL
-- =============================================
-- Add the parameters for the stored procedure here
DECLARE @Chemin_Backup ASVARCHAR(MAX)
, @Nom_Base ASVARCHAR(150)
, @Instance_SQL ASVARCHAR(150)
, @VerifierBackup ASBIT=0
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SETNOCOUNTON;
/* Exemples d'utilisations :
SELECT @Chemin_Backup='MaBase_1.bak'
,@Nom_Base='MaBase_1'
,@Instance_SQL='MonServeur\Instance1'
,@VerifierBackup= 0
SELECT @Chemin_Backup= '\\MesBackups\MaBase_1.bak'
,@Nom_Base='MaBase_1'
,@Instance_SQL='MonServeur\Instance1'
, @VerifierBackup=0
*/
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#Resultat]'))
BEGIN
DROPTABLE #Resultat
END
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#VERIFYONLY_Res]'))
BEGIN
DROPTABLE #VERIFYONLY_Res
END
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#FILELISTONLY_Res]'))
BEGIN
DROPTABLE #FILELISTONLY_Res
END
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#CheminBackup]'))
BEGIN
DROPTABLE #CheminBackup
END
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..#FileExist]'))
BEGIN
DROPTABLE #FileExist
END
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..#HEADERONLY_Res]'))
BEGIN
DROPTABLE #HEADERONLY_Res
END
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..#GroupeDisponibilite]'))
BEGIN
DROPTABLE #GroupeDisponibilite
END
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#Objets]'))
BEGIN
DROPTABLE #Objets
END
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#Journal]'))
BEGIN
DROPTABLE #Journal
END
CREATETABLE #Resultat
(
Num_Lig INTIDENTITY (1,1),
Type_Requete VARCHAR(150)NULL,
Requete VARCHAR(MAX)NULL
)
CREATETABLE #VERIFYONLY_Res
(
Type_Resultat VARCHAR(150),
[Messages] NVARCHAR(MAX)
)
CREATETABLE #CheminBackup
(
Type_Resultat VARCHAR(150)DEFAULT'XP_FILEEXIST',
NomServeur VARCHAR(100) NULL,
NomBase VARCHAR(100) NULL,
Chemin_Backup VARCHAR(1500)NULL,
FichierExiste BITDEFAULT 0
)
CREATETABLE #FILELISTONLY_Res
(
Type_Resultat VARCHAR(150)
,LogicalName nvarchar(128)
,PhysicalName nvarchar(260)
,[Type] char(1)
,FileGroupName nvarchar(128)
,Size numeric(20,0)
,MaxSize numeric(20,0),
FileId tinyint,
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),
DifferentialBaseGUIDuniqueidentifier,
IsReadOnly bit,
IsPresent bit,
TDEThumbprint varbinary(32)
)
CREATETABLE #HEADERONLY_Res
(
Type_Resultat VARCHAR(150)NULL,
BackupName nvarchar(128),
BackupDescription nvarchar(255),
BackupType smallint,
ExpirationDate datetime,
Compressed bit,
Position smallint,
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,
UnicodeComparisonStyleint,
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,
Containment tinyint
)
CREATETABLE #FileExist
(
File_Exists INT NULL,
File_is_a_Directory INT NULL,
Parent_Directory_Exists INT NULL
)
CREATETABLE #GroupeDisponibilite
(
Type_Resultat VARCHAR(150) NULLDEFAULT'GROUPE DISPONIBILITE',
Lien_Serveur nvarchar(150) NULL,
AvailabilityGroupName nvarchar(128)NULL,
PrimaryReplicaServerName nvarchar(128)NULL,
LocalReplicaRole tinyint NULL,
DatabaseName nvarchar(128)NULL,
SynchronizationState tinyint NULL,
IsSuspended bit NULL,
IsJoined bit NULL
)
CREATETABLE #Objets
(
Nom_Objet VARCHAR(150)NULL
)
CREATENONCLUSTEREDINDEX [IDX_#Objets_Nom_Objet] ON #Objets
(
Nom_Objet ASC
)
CREATETABLE #Journal
(
Num_Lig INTIDENTITY (1,1),
Requete VARCHAR(MAX)NULL,
Message_Erreur VARCHAR(MAX)NULL
)
-- Insert statements for procedure here
DECLARE @Requete ASNVARCHAR(MAX)
,@LocalHot ASVARCHAR(50)
,@ListFile_Move ASVARCHAR(MAX)=''
,@RequeteRestore ASVARCHAR(MAX)
,@Type_Requete ASVARCHAR(150)
,@Source_Backup ASVARCHAR(35)
,@FichierExiste ASBIT= 0
,@PrimaryReplicaServerName ASNVARCHAR(128)
SELECT @Source_Backup =CASEWHEN @Chemin_Backup LIKE'%FULL%'THEN'BACKUP_FULL'
WHEN @Chemin_Backup LIKE'%DIFF%' THEN'BACKUP_DIFF'
WHEN @Chemin_Backup LIKE'%LOG%' THEN'BACKUP_LOG'
ELSE'BACKUP_INCONNU'END
SELECT @Type_Requete ='RESTORE_'+ @Source_Backup
SELECT @LocalHot =@@SERVERNAME
-- Récupérer le groupe de disponibilité de la base
BEGINTRY
SELECT @Requete =N'SELECT TOP 1 [name] AS Nom_Objet FROM '+QUOTENAME(@Instance_SQL)+N'.[master].[sys].[all_objects] WHERE [name] LIKE ''availability%'''
INSERTINTO #Objets(Nom_Objet)
EXECUTEsp_executesql@Requete
IFEXISTS(SELECT Nom_Objet FROM #Objets)
BEGIN
SELECT @Requete =CASEWHEN @LocalHot<>@Instance_SQL THENN'SELECT * FROM OPENQUERY('+QUOTENAME(@Instance_SQL)+','''ELSE''END
SELECT @Requete = @Requete +'SELECT '''''+QUOTENAME(@Instance_SQL)+''''' AS Lien_Serveur,T1.name AS [AvailabilityGroupName],
ISNULL(T2.primary_replica, '''''''') AS [PrimaryReplicaServerName],
ISNULL(T4.role, 3) AS [LocalReplicaRole], T5.database_name AS [DatabaseName],
ISNULL(T6.synchronization_state,0) AS [SynchronizationState],
ISNULL(T6.is_suspended, 0) AS [IsSuspended],
ISNULL(T5.is_database_joined, 0) AS [IsJoined]
FROM master.sys.availability_groups T1
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states T2 ON T1.group_id = T2.group_id
INNER JOIN master.sys.availability_replicas T3 ON T1.group_id = T3.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states T4 ON T3.replica_id = T4.replica_id AND T4.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states T5 ON T4.replica_id = T5.replica_id AND T5.database_name='''''+ @Nom_Base +''''''
+' LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states T6 ON T5.replica_id = T6.replica_id AND T5.group_database_id = T6.group_database_id'
SELECT @Requete =CASEWHEN @LocalHot<>@Instance_SQL THEN @Requete +N''')'ELSEREPLACE(@Requete,'''''','''')END
INSERT #GroupeDisponibilite(Lien_Serveur,AvailabilityGroupName,PrimaryReplicaServerName,
LocalReplicaRole,DatabaseName,SynchronizationState,IsSuspended,IsJoined)
EXEC (@Requete)
SELECT @PrimaryReplicaServerName =ISNULL(PrimaryReplicaServerName,@Instance_SQL)FROM #GroupeDisponibilite
END
ENDTRY
BEGINCATCH
INSERTINTO #Journal(Requete,Message_Erreur)
VALUES ('Vérifier groupe disponibilité de la base',ERROR_MESSAGE())
ENDCATCH
--Récupérer le chemin entier du backup s'il n'a pas été renseigné dans le paramètre @Chemin_Backup
BEGINTRY
IF @Chemin_Backup NOTLIKE'%\%'
BEGIN
SELECT @Requete =N' SELECT NomServeur,'''+ISNULL(CAST(@Nom_Base ASVARCHAR(150)),'ALL')+''' AS NomBase,Chemin_Backup FROM OPENQUERY('+QUOTENAME(@PrimaryReplicaServerName)+N',''SELECT TOP 1 @@SERVERNAME AS NomServeur,physical_device_name AS Chemin_Backup FROM [msdb].[dbo].[backupmediafamily]'
SELECT @Requete = @Requete +N' WHERE physical_device_name LIKE ''''%'+CAST(@Chemin_Backup ASVARCHAR(150))+'%'''''
SELECT @Requete = @Requete +''')'
INSERTINTO #CheminBackup(NomServeur,NomBase,Chemin_Backup)
EXECUTEsp_executesql@Requete
SELECT @Chemin_Backup = Chemin_Backup FROM #CheminBackup
END
ELSE
BEGIN
INSERTINTO #CheminBackup(NomServeur,NomBase,Chemin_Backup)
VALUES (@Instance_SQL,@Nom_Base,@Chemin_Backup)
END
ENDTRY
BEGINCATCH
INSERTINTO #Journal(Requete,Message_Erreur)
VALUES ('Récupérer le chemin entier du backup',ERROR_MESSAGE())
ENDCATCH
--Vérifier l'existance du fichier
BEGINTRY
SELECT @Requete ='master.[sys].[xp_fileexist] '''+ @Chemin_Backup +''''
INSERT #FileExist(File_Exists,File_is_a_Directory,Parent_Directory_Exists)
EXECUTEsp_executesql@Requete
IFEXISTS(SELECT File_Exists FROM #FileExist WHERE File_Exists=1)
BEGIN
SELECT @FichierExiste=1
END
UPDATE #CheminBackup
SET
FichierExiste = @FichierExiste
ENDTRY
BEGINCATCH
INSERTINTO #Journal(Requete,Message_Erreur)
VALUES ('Vérifier l''existance du fichier',ERROR_MESSAGE())
ENDCATCH
--Vérifier la validité de la sauvegarde SQL Server
IF @FichierExiste=1
BEGIN
IF @VerifierBackup=1
BEGINTRY
SELECT @Requete ='RESTORE VERIFYONLY FROM DISK = N'''+ @Chemin_Backup +''''
--INSERT #VERIFYONLY_Res([Messages])
EXEC (@Requete)
UPDATE #VERIFYONLY_Res
SET
Type_Resultat ='RESTORE_VERIFYONLY_'+ @Source_Backup
ENDTRY
BEGINCATCH
INSERTINTO #Journal(Requete,Message_Erreur)
VALUES ('RESTORE VERIFYONLY',ERROR_MESSAGE())
ENDCATCH
-- Récupérer les informations du fichier de backup
BEGINTRY
SELECT @Requete ='RESTORE HEADERONLY FROM DISK = N'''+ @Chemin_Backup +''''
INSERT #HEADERONLY_Res(BackupName,BackupDescription,BackupType, ExpirationDate,Compressed,Position,DeviceType,UserName,
ServerName, DatabaseName,DatabaseVersion,DatabaseCreationDate,BackupSize,FirstLSN,LastLSN,CheckpointLSN,
DatabaseBackupLSN,BackupStartDate,BackupFinishDate,SortOrder,[CodePage],UnicodeLocaleId,UnicodeComparisonStyle,
CompatibilityLevel,SoftwareVendorId,SoftwareVersionMajor,SoftwareVersionMinor,SoftwareVersionBuild,MachineName,
Flags,BindingId,RecoveryForkId,Collation,FamilyGUID,HasBulkLoggedData,IsSnapshot,IsReadOnly,IsSingleUser,HasBackupChecksums,
IsDamaged,BeginsLogChain,HasIncompleteMetaData,IsForceOffline,IsCopyOnly,FirstRecoveryForkID,ForkPointLSN,
RecoveryModel,DifferentialBaseLSN,DifferentialBaseGUID,BackupTypeDescription,BackupSetGUID,CompressedBackupSize,
Containment)
EXEC (@Requete)
UPDATE #HEADERONLY_Res
SET
Type_Resultat ='RESTORE_HEADERONLY_'+ @Source_Backup
ENDTRY
BEGINCATCH
INSERTINTO #Journal(Requete,Message_Erreur)
VALUES ('RESTORE HEADERONLY',ERROR_MESSAGE())
ENDCATCH
--afficher la liste des fichiers journaux et des fichiers de base de données contenus dans le jeu de sauvegarde SQL Server.
BEGINTRY
SELECT @Requete ='RESTORE FILELISTONLY FROM DISK = N'''+ @Chemin_Backup +''''
INSERT #FILELISTONLY_Res(LogicalName,PhysicalName,[Type],FileGroupName,Size,MaxSize,FileId ,CreateLSN ,
DropLSN ,UniqueID,ReadOnlyLSN,ReadWriteLSN ,BackupSizeInBytes,SourceBlockSize,
FileGroupId,LogGroupGUID,DifferentialBaseLSN,DifferentialBaseGUID,IsReadOnly,
IsPresent,TDEThumbprint)
EXEC (@Requete)
UPDATE #FILELISTONLY_Res
SET
Type_Resultat ='RESTORE_FILELISTONLY_'+ @Source_Backup
ENDTRY
BEGINCATCH
INSERTINTO #Journal(Requete,Message_Erreur)
VALUES ('RESTORE FILELISTONLY',ERROR_MESSAGE())
ENDCATCH
--Début Construction de la requete RESTORE
BEGINTRY
SELECT @RequeteRestore ='-- Début '+ @Type_Requete +' de la base '+QUOTENAME(@Nom_Base)+' sur l''instance SQL Server '+QUOTENAME(@Instance_SQL)
INSERTINTO #Resultat(Type_Requete,Requete)VALUES (@Type_Requete,@RequeteRestore)
SELECT @RequeteRestore ='RESTORE DATABASE '+QUOTENAME(@Nom_Base)+' FROM DISK = N'''+ @Chemin_Backup +''''
INSERTINTO #Resultat(Type_Requete,Requete)VALUES (@Type_Requete,@RequeteRestore)
INSERTINTO #Resultat(Type_Requete,Requete)VALUES (@Type_Requete,'WITH')
SELECT @Requete =CASEWHEN @LocalHot<>@Instance_SQL THENN'SELECT '''+ @Type_Requete +''' AS Type_Requete,ListFile_Move FROM OPENQUERY('+QUOTENAME(@Instance_SQL)+','''ELSE'SELECT '''+ @Type_Requete +''' AS Type_Requete,ListFile_Move FROM ('END
SELECT @Requete= @Requete +'SELECT ''''MOVE '''''''''''' + name + '''''''''''' TO '''''''''''' + physical_name + '''''''''''','''' AS ListFile_Move FROM sys.master_files WHERE database_id=DB_ID('''''+ @Nom_Base +''''')'
SELECT @Requete =CASEWHEN @LocalHot<>@Instance_SQL THEN @Requete +N''')'ELSEREPLACE(@Requete,'''''','''')+') T1'END
INSERTINTO #Resultat(Type_Requete,Requete)
EXECUTEsp_executesql@Requete
INSERTINTO #Resultat(Type_Requete,Requete)VALUES (@Type_Requete,'NORECOVERY')
INSERTINTO #Resultat(Type_Requete,Requete)VALUES (@Type_Requete,'GO')
SELECT @RequeteRestore ='-- Fin '+ @Type_Requete +' de la base '+QUOTENAME(@Nom_Base)+' sur l''instance SQL Server '+QUOTENAME(@Instance_SQL)
INSERTINTO #Resultat(Type_Requete,Requete)VALUES (@Type_Requete,@RequeteRestore)
ENDTRY
BEGINCATCH
INSERTINTO #Journal(Requete,Message_Erreur)
VALUES ('RESTORE DATABASE CONSTRUCTION REQUETE',ERROR_MESSAGE())
ENDCATCH
--Fin Construction de la requete RESTORE
END
SELECT*FROM #Journal
SELECT*FROM #CheminBackup
SELECT*FROM #HEADERONLY_Res
SELECT*FROM #FILELISTONLY_Res
SELECT*FROM #GroupeDisponibilite
SELECT*FROM #Resultat
DROPTABLE #CheminBackup
DROPTABLE #HEADERONLY_Res
DROPTABLE #FILELISTONLY_Res
DROPTABLE #VERIFYONLY_Res
DROPTABLE #FileExist
DROPTABLE #GroupeDisponibilite
DROPTABLE #Resultat
DROPTABLE #Journal
END
GO