Restauration de sauvegarde automatique entre instances

Les scripts suivants permettent de restaurer automatiquement des sauvegardes de bases de données entre serveurs, sans recopier les fichiers de sauvegardes d’un serveur à l’autre : ils sont particulièrement utiles en cas de migration ou de synchronisation entre instances. En outre, ils permettent des restaurations incrémentielles successives basées sur les sauvegardes de journal.

Le principe est le suivant :

Sur l’instance source, on crée une nouvelle vue dans msdb pour récupérer l’historique et la séquence de sauvegardes effectuées
Sur le serveur source, on partage le répertoire qui contient les sauvegardes et on donne les permissions de lecture au compte de service de l’instance de destination.
Sur l’instance de destination, on crée un serveur lié qui pointe vers l’instance source
Sur l’instance de destination, on crée une base de données de travail qui contient les fonctions et procédures stockées de gestion, ainsi qu’une table qui liste les bases à restaurer. On renseigne dans la table la liste des bases à restaurer, on peut aussi préciser un nouveau nom de base pour la restauration.
Sur l’instance de destination, on exécute la procédure stockée. Elle effectue automatiquement la restauration des bases que l’on a configuré : elle va directement chercher l’historique (à travers le serveur lié) et lire les fichiers de sauvegardes sur le serveur source (à travers le partage). Entre autres paramètres, un des paramètres de la procédure vous permet de configurer s’il s’agit d’une initialisation ou d’une synchronisation incrémentale par restauration successive de nouvelles sauvegardes de journal.

Vue à créer dans la base msdb de l’instance source :

/*-------------------------------------------------------------------
[SCRIPT] view msdb.V_LastDiskBackupSequence
[DATABASE] msdb
[DESCRIPTION] Get the last sequence of disk database backups (Full,Diff,Log)
			  with restore commands	generation
[AUTHOR] DATAFLY - Arian Papillon
[USAGE] SELECT * FROM msdb.dbo.V_LastDiskBackupSequence 
        WHERE database_name = 'MyDB'
        ORDER BY database_name ASC, backup_sequence;
-------------------------------------------------------------------*/
CREATE VIEW [dbo].[V_LastDiskBackupSequence] AS
-- Last Full Backup
SELECT   ROW_NUMBER() OVER ( PARTITION BY database_name
                             ORDER BY backup_finish_date ) AS backup_sequence
       , database_name
       , backup_set_id
       , backup_set_uuid
       , media_set_id
       , backup_start_date
       , backup_finish_date
       , duration
       , type
       , name
       , compressed_backup_size
       , physical_device_name
       , position
       , CASE WHEN ROW_NUMBER() OVER ( PARTITION BY database_name
                                       ORDER BY backup_finish_date DESC ) = 1 THEN
                  REPLACE(restorecmd, 'NORECOVERY', 'RECOVERY')
              ELSE restorecmd
         END AS restorecmd
FROM     (   SELECT database_name
                  , backup_set_id
                  , backup_set_uuid
                  , media_set_id
                  , backup_start_date
                  , backup_finish_date
                  , duration
                  , type
                  , name
                  , compressed_backup_size
                  , physical_device_name
                  , position
                  , 'RESTORE DATABASE [' + FB.database_name + '] FROM DISK='''
                    + FB.physical_device_name + ''' WITH FILE = '
                    + CAST(position AS VARCHAR(3)) + ', NORECOVERY;' AS restorecmd
             FROM   (   SELECT bk.database_name
                             , bk.backup_set_id
                             , bk.backup_set_uuid
                             , bk.media_set_id
                             , bk.backup_start_date
                             , bk.backup_finish_date
                             , DATEDIFF(
                                   SECOND
                                 , bk.backup_start_date
                                 , bk.backup_finish_date) AS duration
                             , bk.type
                             , bk.name
                             , bk.compressed_backup_size
                             , bm.physical_device_name
                             , bk.position
                             , ROW_NUMBER() OVER ( PARTITION BY bk.database_name
                                                   ORDER BY bk.backup_finish_date DESC ) AS RowNumber
                        FROM   msdb..backupset bk
                               JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id
                        WHERE  bk.type = 'D'
                               AND DB_ID(bk.database_name) IS NOT NULL -- test d'existence de la base
                               AND bm.device_type IN (2,102) -- pour fichiers disque
             ) AS FB
             WHERE  RowNumber = 1
             UNION ALL
             -- Last Incremental Backup (after last full backup)
             SELECT IBA.database_name
                  , IBA.backup_set_id
                  , IBA.backup_set_uuid
                  , IBA.media_set_id
                  , IBA.backup_start_date
                  , IBA.backup_finish_date
                  , IBA.duration
                  , IBA.type
                  , IBA.name
                  , IBA.compressed_backup_size
                  , IBA.physical_device_name
                  , IBA.position
                  , 'RESTORE DATABASE [' + IBA.database_name + '] FROM DISK='''
                    + IBA.physical_device_name + ''' WITH FILE = '
                    + CAST(position AS VARCHAR(3)) + ', NORECOVERY;' AS restorecmd
             FROM   (   SELECT bk.database_name
                             , bk.backup_set_id
                             , bk.backup_set_uuid
                             , bk.media_set_id
                             , bk.backup_start_date
                             , bk.backup_finish_date
                             , DATEDIFF(
                                   SECOND
                                 , bk.backup_start_date
                                 , bk.backup_finish_date) AS duration
                             , bk.type
                             , bk.name
                             , bk.compressed_backup_size
                             , bm.physical_device_name
                             , bk.position
                             , ROW_NUMBER() OVER ( PARTITION BY bk.database_name
                                                   ORDER BY bk.backup_finish_date DESC ) AS RowNumber
                        FROM   msdb..backupset bk
                               JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id
                        WHERE  bk.type = 'I'
                               AND DB_ID(bk.database_name) IS NOT NULL -- test if database exists
                               AND bm.device_type IN (2,102) -- disk files
             ) AS IBA
                    JOIN (   SELECT *
                             FROM   (   SELECT bk.database_name
                                             , bk.backup_finish_date
                                             , ROW_NUMBER() OVER ( PARTITION BY bk.database_name
                                                                   ORDER BY bk.backup_finish_date DESC ) AS RowNumber
                                        FROM   msdb..backupset bk
                                               JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id
                                        WHERE  bk.type = 'D'
                                               AND DB_ID(bk.database_name) IS NOT NULL -- test if database exists
                                               AND bm.device_type IN (2,102) -- disk files
                             ) AS FB
                             WHERE  RowNumber = 1 ) AS LFB ON IBA.database_name = LFB.database_name
                                                              AND IBA.backup_finish_date > LFB.backup_finish_date
             WHERE  IBA.RowNumber = 1
             UNION ALL

             -- Transaction logs backups after the last Full or Incremental Backup
             SELECT TLB.database_name
                  , TLB.backup_set_id
                  , TLB.backup_set_uuid
                  , TLB.media_set_id
                  , TLB.backup_start_date
                  , TLB.backup_finish_date
                  , TLB.duration
                  , TLB.type
                  , TLB.name
                  , TLB.compressed_backup_size
                  , TLB.physical_device_name
                  , TLB.position
                  , 'RESTORE LOG [' + TLB.database_name + '] FROM DISK='''
                    + TLB.physical_device_name + ''' WITH FILE = '
                    + CAST(position AS VARCHAR(3)) + ', NORECOVERY;' AS restorecmd
             FROM   (   SELECT database_name
                             , backup_set_id
                             , backup_set_uuid
                             , bk.media_set_id
                             , backup_start_date
                             , backup_finish_date
                             , DATEDIFF(
                                   SECOND
                                 , bk.backup_start_date
                                 , bk.backup_finish_date) AS duration
                             , type
                             , name
                             , compressed_backup_size
                             , physical_device_name
                             , bk.position
                        FROM   msdb..backupset bk
                               JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id
                        WHERE  bk.type = 'L'
                               AND DB_ID(bk.database_name) IS NOT NULL -- test if database exists
                               AND bm.device_type IN (2,102) -- disk files
             ) AS TLB
                    JOIN (
                             -- Last Database backup, Full or incremental (if any)  LFIB
                             SELECT database_name
                                  , backup_finish_date
                                  , type
                             FROM   (   SELECT bk.database_name
                                             , bk.backup_finish_date
                                             , bk.type
                                             , ROW_NUMBER() OVER ( PARTITION BY bk.database_name
                                                                   ORDER BY bk.backup_finish_date DESC ) AS RowNumber
                                        FROM   msdb..backupset bk
                                               JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id
                                        WHERE  bk.type IN ( 'D', 'I' )
                                               AND DB_ID(bk.database_name) IS NOT NULL -- test if database exists
                                               AND bm.device_type IN (2,102) -- disk files
                             ) AS FB
                             WHERE  RowNumber = 1 ) AS LFIB ON TLB.database_name = LFIB.database_name
                                                               AND TLB.backup_finish_date > LFIB.backup_finish_date ) seq
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

/*-------------------------------------------------------------------

[SCRIPT] view msdb.V_LastDiskBackupSequence

[DATABASE] msdb

[DESCRIPTION] Get the last sequence of disk database backups (Full,Diff,Log)

with restore commands generation

[AUTHOR] DATAFLY - Arian Papillon

[USAGE] SELECT * FROM msdb.dbo.V_LastDiskBackupSequence

WHERE database_name = 'MyDB'

ORDER BY database_name ASC, backup_sequence;

-------------------------------------------------------------------*/

CREATE VIEW [dbo].[V_LastDiskBackupSequence] AS

-- Last Full Backup

SELECT ROW_NUMBER() OVER ( PARTITION BY database_name

ORDER BY backup_finish_date ) AS backup_sequence

, database_name

, backup_set_id

, backup_set_uuid

, media_set_id

, backup_start_date

, backup_finish_date

, duration

, type

, name

, compressed_backup_size

, physical_device_name

, position

, CASE WHEN ROW_NUMBER() OVER ( PARTITION BY database_name

ORDER BY backup_finish_date DESC ) = 1 THEN

REPLACE(restorecmd, 'NORECOVERY', 'RECOVERY')

ELSE restorecmd

END AS restorecmd

FROM ( SELECT database_name

, backup_set_id

, backup_set_uuid

, media_set_id

, backup_start_date

, backup_finish_date

, duration

, type

, name

, compressed_backup_size

, physical_device_name

, position

, 'RESTORE DATABASE [' + FB.database_name + '] FROM DISK='''

+ FB.physical_device_name + ''' WITH FILE = '

+ CAST(position AS VARCHAR(3)) + ', NORECOVERY;' AS restorecmd

FROM ( SELECT bk.database_name

, bk.backup_set_id

, bk.backup_set_uuid

, bk.media_set_id

, bk.backup_start_date

, bk.backup_finish_date

, DATEDIFF(

SECOND

, bk.backup_start_date

, bk.backup_finish_date) AS duration

, bk.type

, bk.name

, bk.compressed_backup_size

, bm.physical_device_name

, bk.position

, ROW_NUMBER() OVER ( PARTITION BY bk.database_name

ORDER BY bk.backup_finish_date DESC ) AS RowNumber

FROM msdb..backupset bk

JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id

WHERE bk.type = 'D'

AND DB_ID(bk.database_name) IS NOT NULL -- test d'existence de la base

AND bm.device_type IN (2,102) -- pour fichiers disque

) AS FB

WHERE RowNumber = 1

UNION ALL

-- Last Incremental Backup (after last full backup)

SELECT IBA.database_name

, IBA.backup_set_id

, IBA.backup_set_uuid

, IBA.media_set_id

, IBA.backup_start_date

, IBA.backup_finish_date

, IBA.duration

, IBA.type

, IBA.name

, IBA.compressed_backup_size

, IBA.physical_device_name

, IBA.position

, 'RESTORE DATABASE [' + IBA.database_name + '] FROM DISK='''

+ IBA.physical_device_name + ''' WITH FILE = '

+ CAST(position AS VARCHAR(3)) + ', NORECOVERY;' AS restorecmd

FROM ( SELECT bk.database_name

, bk.backup_set_id

, bk.backup_set_uuid

, bk.media_set_id

, bk.backup_start_date

, bk.backup_finish_date

, DATEDIFF(

SECOND

, bk.backup_start_date

, bk.backup_finish_date) AS duration

, bk.type

, bk.name

, bk.compressed_backup_size

, bm.physical_device_name

, bk.position

, ROW_NUMBER() OVER ( PARTITION BY bk.database_name

ORDER BY bk.backup_finish_date DESC ) AS RowNumber

FROM msdb..backupset bk

JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id

WHERE bk.type = 'I'

AND DB_ID(bk.database_name) IS NOT NULL -- test if database exists

AND bm.device_type IN (2,102) -- disk files

) AS IBA

JOIN ( SELECT *

FROM ( SELECT bk.database_name

, bk.backup_finish_date

, ROW_NUMBER() OVER ( PARTITION BY bk.database_name

ORDER BY bk.backup_finish_date DESC ) AS RowNumber

FROM msdb..backupset bk

JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id

WHERE bk.type = 'D'

AND DB_ID(bk.database_name) IS NOT NULL -- test if database exists

AND bm.device_type IN (2,102) -- disk files

) AS FB

WHERE RowNumber = 1 ) AS LFB ON IBA.database_name = LFB.database_name

AND IBA.backup_finish_date > LFB.backup_finish_date

WHERE IBA.RowNumber = 1

UNION ALL

-- Transaction logs backups after the last Full or Incremental Backup

SELECT TLB.database_name

, TLB.backup_set_id

, TLB.backup_set_uuid

, TLB.media_set_id

, TLB.backup_start_date

, TLB.backup_finish_date

, TLB.duration

, TLB.type

, TLB.name

, TLB.compressed_backup_size

, TLB.physical_device_name

, TLB.position

, 'RESTORE LOG [' + TLB.database_name + '] FROM DISK='''

+ TLB.physical_device_name + ''' WITH FILE = '

+ CAST(position AS VARCHAR(3)) + ', NORECOVERY;' AS restorecmd

FROM ( SELECT database_name

, backup_set_id

, backup_set_uuid

, bk.media_set_id

, backup_start_date

, backup_finish_date

, DATEDIFF(

SECOND

, bk.backup_start_date

, bk.backup_finish_date) AS duration

, type

, name

, compressed_backup_size

, physical_device_name

, bk.position

FROM msdb..backupset bk

JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id

WHERE bk.type = 'L'

AND DB_ID(bk.database_name) IS NOT NULL -- test if database exists

AND bm.device_type IN (2,102) -- disk files

) AS TLB

JOIN (

-- Last Database backup, Full or incremental (if any) LFIB

SELECT database_name

, backup_finish_date

, type

FROM ( SELECT bk.database_name

, bk.backup_finish_date

, bk.type

, ROW_NUMBER() OVER ( PARTITION BY bk.database_name

ORDER BY bk.backup_finish_date DESC ) AS RowNumber

FROM msdb..backupset bk

JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id

WHERE bk.type IN ( 'D', 'I' )

AND DB_ID(bk.database_name) IS NOT NULL -- test if database exists

AND bm.device_type IN (2,102) -- disk files

) AS FB

WHERE RowNumber = 1 ) AS LFIB ON TLB.database_name = LFIB.database_name

AND TLB.backup_finish_date > LFIB.backup_finish_date ) seq

Puis, n’oubliez pas de créer le serveur lié et le partage, ainsi que configurer les permissions nécessaires.

Base de données _SQL_MIGRATION_ et ses objets, à créer sur l’instance de destination

/*-------------------------------------------------------------------
[SCRIPT] Création de la base de gestion _SQL_MIGRATION_
[DATABASE] _SQL_MIGRATION_
[DESCRIPTION] Create database and objects
[AUTHOR] DATAFLY - Arian Papillon
-------------------------------------------------------------------*/
-- Création de la base sur l'instance destination et des objets nécessaires
----------------------
IF NOT EXISTS (   SELECT *
                  FROM   sys.databases
                  WHERE  name = '_SQL_MIGRATION_' )
    EXEC ( 'CREATE DATABASE _SQL_MIGRATION_' );
GO

USE _SQL_MIGRATION_;
GO

-- Création des objets
----------------------
-- suppressions préventives
IF OBJECT_ID('dbo.T_BackupsFilesToRestore') IS NOT NULL
    DROP TABLE dbo.T_BackupsFilesToRestore;
IF OBJECT_ID('dbo.F_CONCAT_FILE') IS NOT NULL
    EXEC ( 'DROP FUNCTION dbo.F_CONCAT_FILE' );
IF OBJECT_ID('dbo.F_EXTRACT_FILE') IS NOT NULL
    EXEC ( 'DROP FUNCTION dbo.F_EXTRACT_FILE' );
GO

--Table des bases à restaurer (créée une seule fois)
CREATE TABLE T_DatabasesToRestore
    (
        database_name VARCHAR(256) PRIMARY KEY
      , new_name VARCHAR(256) NULL
      , to_restore BIT
            DEFAULT ( 0 ) ,
    );
-- Exemple d'alimentation de la table
-- INSERT T_DatabasesToRestore (database_name,to_restore) SELECT name,1 FROM linkedserver.master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb'), -- toutes les bases
-- UPDATE dbo.T_DatabasesToRestore SET to_restore = 0 WHERE database_name IN ('master','model','msdb','tempdb')
GO

-- création des fonctions de travail -- merci à Frédéric Brouard
CREATE FUNCTION dbo.F_CONCAT_FILE
    (
        @BKD_ID INT
    )
RETURNS NVARCHAR(MAX)
    BEGIN
        DECLARE @R NVARCHAR(MAX);
        SET @R = '';
        SELECT @R = @R + ', MOVE ''' + DBF_FNAME_SQL + ''' TO '''
                    + DBF_FILE_TARGET + ''''
        FROM   dbo.T_DATABASE_FILE_DBF
        WHERE  BKD_ID = @BKD_ID;
        RETURN @R;
    END;
GO

CREATE FUNCTION dbo.F_EXTRACT_FILE
    (
        @FILE NVARCHAR(1024)
      , @WHAT CHAR(1) = 'N'
    )
RETURNS NVARCHAR(1024)
AS
    BEGIN
        -- si @WHAT = 'N' renvoi le nom, 
        --    @WHAT = 'E' renvoi l'extension, 
        --    @WHAT = 'P' renvoi le path, 
        IF ( @WHAT IS NULL )
           OR ( @WHAT NOT IN ( 'N', 'E', 'P' ))
           OR ( @FILE IS NULL )
           OR ( @FILE = '' )
            RETURN NULL;
        DECLARE @POINT SMALLINT
              , @SLASH SMALLINT
              , @LEN SMALLINT
              , @DATA NVARCHAR(1024);
        SET @LEN = LEN(@FILE);
        IF @WHAT IN ( 'N', 'E' )
            SET @POINT = @LEN - CHARINDEX('.', REVERSE(@FILE)) + 1;
        IF CHARINDEX('.', @FILE) = 0
            SET @POINT = @LEN + 1;
        IF @WHAT IN ( 'N', 'P' )
            SET @SLASH = @LEN - CHARINDEX('\', REVERSE(@FILE)) + 1;
        IF CHARINDEX('\', @FILE) = 0
            SET @SLASH = 0;
        IF @WHAT = 'N'
            RETURN SUBSTRING(@FILE, @SLASH + 1, @POINT - @SLASH - 1);
        IF @WHAT = 'P'
           AND @SLASH = 0
            RETURN '';
        IF @WHAT = 'P'
            RETURN SUBSTRING(@FILE, 1, @SLASH);
        IF @WHAT = 'E'
           AND @POINT > @LEN
            RETURN '';
        IF @WHAT = 'E'
            RETURN SUBSTRING(@FILE, @POINT + 1, LEN(@FILE) - @POINT);
        RETURN NULL;
    END;
GO

100

101

102

103

/*-------------------------------------------------------------------

[SCRIPT] Création de la base de gestion _SQL_MIGRATION_

[DATABASE] _SQL_MIGRATION_

[DESCRIPTION] Create database and objects

[AUTHOR] DATAFLY - Arian Papillon

-------------------------------------------------------------------*/

-- Création de la base sur l'instance destination et des objets nécessaires

----------------------

IF NOT EXISTS ( SELECT *

FROM sys.databases

WHERE name = '_SQL_MIGRATION_' )

EXEC ( 'CREATE DATABASE _SQL_MIGRATION_' );

USE _SQL_MIGRATION_;

-- Création des objets

----------------------

-- suppressions préventives

IF OBJECT_ID('dbo.T_BackupsFilesToRestore') IS NOT NULL

DROP TABLE dbo.T_BackupsFilesToRestore;

IF OBJECT_ID('dbo.F_CONCAT_FILE') IS NOT NULL

EXEC ( 'DROP FUNCTION dbo.F_CONCAT_FILE' );

IF OBJECT_ID('dbo.F_EXTRACT_FILE') IS NOT NULL

EXEC ( 'DROP FUNCTION dbo.F_EXTRACT_FILE' );

--Table des bases à restaurer (créée une seule fois)

CREATE TABLE T_DatabasesToRestore

(

database_name VARCHAR(256) PRIMARY KEY

, new_name VARCHAR(256) NULL

, to_restore BIT

DEFAULT ( 0 ) ,

);

-- Exemple d'alimentation de la table

-- INSERT T_DatabasesToRestore (database_name,to_restore) SELECT name,1 FROM linkedserver.master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb'), -- toutes les bases

-- UPDATE dbo.T_DatabasesToRestore SET to_restore = 0 WHERE database_name IN ('master','model','msdb','tempdb')

-- création des fonctions de travail -- merci à Frédéric Brouard

CREATE FUNCTION dbo.F_CONCAT_FILE

(

@BKD_ID INT

)

RETURNS NVARCHAR(MAX)

BEGIN

DECLARE @R NVARCHAR(MAX);

SET @R = '';

SELECT @R = @R + ', MOVE ''' + DBF_FNAME_SQL + ''' TO '''

+ DBF_FILE_TARGET + ''''

FROM dbo.T_DATABASE_FILE_DBF

WHERE BKD_ID = @BKD_ID;

RETURN @R;

END;

CREATE FUNCTION dbo.F_EXTRACT_FILE

(

@FILE NVARCHAR(1024)

, @WHAT CHAR(1) = 'N'

)

RETURNS NVARCHAR(1024)

BEGIN

-- si @WHAT = 'N' renvoi le nom,

-- @WHAT = 'E' renvoi l'extension,

-- @WHAT = 'P' renvoi le path,

IF ( @WHAT IS NULL )

OR ( @WHAT NOT IN ( 'N', 'E', 'P' ))

OR ( @FILE IS NULL )

OR ( @FILE = '' )

RETURN NULL;

DECLARE @POINT SMALLINT

, @SLASH SMALLINT

, @LEN SMALLINT

, @DATA NVARCHAR(1024);

SET @LEN = LEN(@FILE);

IF @WHAT IN ( 'N', 'E' )

SET @POINT = @LEN - CHARINDEX('.', REVERSE(@FILE)) + 1;

IF CHARINDEX('.', @FILE) = 0

SET @POINT = @LEN + 1;

IF @WHAT IN ( 'N', 'P' )

SET @SLASH = @LEN - CHARINDEX('\', REVERSE(@FILE)) + 1;

IF CHARINDEX('\', @FILE) = 0

SET @SLASH = 0;

IF @WHAT = 'N'

RETURN SUBSTRING(@FILE, @SLASH + 1, @POINT - @SLASH - 1);

IF @WHAT = 'P'

AND @SLASH = 0

RETURN '';

IF @WHAT = 'P'

RETURN SUBSTRING(@FILE, 1, @SLASH);

IF @WHAT = 'E'

AND @POINT > @LEN

RETURN '';

IF @WHAT = 'E'

RETURN SUBSTRING(@FILE, @POINT + 1, LEN(@FILE) - @POINT);

RETURN NULL;

END;

La procédure stockée de restauration, à créer sur la base _SQL_MIGRATION_ précédemment créée

Les paramètres sont les suivants :

@SourceLinkedServer NVARCHAR(64) — Nom du serveur lié source des sauvegarde (sous la forme SERVEUR\INSTANCE)
@DatabaseToRestoreTable NVARCHAR(256) — Nom de la table qui contient la liste des bases à restaurer
@PathBackupRetrieval NVARCHAR(256), — chemin où aller chercher les sauvegardes (le nom du partage)
@PathBackupOriginal NVARCHAR(256) — chemin où sont faites les sauvegardes sur la source (partie qui sera remplacée par @PathBackupRetrieval)
@PathDestinationData NVARCHAR(256) — chemin du répertoire de destination des fichiers de données de la base à restaurer
@PathDestinationLog NVARCHAR(256) — chemin du répertoire de destination des fichiers de journal à restaurer
@Init BIT — à 1, initialisation, à 0 restore les nouvelles sauvegardes non encore restaurées
@Debug BIT — à 1 (par défaut) n’exécute pas et renvoie le script, à 0 il execute. En debug, ne met pas à jour le statut.
@OnlyFullBackups BIT = 0 — à 1, ne considère que les backups FULL
@NoRestoreLogs BIT = 0 — à 1, ne traite pas les sauvegardes de journal (mais les différentielles sont traitées)
@SourceSQLVersion INT — paramètre non utilisé actuellement
@SetOfflineBefore BIT = 0 — à 1, si la base existe, la met OFFLINE WITH ROLLBACK IMMEDIATE avant restauration
@DoRecovery BIT = 0 — à 1, effectue le RECOVERY de chaque base après la dernière restauration de la séquence
@TargetCompatibility INT — si renseigné et @DoRecovery activé, niveau de compatibilité à configurer après restauration

Exemple d’utilisation :

EXEC dbo.AutoRestoreDB @SourceLinkedServer = N’MONK\SQL2016′ , @DatabaseToRestoreTable = N’T_DatabasesToRestore’ , @PathBackupRetrieval = N’\\server\sharebackup\’ , @PathBackupOriginal = N’c:\temp\’ , @PathDestinationData = N’C:\MSSQLData\MSSQL14.SQL2017\MSSQL\Data’ , @PathDestinationLog = N’C:\MSSQLData\MSSQL14.SQL2017\MSSQL\Data’ , @Init = 1 , @Debug = 1 , @OnlyFullBackups = 0 , @NoRestoreLogs = 0 , @SourceSQLVersion = NULL , @SetOfflineBefore = 1 , @DoRecovery = 1 , @TargetCompatibility = 140

/*-------------------------------------------------------------------
[SCRIPT] Restauration générique pour migration : procédure AutoRestoreDB
[DATABASE] _SQL_MIGRATION_  
[DESCRIPTION] Restaure la séquence de backups (disque), soit entièrement, soit uniquement les nouveaux journaux.
[MAJ PAR] DATAFLY - Arian Papillon
[DATEMAJ]  mars 2018
[Utilisation] 

Pré-requis : 
	Sur le serveur destination
		Base _SQL_MIGRATION_ sur la destination où créer cette procédure 
		Table des bases à restaurer (voir script ci-dessous)
		Serveur lié vers le serveur source avec la sécurité configurée
	Sur le serveur source
		Vue msdb.dbo.V_LastDiskBackupSequence sur le serveur source (voir script ci-dessous)
		Partage sur le serveur source vers le répertoire des backups et configuration des permissions

Paramétrage - Modifier :
		@SourceLinkedServer NVARCHAR(64)			-- Nom du serveur lié source des sauvegarde (sous la forme SERVEUR\INSTANCE)
		@DatabaseToRestoreTable NVARCHAR(256)		-- Nom de la table qui contient la liste des bases à restaurer
        @PathBackupRetrieval NVARCHAR(256),			-- chemin où aller chercher les sauvegardes
		@PathBackupOriginal NVARCHAR(256)           -- chemin où sont faites les sauvegardes sur la source (sera remplacé par @PathBackupRetrieval)
        @PathDestinationData NVARCHAR(256)			-- chemin du répertoire de destination des fichiers de données
		@PathDestinationLog NVARCHAR(256)			-- chemin du répertoire de destination des fichiers de journal
		@Init BIT									-- à 1, initialisation, à 0 restore les nouvelles sauvegardes non encore restaurées
		@Debug BIT									-- à 1, n'exécute pas, à 0 il execute. En debug, ne met pas à jour le statut.
		@OnlyFullBackups BIT = 0					-- à 1, ne considère que les backups FULL
		@NoRestoreLogs BIT = 0						-- à 1, ne traite pas les sauvegardes de journal
		@SourceSQLVersion INT						-- paramètre non utilisé actuellement
		@SetOfflineBefore BIT = 0					-- à 1, si la base existe, la met OFFLINE WITH ROLLBACK IMMEDIATE avant restauration
		@DoRecovery BIT = 0							-- à 1, effectue le RECOVERY de chaque base après la dernière restauration de la séquence
		@TargetCompatibility INT					-- si renseigné et @DoRecovery activé, niveau de compatibilité à configurer après restauration

-- Exemple d'utilisation
EXEC dbo.AutoRestoreDB @SourceLinkedServer = N'MONK\SQL2016'				
                     , @DatabaseToRestoreTable = N'T_DatabasesToRestore' 
                     , @PathBackupRetrieval = N'c:\temp\'    
                     , @PathBackupOriginal = N'c:\temp\'     
                     , @PathDestinationData = N'C:\MSSQLData\MSSQL14.SQL2017\MSSQL\Data'   
                     , @PathDestinationLog = N'C:\MSSQLData\MSSQL14.SQL2017\MSSQL\Data'     
                     , @Init = 1                     
                     , @Debug = 1                   
                     , @OnlyFullBackups = 0          
                     , @NoRestoreLogs = 0           
                     , @SourceSQLVersion = NULL         
                     , @SetOfflineBefore = 1         
                     , @DoRecovery = 1               
                     , @TargetCompatibility = 140    
-------------------------------------------------------------------*/

CREATE OR ALTER PROCEDURE AutoRestoreDB
    @SourceLinkedServer NVARCHAR(64)
  , @DatabaseToRestoreTable NVARCHAR(256)
  , @PathBackupRetrieval NVARCHAR(256)
  , @PathBackupOriginal NVARCHAR(256)
  , @PathDestinationData NVARCHAR(256)
  , @PathDestinationLog NVARCHAR(256)
  , @Init BIT = 1
  , @Debug BIT = 1
  , @OnlyFullBackups BIT = 0
  , @NoRestoreLogs BIT = 0
  , @SourceSQLVersion INT
  , @SetOfflineBefore BIT = 0
  , @DoRecovery BIT = 0
  , @TargetCompatibility INT
AS

    --===========================================================================--
    -- ### Variables pour test                                                  ###--
    --===========================================================================--
    --DECLARE @SourceLinkedServer NVARCHAR(64) = 'MONK\SQL2016';
    --DECLARE @PathBackupRetrieval NVARCHAR(256) = 'c:\temp\' -- où on va récupérer les sauvegardes (partage)
    --      , @PathBackupOriginal NVARCHAR(256) = 'c:\temp\'; -- où les sauvegardes sont effectuées (chemin local)
    --DECLARE @PathDestinationData NVARCHAR(256) = 'G:\MSSQLData\SINISTRESDATA\'   -- où stocker (move) les fichiers de données  
    --      , @PathDestinationLog NVARCHAR(256) = 'G:\MSSQLTLogs\SINISTRESTLOGS\'; -- où stocker (move) les fichiers de journaux

    --DECLARE @Init BIT = 1; -- Initialisation (FULL Backup) ou ajout de nouveaux logs
    --DECLARE @Debug BIT = 1; -- Mode debug (à 0, il exécute le restore)
    --DECLARE @DatabaseToRestoreTable NVARCHAR(256) = N'T_DatabasesToRestore'
    --DECLARE @OnlyFullBackups BIT = 0;
    --DECLARE @NoRestoreLogs BIT = 0;
    --DECLARE @SourceSQLVersion INT;
    --DECLARE @SetOfflineBefore BIT = 0;
    --DECLARE @DoRecovery BIT = 0;
    --DECLARE @TargetCompatibility INT = 120;
    -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @xp_cmdshell SQL_VARIANT;
    DECLARE @adv SQL_VARIANT;
    SELECT @xp_cmdshell = value_in_use
    FROM   sys.configurations
    WHERE  name LIKE 'xp_cmdshell%';
    SELECT @adv = value_in_use
    FROM   sys.configurations
    WHERE  name LIKE 'show advanced%';

    IF @xp_cmdshell = 0
        BEGIN
            IF @adv = 0
                BEGIN
                    EXEC sp_configure 'show advanced'
                                    , 1;
                    RECONFIGURE WITH OVERRIDE;
                END;
            EXEC sp_configure 'xp_cmdshell'
                            , 1;
            RECONFIGURE WITH OVERRIDE;
        END;


    SET NOCOUNT ON;
    IF @Init = 1
        BEGIN
            IF OBJECT_ID('dbo.T_BackupsFilesToRestore') IS NOT NULL
                DROP TABLE dbo.T_BackupsFilesToRestore; -- A ne supprimer que si réinitialisation
            IF OBJECT_ID('dbo.T_BackupsFilesToRestore') IS NULL
                BEGIN
                    CREATE TABLE [dbo].[T_BackupsFilesToRestore]
                        (
                            [id] INT IDENTITY PRIMARY KEY
                          , [database_name] [NVARCHAR](128) NULL
                          , [backup_set_id] [INT] NOT NULL
                          , [backup_set_uuid] [UNIQUEIDENTIFIER] NOT NULL
                          , [media_set_id] [INT] NOT NULL
                          , [backup_start_date] [DATETIME] NULL
                          , [backup_finish_date] [DATETIME] NULL
                          , [duration] [INT] NULL
                          , [type] [CHAR](1) NULL
                          , [name] [NVARCHAR](128) NULL
                          , [compressed_backup_size] [NUMERIC](20, 0) NULL
                          , [physical_device_name] [NVARCHAR](400) NULL
                          , [source_physical_name] [NVARCHAR](400)
                          , [position] INT
                          --					, [restorecmd] NVARCHAR(400)
                          , done BIT
                                DEFAULT ( 0 )
                          , restore_start_date DATETIME NULL
                          , restore_finish_date DATETIME NULL
                          , new_name NVARCHAR(128) NULL
                          , reverse_sequence INT
                        ) ON [PRIMARY];
                END;
        END;


    -- Récupérer la liste des sauvegardes concernées 
    SET @SQL = '
INSERT  dbo.T_BackupsFilesToRestore
        ( [database_name]
        , [backup_set_id]
        , [backup_set_uuid]
        , [media_set_id]
        , [backup_start_date]
        , [backup_finish_date]
        , [duration]
        , [type]
        , [name]
        , [compressed_backup_size]
        , [physical_device_name]
		, [position]
--		, [restorecmd]
		, [new_name]
		, [reverse_sequence]
        )
        SELECT  --LB.backup_sequence,
                LB.database_name
              , LB.backup_set_id
              , LB.backup_set_uuid
              , LB.media_set_id
              , LB.backup_start_date
              , LB.backup_finish_date
              , LB.duration
              , LB.type
              , LB.name
              , LB.compressed_backup_size
              , LB.physical_device_name
              , LB.position
--              , LB.restorecmd
			  , LD.new_name
			  , ROW_NUMBER() OVER (PARTITION BY LB.database_name ORDER BY LB.backup_finish_date DESC) AS reverse_sequence
        FROM    [' + @SourceLinkedServer
               + '].msdb.dbo.V_LastDiskBackupSequence LB 
                INNER HASH JOIN ' + @DatabaseToRestoreTable
               + ' LD ON LB.database_name = LD.database_name
                                                           AND LD.to_restore = 1 ';
    -- Limiter aux backup FULL
    IF @OnlyFullBackups = 1
        SET @SQL = @SQL + ' AND LB.type = ''D'' ';
    IF @NoRestoreLogs = 1
        SET @SQL = @SQL + ' AND LB.type <> ''L'' ';
    -------------------------
    SET @SQL = @SQL
               + 'WHERE   NOT EXISTS ( SELECT *
                             FROM   dbo.T_BackupsFilesToRestore
                             WHERE  backup_set_id = LB.backup_set_id );';
    EXEC sp_executesql @SQL;
    -- Séquence de restore
    DECLARE @database_name NVARCHAR(128)
          , @new_name NVARCHAR(128)
          , @reverse_sequence INT
          , @backup_set_id INT
          , @type CHAR(1)
          , @physical_device_name NVARCHAR(400);
    DECLARE @FileNameToSearch VARCHAR(400);
    DECLARE @FileNewName TABLE
        (
            FileName NVARCHAR(256)
        );
    DECLARE @ChkFile VARCHAR(255);
    DECLARE @source_physical_name NVARCHAR(400);
    DECLARE @position INT;
    DECLARE @move NVARCHAR(2000);
    -- RESTORE FILELISTONLY FROM DISK = ...
    IF OBJECT_ID('tempdb..#DbFilesFromBackup') IS NOT NULL
        DROP TABLE #DbFilesFromBackup;
    CREATE TABLE #DbFilesFromBackup
        (
            LogicalName NVARCHAR(128)
          , PhysicalName NVARCHAR(260)
          , [Type] CHAR(1)
          , FileGroupName NVARCHAR(128)
          , Taille NUMERIC(20, 0)
          , MaxSize NUMERIC(20, 0)
          , FileID BIGINT
          , CreateLSN NUMERIC(25, 0)
          , DropLSN NUMERIC(25, 0) NULL
          , UniqueID UNIQUEIDENTIFIER
          , ReadOnlyLSN NUMERIC(25, 0) NULL
          , ReadWriteLSN NUMERIC(25, 0) NULL
          , BackupSizeInBytes BIGINT
          , SourceBlockSize INT
          , FileGroupID INT
          , LogGroupGUID UNIQUEIDENTIFIER NULL
          , DifferentialBaseLSN NUMERIC(25, 0) NULL
          , DifferentialBaseGUID UNIQUEIDENTIFIER
          , IsReadOnly BIT
          , IsPresent BIT
          , TDEThumbprint VARBINARY(32)
        --      , SnapshotUrl NVARCHAR(360) -- SQL 2016+
        );
    IF LEFT(CAST(SERVERPROPERTY('productversion') AS VARCHAR(10)), CHARINDEX(
                                                                       '.'
                                                                     , CAST(SERVERPROPERTY(
                                                                                'productversion') AS VARCHAR(10)))
                                                                   - 1) >= 13
        ALTER TABLE #DbFilesFromBackup
        ADD SnapshotUrl NVARCHAR(360); -- SQL 2016+

    DECLARE RestoreCursor CURSOR FOR
        SELECT   database_name
               , backup_set_id
               , type
               , physical_device_name
               , position
               , new_name
               , reverse_sequence
        FROM     [dbo].[T_BackupsFilesToRestore]
        WHERE    done = 0
        ORDER BY database_name ASC
               , backup_finish_date ASC
        FOR UPDATE OF source_physical_name
                    , done
                    , restore_start_date
                    , restore_finish_date;

    OPEN RestoreCursor;
    FETCH NEXT FROM RestoreCursor
    INTO @database_name
       , @backup_set_id
       , @type
       , @physical_device_name
       , @position
       , @new_name
       , @reverse_sequence;
    WHILE ( @@FETCH_STATUS >= 0 )
        BEGIN

            -- vérifier l'existence du fichier (ou son nouveau nom si l'extension a changé), mettre à jour source_physical_name
            SET @FileNameToSearch = REPLACE(
                                        dbo.F_EXTRACT_FILE(
                                            @physical_device_name , 'P')
                                      , @PathBackupOriginal
                                      , @PathBackupRetrieval)
                                    + dbo.F_EXTRACT_FILE(
                                          @physical_device_name , 'N') + '*';
            SET @ChkFile = 'DIR /B ' + @FileNameToSearch;
            DELETE FROM @FileNewName;
            INSERT @FileNewName
            EXEC xp_cmdshell @ChkFile;
            SELECT TOP 1 @source_physical_name = FileName
            FROM   @FileNewName;
            UPDATE dbo.T_BackupsFilesToRestore
            SET    source_physical_name = REPLACE(
                                              dbo.F_EXTRACT_FILE(
                                                  @physical_device_name , 'P')
                                            , @PathBackupOriginal
                                            , @PathBackupRetrieval)
                                          + @source_physical_name
            WHERE CURRENT OF RestoreCursor;

            IF @source_physical_name IS NULL
                RAISERROR('Erreur fichier de backup', 16, 1);

            -- Commande de restore
            IF @type = 'D'
                BEGIN
                    DELETE FROM #DbFilesFromBackup;
                    SET @SQL = 'RESTORE FILELISTONLY FROM DISK = '''
                               + REPLACE(
                                     dbo.F_EXTRACT_FILE(
                                         @physical_device_name , 'P')
                                   , @PathBackupOriginal
                                   , @PathBackupRetrieval)
                               + @source_physical_name + ''' WITH FILE = '
                               + CAST(@position AS VARCHAR(3));
                    INSERT INTO #DbFilesFromBackup
                    EXEC sp_executesql @SQL;
                    SET @move = '';
                    -- SELECT * FROM @DbFilesFromBackup
                    SELECT @move = @move + 'MOVE ''' + LogicalName
                                   + ''' TO '''
                                   + CASE WHEN [Type] = 'D' THEN
                                              @PathDestinationData
                                          WHEN [Type] = 'L' THEN
                                              @PathDestinationLog
                                     END
                                   + dbo.F_EXTRACT_FILE(PhysicalName, 'N')
                                   + '.'
                                   + dbo.F_EXTRACT_FILE(PhysicalName, 'E')
                                   + ''', '
                    FROM   #DbFilesFromBackup;
                    IF @SetOfflineBefore = 1
                       AND DB_ID(COALESCE(@new_name, @database_name)) IS NOT NULL
                        BEGIN
                            SET @SQL = 'ALTER DATABASE ['
                                       + COALESCE(@new_name, @database_name)
                                       + '] SET OFFLINE WITH ROLLBACK IMMEDIATE; ';
                        END;
                    ELSE
                        BEGIN
                            SET @SQL = '';
                        END;
                    SET @SQL = @SQL + 'RESTORE DATABASE ['
                               + COALESCE(@new_name, @database_name)
                               + '] FROM DISK = '''
                               + REPLACE(
                                     dbo.F_EXTRACT_FILE(
                                         @physical_device_name , 'P')
                                   , @PathBackupOriginal
                                   , @PathBackupRetrieval)
                               + @source_physical_name + ''' WITH FILE = '
                               + CAST(@position AS VARCHAR(3)) + ', ' + @move
                               + 'NORECOVERY, REPLACE, STATS=10';
                END;
            IF @type = 'I'
                BEGIN
                    SET @SQL = 'RESTORE DATABASE ['
                               + COALESCE(@new_name, @database_name)
                               + '] FROM DISK = '''
                               + REPLACE(
                                     dbo.F_EXTRACT_FILE(
                                         @physical_device_name , 'P')
                                   , @PathBackupOriginal
                                   , @PathBackupRetrieval)
                               + @source_physical_name + ''' WITH FILE = '
                               + CAST(@position AS VARCHAR(3))
                               + ', NORECOVERY, STATS=10';
                END;
            IF @type = 'L'
                BEGIN
                    SET @SQL = 'RESTORE LOG ['
                               + COALESCE(@new_name, @database_name)
                               + '] FROM DISK = '''
                               + REPLACE(
                                     dbo.F_EXTRACT_FILE(
                                         @physical_device_name , 'P')
                                   , @PathBackupOriginal
                                   , @PathBackupRetrieval)
                               + @source_physical_name + ''' WITH FILE = '
                               + CAST(@position AS VARCHAR(3))
                               + ', NORECOVERY, STATS=10';
                END;

            UPDATE dbo.T_BackupsFilesToRestore
            SET    restore_start_date = GETDATE()
            WHERE CURRENT OF RestoreCursor;

            -----------------------------------------------
            PRINT @SQL;
            IF @Debug = 0
                BEGIN
                    EXEC sp_executesql @SQL; --run restore
                    -----------------------------------------------
                    IF @@ERROR = 0
                        BEGIN

                            UPDATE dbo.T_BackupsFilesToRestore
                            SET    restore_finish_date = GETDATE()
                                 , done = 1
                            WHERE CURRENT OF RestoreCursor;
                        END;
                END;
            IF @DoRecovery = 1
               AND @reverse_sequence = 1 -- last backup to restore
                BEGIN
                    SET @SQL = 'RESTORE DATABASE ['
                               + COALESCE(@new_name, @database_name)
                               + '] WITH RECOVERY;';
                    PRINT @SQL;
                    IF @Debug = 0
                        EXEC sp_executesql @SQL;
                    IF @TargetCompatibility IS NOT NULL
                        BEGIN
                            SET @SQL = 'ALTER DATABASE ['
                                       + COALESCE(@new_name, @database_name)
                                       + '] SET COMPATIBILITY_LEVEL = '
                                       + CAST(@TargetCompatibility AS VARCHAR(3));
                            PRINT @SQL;
                            IF @Debug = 0
                                EXEC sp_executesql @SQL;
                        END;
                END;
            FETCH NEXT FROM RestoreCursor
            INTO @database_name
               , @backup_set_id
               , @type
               , @physical_device_name
               , @position
               , @new_name
               , @reverse_sequence;
        END;
    CLOSE RestoreCursor;
    DEALLOCATE RestoreCursor;


    IF @xp_cmdshell = 0
        BEGIN
            EXEC sp_configure 'xp_cmdshell'
                            , 0;
            RECONFIGURE WITH OVERRIDE;
            IF @adv = 0
                BEGIN
                    EXEC sp_configure 'show advanced'
                                    , 0;
                    RECONFIGURE WITH OVERRIDE;
                END;
        END;

GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

/*-------------------------------------------------------------------

[SCRIPT] Restauration générique pour migration : procédure AutoRestoreDB

[DATABASE] _SQL_MIGRATION_

[DESCRIPTION] Restaure la séquence de backups (disque), soit entièrement, soit uniquement les nouveaux journaux.

[MAJ PAR] DATAFLY - Arian Papillon

[DATEMAJ] mars 2018

[Utilisation]

Pré-requis :

Sur le serveur destination

Base _SQL_MIGRATION_ sur la destination où créer cette procédure

Table des bases à restaurer (voir script ci-dessous)

Serveur lié vers le serveur source avec la sécurité configurée

Sur le serveur source

Vue msdb.dbo.V_LastDiskBackupSequence sur le serveur source (voir script ci-dessous)

Partage sur le serveur source vers le répertoire des backups et configuration des permissions

Paramétrage - Modifier :

@SourceLinkedServer NVARCHAR(64) -- Nom du serveur lié source des sauvegarde (sous la forme SERVEUR\INSTANCE)

@DatabaseToRestoreTable NVARCHAR(256) -- Nom de la table qui contient la liste des bases à restaurer

@PathBackupRetrieval NVARCHAR(256), -- chemin où aller chercher les sauvegardes

@PathBackupOriginal NVARCHAR(256) -- chemin où sont faites les sauvegardes sur la source (sera remplacé par @PathBackupRetrieval)

@PathDestinationData NVARCHAR(256) -- chemin du répertoire de destination des fichiers de données

@PathDestinationLog NVARCHAR(256) -- chemin du répertoire de destination des fichiers de journal

@Init BIT -- à 1, initialisation, à 0 restore les nouvelles sauvegardes non encore restaurées

@Debug BIT -- à 1, n'exécute pas, à 0 il execute. En debug, ne met pas à jour le statut.

@OnlyFullBackups BIT = 0 -- à 1, ne considère que les backups FULL

@NoRestoreLogs BIT = 0 -- à 1, ne traite pas les sauvegardes de journal

@SourceSQLVersion INT -- paramètre non utilisé actuellement

@SetOfflineBefore BIT = 0 -- à 1, si la base existe, la met OFFLINE WITH ROLLBACK IMMEDIATE avant restauration

@DoRecovery BIT = 0 -- à 1, effectue le RECOVERY de chaque base après la dernière restauration de la séquence

@TargetCompatibility INT -- si renseigné et @DoRecovery activé, niveau de compatibilité à configurer après restauration

-- Exemple d'utilisation

EXEC dbo.AutoRestoreDB @SourceLinkedServer = N'MONK\SQL2016'

, @DatabaseToRestoreTable = N'T_DatabasesToRestore'

, @PathBackupRetrieval = N'c:\temp\'

, @PathBackupOriginal = N'c:\temp\'

, @PathDestinationData = N'C:\MSSQLData\MSSQL14.SQL2017\MSSQL\Data'

, @PathDestinationLog = N'C:\MSSQLData\MSSQL14.SQL2017\MSSQL\Data'

, @Init = 1

, @Debug = 1

, @OnlyFullBackups = 0

, @NoRestoreLogs = 0

, @SourceSQLVersion = NULL

, @SetOfflineBefore = 1

, @DoRecovery = 1

, @TargetCompatibility = 140

-------------------------------------------------------------------*/

CREATE OR ALTER PROCEDURE AutoRestoreDB

@SourceLinkedServer NVARCHAR(64)

, @DatabaseToRestoreTable NVARCHAR(256)

, @PathBackupRetrieval NVARCHAR(256)

, @PathBackupOriginal NVARCHAR(256)

, @PathDestinationData NVARCHAR(256)

, @PathDestinationLog NVARCHAR(256)

, @Init BIT = 1

, @Debug BIT = 1

, @OnlyFullBackups BIT = 0

, @NoRestoreLogs BIT = 0

, @SourceSQLVersion INT

, @SetOfflineBefore BIT = 0

, @DoRecovery BIT = 0

, @TargetCompatibility INT

--===========================================================================--

-- ### Variables pour test ###--

--===========================================================================--

--DECLARE @SourceLinkedServer NVARCHAR(64) = 'MONK\SQL2016';

--DECLARE @PathBackupRetrieval NVARCHAR(256) = 'c:\temp\' -- où on va récupérer les sauvegardes (partage)

-- , @PathBackupOriginal NVARCHAR(256) = 'c:\temp\'; -- où les sauvegardes sont effectuées (chemin local)

--DECLARE @PathDestinationData NVARCHAR(256) = 'G:\MSSQLData\SINISTRESDATA\' -- où stocker (move) les fichiers de données

-- , @PathDestinationLog NVARCHAR(256) = 'G:\MSSQLTLogs\SINISTRESTLOGS\'; -- où stocker (move) les fichiers de journaux

--DECLARE @Init BIT = 1; -- Initialisation (FULL Backup) ou ajout de nouveaux logs

--DECLARE @Debug BIT = 1; -- Mode debug (à 0, il exécute le restore)

--DECLARE @DatabaseToRestoreTable NVARCHAR(256) = N'T_DatabasesToRestore'

--DECLARE @OnlyFullBackups BIT = 0;

--DECLARE @NoRestoreLogs BIT = 0;

--DECLARE @SourceSQLVersion INT;

--DECLARE @SetOfflineBefore BIT = 0;

--DECLARE @DoRecovery BIT = 0;

--DECLARE @TargetCompatibility INT = 120;

-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

DECLARE @SQL NVARCHAR(MAX);

DECLARE @xp_cmdshell SQL_VARIANT;

DECLARE @adv SQL_VARIANT;

SELECT @xp_cmdshell = value_in_use

FROM sys.configurations

WHERE name LIKE 'xp_cmdshell%';

SELECT @adv = value_in_use

FROM sys.configurations

WHERE name LIKE 'show advanced%';

IF @xp_cmdshell = 0

BEGIN

IF @adv = 0

BEGIN

EXEC sp_configure 'show advanced'

, 1;

RECONFIGURE WITH OVERRIDE;

END;

EXEC sp_configure 'xp_cmdshell'

, 1;

RECONFIGURE WITH OVERRIDE;

END;

SET NOCOUNT ON;

IF @Init = 1

BEGIN

IF OBJECT_ID('dbo.T_BackupsFilesToRestore') IS NOT NULL

DROP TABLE dbo.T_BackupsFilesToRestore; -- A ne supprimer que si réinitialisation

IF OBJECT_ID('dbo.T_BackupsFilesToRestore') IS NULL

BEGIN

CREATE TABLE [dbo].[T_BackupsFilesToRestore]

(

[id] INT IDENTITY PRIMARY KEY

, [database_name] [NVARCHAR](128) NULL

, [backup_set_id] [INT] NOT NULL

, [backup_set_uuid] [UNIQUEIDENTIFIER] NOT NULL

, [media_set_id] [INT] NOT NULL

, [backup_start_date] [DATETIME] NULL

, [backup_finish_date] [DATETIME] NULL

, [duration] [INT] NULL

, [type] [CHAR](1) NULL

, [name] [NVARCHAR](128) NULL

, [compressed_backup_size] [NUMERIC](20, 0) NULL

, [physical_device_name] [NVARCHAR](400) NULL

, [source_physical_name] [NVARCHAR](400)

, [position] INT

-- , [restorecmd] NVARCHAR(400)

, done BIT

DEFAULT ( 0 )

, restore_start_date DATETIME NULL

, restore_finish_date DATETIME NULL

, new_name NVARCHAR(128) NULL

, reverse_sequence INT

) ON [PRIMARY];

END;

-- Récupérer la liste des sauvegardes concernées

SET @SQL = '

INSERT dbo.T_BackupsFilesToRestore

( [database_name]

, [backup_set_id]

, [backup_set_uuid]

, [media_set_id]

, [backup_start_date]

, [backup_finish_date]

, [duration]

, [type]

, [name]

, [compressed_backup_size]

, [physical_device_name]

, [position]

-- , [restorecmd]

, [new_name]

, [reverse_sequence]

)

SELECT --LB.backup_sequence,

LB.database_name

, LB.backup_set_id

, LB.backup_set_uuid

, LB.media_set_id

, LB.backup_start_date

, LB.backup_finish_date

, LB.duration

, LB.type

, LB.name

, LB.compressed_backup_size

, LB.physical_device_name

, LB.position

-- , LB.restorecmd

, LD.new_name

, ROW_NUMBER() OVER (PARTITION BY LB.database_name ORDER BY LB.backup_finish_date DESC) AS reverse_sequence

FROM [' + @SourceLinkedServer

+ '].msdb.dbo.V_LastDiskBackupSequence LB

INNER HASH JOIN ' + @DatabaseToRestoreTable

+ ' LD ON LB.database_name = LD.database_name

AND LD.to_restore = 1 ';

-- Limiter aux backup FULL

IF @OnlyFullBackups = 1

SET @SQL = @SQL + ' AND LB.type = ''D'' ';

IF @NoRestoreLogs = 1

SET @SQL = @SQL + ' AND LB.type <> ''L'' ';

-------------------------

SET @SQL = @SQL

+ 'WHERE NOT EXISTS ( SELECT *

FROM dbo.T_BackupsFilesToRestore

WHERE backup_set_id = LB.backup_set_id );';

EXEC sp_executesql @SQL;

-- Séquence de restore

DECLARE @database_name NVARCHAR(128)

, @new_name NVARCHAR(128)

, @reverse_sequence INT

, @backup_set_id INT

, @type CHAR(1)

, @physical_device_name NVARCHAR(400);

DECLARE @FileNameToSearch VARCHAR(400);

DECLARE @FileNewName TABLE

(

FileName NVARCHAR(256)

);

DECLARE @ChkFile VARCHAR(255);

DECLARE @source_physical_name NVARCHAR(400);

DECLARE @position INT;

DECLARE @move NVARCHAR(2000);

-- RESTORE FILELISTONLY FROM DISK = ...

IF OBJECT_ID('tempdb..#DbFilesFromBackup') IS NOT NULL

DROP TABLE #DbFilesFromBackup;

CREATE TABLE #DbFilesFromBackup

(

LogicalName NVARCHAR(128)

, PhysicalName NVARCHAR(260)

, [Type] CHAR(1)

, FileGroupName NVARCHAR(128)

, Taille NUMERIC(20, 0)

, MaxSize NUMERIC(20, 0)

, FileID BIGINT

, CreateLSN NUMERIC(25, 0)

, DropLSN NUMERIC(25, 0) NULL

, UniqueID UNIQUEIDENTIFIER

, ReadOnlyLSN NUMERIC(25, 0) NULL

, ReadWriteLSN NUMERIC(25, 0) NULL

, BackupSizeInBytes BIGINT

, SourceBlockSize INT

, FileGroupID INT

, LogGroupGUID UNIQUEIDENTIFIER NULL

, DifferentialBaseLSN NUMERIC(25, 0) NULL

, DifferentialBaseGUID UNIQUEIDENTIFIER

, IsReadOnly BIT

, IsPresent BIT

, TDEThumbprint VARBINARY(32)

-- , SnapshotUrl NVARCHAR(360) -- SQL 2016+

);

IF LEFT(CAST(SERVERPROPERTY('productversion') AS VARCHAR(10)), CHARINDEX(

'.'

, CAST(SERVERPROPERTY(

'productversion') AS VARCHAR(10)))

- 1) >= 13

ALTER TABLE #DbFilesFromBackup

ADD SnapshotUrl NVARCHAR(360); -- SQL 2016+

DECLARE RestoreCursor CURSOR FOR

SELECT database_name

, backup_set_id

, type

, physical_device_name

, position

, new_name

, reverse_sequence

FROM [dbo].[T_BackupsFilesToRestore]

WHERE done = 0

ORDER BY database_name ASC

, backup_finish_date ASC

FOR UPDATE OF source_physical_name

, done

, restore_start_date

, restore_finish_date;

OPEN RestoreCursor;

FETCH NEXT FROM RestoreCursor

INTO @database_name

, @backup_set_id

, @type

, @physical_device_name

, @position

, @new_name

, @reverse_sequence;

WHILE ( @@FETCH_STATUS >= 0 )

BEGIN

-- vérifier l'existence du fichier (ou son nouveau nom si l'extension a changé), mettre à jour source_physical_name

SET @FileNameToSearch = REPLACE(

dbo.F_EXTRACT_FILE(

@physical_device_name , 'P')

, @PathBackupOriginal

, @PathBackupRetrieval)

+ dbo.F_EXTRACT_FILE(

@physical_device_name , 'N') + '*';

SET @ChkFile = 'DIR /B ' + @FileNameToSearch;

DELETE FROM @FileNewName;

INSERT @FileNewName

EXEC xp_cmdshell @ChkFile;

SELECT TOP 1 @source_physical_name = FileName

FROM @FileNewName;

UPDATE dbo.T_BackupsFilesToRestore

SET source_physical_name = REPLACE(

dbo.F_EXTRACT_FILE(

@physical_device_name , 'P')

, @PathBackupOriginal

, @PathBackupRetrieval)

+ @source_physical_name

WHERE CURRENT OF RestoreCursor;

IF @source_physical_name IS NULL

RAISERROR('Erreur fichier de backup', 16, 1);

-- Commande de restore

IF @type = 'D'

BEGIN

DELETE FROM #DbFilesFromBackup;

SET @SQL = 'RESTORE FILELISTONLY FROM DISK = '''

+ REPLACE(

dbo.F_EXTRACT_FILE(

@physical_device_name , 'P')

, @PathBackupOriginal

, @PathBackupRetrieval)

+ @source_physical_name + ''' WITH FILE = '

+ CAST(@position AS VARCHAR(3));

INSERT INTO #DbFilesFromBackup

EXEC sp_executesql @SQL;

SET @move = '';

-- SELECT * FROM @DbFilesFromBackup

SELECT @move = @move + 'MOVE ''' + LogicalName

+ ''' TO '''

+ CASE WHEN [Type] = 'D' THEN

@PathDestinationData

WHEN [Type] = 'L' THEN

@PathDestinationLog

END

+ dbo.F_EXTRACT_FILE(PhysicalName, 'N')

+ '.'

+ dbo.F_EXTRACT_FILE(PhysicalName, 'E')

+ ''', '

FROM #DbFilesFromBackup;

IF @SetOfflineBefore = 1

AND DB_ID(COALESCE(@new_name, @database_name)) IS NOT NULL

BEGIN

SET @SQL = 'ALTER DATABASE ['

+ COALESCE(@new_name, @database_name)

+ '] SET OFFLINE WITH ROLLBACK IMMEDIATE; ';

END;

ELSE

BEGIN

SET @SQL = '';

END;

SET @SQL = @SQL + 'RESTORE DATABASE ['

+ COALESCE(@new_name, @database_name)

+ '] FROM DISK = '''

+ REPLACE(

dbo.F_EXTRACT_FILE(

@physical_device_name , 'P')

, @PathBackupOriginal

, @PathBackupRetrieval)

+ @source_physical_name + ''' WITH FILE = '

+ CAST(@position AS VARCHAR(3)) + ', ' + @move

+ 'NORECOVERY, REPLACE, STATS=10';

END;

IF @type = 'I'

BEGIN

SET @SQL = 'RESTORE DATABASE ['

+ COALESCE(@new_name, @database_name)

+ '] FROM DISK = '''

+ REPLACE(

dbo.F_EXTRACT_FILE(

@physical_device_name , 'P')

, @PathBackupOriginal

, @PathBackupRetrieval)

+ @source_physical_name + ''' WITH FILE = '

+ CAST(@position AS VARCHAR(3))

+ ', NORECOVERY, STATS=10';

END;

IF @type = 'L'

BEGIN

SET @SQL = 'RESTORE LOG ['

+ COALESCE(@new_name, @database_name)

+ '] FROM DISK = '''

+ REPLACE(

dbo.F_EXTRACT_FILE(

@physical_device_name , 'P')

, @PathBackupOriginal

, @PathBackupRetrieval)

+ @source_physical_name + ''' WITH FILE = '

+ CAST(@position AS VARCHAR(3))

+ ', NORECOVERY, STATS=10';

END;

UPDATE dbo.T_BackupsFilesToRestore

SET restore_start_date = GETDATE()

WHERE CURRENT OF RestoreCursor;

-----------------------------------------------

PRINT @SQL;

IF @Debug = 0

BEGIN

EXEC sp_executesql @SQL; --run restore

-----------------------------------------------

IF @@ERROR = 0

BEGIN

UPDATE dbo.T_BackupsFilesToRestore

SET restore_finish_date = GETDATE()

, done = 1

WHERE CURRENT OF RestoreCursor;

END;

IF @DoRecovery = 1

AND @reverse_sequence = 1 -- last backup to restore

BEGIN

SET @SQL = 'RESTORE DATABASE ['

+ COALESCE(@new_name, @database_name)

+ '] WITH RECOVERY;';

PRINT @SQL;

IF @Debug = 0

EXEC sp_executesql @SQL;

IF @TargetCompatibility IS NOT NULL

BEGIN

SET @SQL = 'ALTER DATABASE ['

+ COALESCE(@new_name, @database_name)

+ '] SET COMPATIBILITY_LEVEL = '

+ CAST(@TargetCompatibility AS VARCHAR(3));

PRINT @SQL;

IF @Debug = 0

EXEC sp_executesql @SQL;

END;

FETCH NEXT FROM RestoreCursor

INTO @database_name

, @backup_set_id

, @type

, @physical_device_name

, @position

, @new_name

, @reverse_sequence;

END;

CLOSE RestoreCursor;

DEALLOCATE RestoreCursor;

IF @xp_cmdshell = 0

BEGIN

EXEC sp_configure 'xp_cmdshell'

, 0;

RECONFIGURE WITH OVERRIDE;

IF @adv = 0

BEGIN

EXEC sp_configure 'show advanced'

, 0;

RECONFIGURE WITH OVERRIDE;

END;