Le concept assez ancien de « Log Shipping » (littéralement journaux naviguant) permet à peu de frais de construire un serveur de secours asynchrone à reprise manuelle. Il est basé sur l’envoi régulier et continu des copies des journaux de transaction générés par la sauvegarde transactionnelle.

La mécanique

Elle se décompose en trois parties :

La génération régulière de sauvegardes des journaux de transactions, que l’on effectuera par le biais de l’Agent SQL et à condition que les bases visées soit en mode de journalisation complète (FULL RECOVERY).
le déplacement des fichiers de sauvegarde du serveur source au serveur cible, par le biais d’un mécanisme propre au système d’exploitation ou bien à l’aide d’un outil tierce (Robocopy par exemple).
La restauration des bases à partir de ces sauvegardes transactionnelles, à condition de mettre ces bases en mode d’attente (NORECOVERY).

Quelques rappels

Pour générer des fichiers de sauvegarde transactionnelle, il suffit d’utiliser la commande Transact SQL :

BACKUP LOG nom_base 
   TO DISK = 'Chemin\Fichier.trn';

1 2	BACKUP LOG nom_base TO DISK = 'Chemin\Fichier.trn';

Par convention, on utilise l’extension .TRN pour les sauvegardes transactionnelles.

Pour restaurer une base à partir des sauvegardes transactionnelles, il faut :

commencer par restaurer le plus récent fichier de la sauvegarde complète de la base, en mode NORECOVERY (la base est alors inaccessible mais présente à l’état de « restauration »)

Ceci se fait à l’aide de la commande :

RESTORE DATABASE nom_base 
   FROM DISK = 'Chemin\Fichier.bak' 
   WITH NORECOVERY;

RESTORE DATABASE nom_base

FROM DISK = 'Chemin\Fichier.bak'

WITH NORECOVERY;

compléter la base en restaurant, dans l’ordre, les fichiers de sauvegarde transactionnelle en mode NORECOVERY (la base se trouve alors encore en état de restauration).

Ceci se fait à l’aide de la commande :

RESTORE LOG nom_base 
   FROM DISK = 'Chemin\Fichier.trn' 
   WITH NORECOVERY;

RESTORE LOG nom_base

FROM DISK = 'Chemin\Fichier.trn'

WITH NORECOVERY;

Le processus devant fonctionner en continu, la base est toujours inaccessible par ce que présente à l’état de restauration.

Pour rendre la base à l’état de production il suffit de restaurer la dernière sauvegarde transactionnelle avec l’option RECOVERY :

RESTORE LOG nom_base  
   FROM DISK = 'Chemin\Fichier.trn' 
   WITH RECOVERY;

RESTORE LOG nom_base

FROM DISK = 'Chemin\Fichier.trn'

WITH RECOVERY;

Notez que le mot clef RECOVERY n’est pas obligatoire car à défaut.

Le journal de transaction est constitué de segments identifiés par des nombres binaires appelés Log Segment Number (LSN) qui se suivent. Ainsi pour pouvoir reconstituer toutes les transactions d’une base, il faut que ces LSN se recoupent entre la précédente restauration transactionnelle effectuée et la nouvelle à passer.

Toute rupture dans la chaine des segments entraine l’impossibilité de reconstituer la base.

Il faut donc faire très attention à veiller à la continuité de la chaine des sauvegardes transactionnelle qui doit donc être sans oubli et sans trou. Par exemple une sauvegarde complète inopinée casserait cette belle mécanique (sauf si elle est effectuée avec l’option COPY_ONLY).

Quelques subtilités

Si votre base est en état de restauration, il est possible de la basculer en état de production par une simple commande :

RESTORE LOG nom_base 
   WITH RECOVERY;

1 2	RESTORE LOG nom_base WITH RECOVERY;

Mais dans ce cas vous ne pourrez plus la compléter avec de nouvelles sauvegardes transactionnelles puisque, à l’état de production, son contenu a déjà divergé de la base d’origine. En effet, une transaction la mettant en mode de production a été journalisée à l’intérieur de la base cassant ainsi la continuité des segments transactionnels…

Toujours en état de restauration, il est possible de rendre votre base accessible en lecture seulement et dans un statut permettant de la remettre en état de restauration afin de redémarrer le processus de log shipping. Ceci se fait à l’aide de la commande :

RESTORE LOG nom_base 
   WITH NORECOVERY, 
        STANDBY = 'Chemin\FichierUndo.bak';

RESTORE LOG nom_base

WITH NORECOVERY,

STANDBY = 'Chemin\FichierUndo.bak';

Le fichier « FichierUndo.bak » est un journal parallèle utilisé pour stocker temporairement les données des transactions non encore validées, que le moteur « défait » afin de permettre la lecture de toutes les tables (les verrous en cours étant abandonnés).

Notez que la base se place en état de veille avec lecture seule. Les données sont alors accessibles pour tous types de lectures (vues, requêtes, fonctions, procédures…).

Si la base est en état de restauration avec lecture seulement, alors il est possible de revenir en mode de restauration pour reprendre la continuité du processus de « log shipping » à l’aide de la commande :

RESTORE LOG nom_base WITH NORECOVERY;

1	RESTORE LOG nom_base WITH NORECOVERY;

La solution d’automatisation

J’ai automatisé une telle solution pour l’un de mes clients à l’aide de deux tables et cinq procédures, le tout figurant dans la base msdb, dans des schémas spécifiques :

_MNT : pour la maintenance, dont la sauvegarde (côté source)…
LOG_SHIPPEMENT pour tous les objets du « log shipping » (côté cible)

Côté source

Une seule procédure, qui effectue toutes les sauvegardes :

USE msdb;
GO
CREATE PROCEDURE _MNT.P_SAUVE_TOUTES_BASE 
                 @TYPE_SAUVEGARDE     CHAR(4), 
                 @DESTINATION         NVARCHAR(256),
                 @CONSERVE_JOUR       TINYINT,
                 @DATABASE            NVARCHAR(128) = NULL
AS
/******************************************************************************
* MODULE  : MAINTENANCE                                                       *
* NATURE  : PROCEDURE                                                         *
* OBJECT  : msdb._MNT.P_SAUVE_TOUTES_BASE                                     *
* CREATE  : 2015-12-09                                                        *
* AUTHOR  : Frederic Brouard (FBD)                                            *
* VERSION : 1                                                                 *
* SYSTEM  : No                                                                *
* VALID   : 2012-2017                                                         *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
* expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *
*******************************************************************************
* PURPOSE :                                                                   *
* Cette procédure effectue les sauvegardes ordinaires d'une base particulière *
* ou de toutes les bases du serveur :                                         *
* Si le paramètre @DATABASE n'est pas renseigné, la sauvegarde concerne       *
* toutes les bases à l'exception de model et tempdb.                          *
* Les bases systèmes master et msdb sont sauvegardées.                        *
* Seule les bases n'étant pas en mode de récupération simple peuvent faire    *
* l'objet d'une sauvegarde transactionnelle.                                  *
* La base master ne peut faire l'objet d'une sauvegarde différentielle        *
* Les bases non concernées sont les bases ayant les caractéristiques          *
* suivantes :                                                                 *
*    Les clichés de BD (DATABASE SNAPSHOT) : source_database_id non NULL      *
*    Bases en lecture seule : is_read_only = 1                                *
*    Bases "hors ligne" : state_desc &amp;lt;&amp;gt; 'ONLINE'                              *
*    Bases non opérationnelle : is_in_standby = 1 ou is_cleanly_shutdown = 1  *
*******************************************************************************
* PARAMÈTRES EN ENTRÉE :                                                      *
*    @TYPE_SAUVEGARDE CHAR(4) :                                               *
*       Nature de la sauvegarde parmi ...                                     *
*          'FULL' =&amp;gt; complète                                                 *             
*          'DIFF' =&amp;gt; différentielle                                           * 
*          'TRAN' =&amp;gt; transactionnelle                                         *
*    @DESTINATION NVARCHAR(256) :                                             *
*       Chemin de stockage des fichiers des sauvegardés                       *
*    @CONSERVE_JOUR TINYINT :                                                 *
*       nombre de jours pendant lesquels les fichiers de sauvegardes sont     *
*       conservés avant d'être supprimés                                      *
*    @DATABASE            NVARCHAR(128) = NULL                 *
* DataBase(s) Name(s) to backup, can be NULL. In this case all databases                  *
*******************************************************************************
* EXAMPLE :                                                                   *
*    EXEC _MNT.P_SAUVE_TOUTES_BASE 'FULL', 'F:\BACKUP\', 7, NULL;             *
*    Sauvegarde les bases de données dans le répertoire F:\BACKUP             *
*    et supprime les fichiers des sauvegardes datant de plus de 7 jours       *
*******************************************************************************
* NOTES   :                                                                   *
*    Cette procédure manipule les procédures systèmes suivantes :             *
*       xp_fileexist pour vérifier si le répertoire passé en argument existe  *
*          sur le système de fichier                                          *
*       xp_delete_file pour supprimer les fichiers de sauvegarde obsolète     *
******************************************************************************/
SET NOCOUNT ON;
DECLARE @ERROR VARCHAR(2044);
-- vérification paramètre de type de sauvegarde
IF @TYPE_SAUVEGARDE IS NULL 
   SET @TYPE_SAUVEGARDE = 'FULL';
SET @TYPE_SAUVEGARDE = UPPER(@TYPE_SAUVEGARDE)
IF @TYPE_SAUVEGARDE NOT IN ('FULL', 'DIFF', 'TRAN')
BEGIN
   SET @ERROR = 'Procédure msdb._MNT.P_SAUVE_TOUTES_BASE  - '
              + 'valeur de paramètre inconnu pour @TYPE_SAUVE : %s';
   RAISERROR(@ERROR, 16, 1, @TYPE_SAUVEGARDE);
   RETURN;
END;
-- si base passée en paramètre, vérification d'existance de cette base
IF @DATABASE IS NOT NULL 
   IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DATABASE)
BEGIN
   SET @ERROR = 'Procédure msdb._MNT.P_SAUVE_TOUTES_BASE  - '
              + 'valeur de paramètre incorrect pour @DATABASE : %s';
   RAISERROR(@ERROR, 16, 1, @DATABASE);
   RETURN;
END;
-- vérification paramètre de répertoire
IF @DESTINATION IS NULL OR @DESTINATION = ''
BEGIN
   SET @ERROR = 'Procédure msdb._MNT.P_SAUVE_TOUTES_BASE  - '
              +'valeur de paramètre anormal pour @DIR_SAUVE : NULL ou vide !';
   RAISERROR(@ERROR, 16, 1);
   RETURN;
END
SET @DESTINATION = RTRIM(@DESTINATION);
IF RIGHT(@DESTINATION, 1) &amp;lt;&amp;gt; N'\'
   SET @DESTINATION = @DESTINATION + N'\';
DECLARE @T TABLE (FILE_EXISTS BIT, FILE_IS_DIR BIT, PARENT_FILE_EXISTS BIT);
INSERT INTO @T
EXEC master.dbo.xp_fileexist @DESTINATION;
IF NOT EXISTS(SELECT * FROM @T WHERE FILE_IS_DIR = 1)
BEGIN
   SET @ERROR = 'Procédure msdb.S_SQLPRO.P_BACKUPS - '
              +'valeur de paramètre inconnu pour @DIR_SAUVE : %s '
              +'(le répertoire n''existe pas).';
   RAISERROR(@ERROR, 16, 1, @DESTINATION);
   RETURN;
END
-- création commandes de sauvegarde
DECLARE @DTA NVARCHAR(32), @SQL NVARCHAR(max), @EXT NCHAR(3);
SELECT @DTA = N'_D' + REPLACE(REPLACE(REPLACE(CONVERT(NCHAR(19), 
                                              GETDATE(), 121), 
                                      N'-', N''), 
                              N':', N''), 
                      N' ', N'_T'),
       @SQL = N'',
       @EXT = CASE @TYPE_SAUVEGARDE 
                 WHEN 'FULL' THEN N'BAK'
                 WHEN 'DIFF' THEN N'DIF'
                 WHEN 'TRAN' THEN N'TRN'
       END;
SELECT @SQL = @SQL + N'BACKUP ' + 
       CASE WHEN @TYPE_SAUVEGARDE = 'TRAN' 
               THEN N'LOG ' 
            ELSE N'DATABASE ' 
       END + name + N' TO DISK = ''' + @DESTINATION + 
    + REPLACE(CAST(SERVERPROPERTY('ServerName') 
                   AS NVARCHAR(30)),'\','_') + N'_' +
       name + @DTA + 
       N'.' + @EXT + N''' ' +
       CASE @TYPE_SAUVEGARDE 
          WHEN 'DIFF' 
             THEN N' WITH DIFFERENTIAL' 
          ELSE N'' 
       END + N'; '
FROM   sys.databases 
       AS db
       LEFT OUTER JOIN sys.database_recovery_status AS rs
            ON db.database_id = rs.database_id
WHERE  name = COALESCE(@DATABASE, name)
  AND  name NOT IN ('model', 'tempdb')
  AND  state_desc = 'ONLINE' 
  AND  source_database_id IS NULL
  AND  is_read_only = 0
  AND  is_in_standby = 0
  AND  is_cleanly_shutdown = 0
--&amp;gt; sauvegarde transactionnelle impossible si mode de récupération simple
  AND  CASE 
          WHEN @TYPE_SAUVEGARDE = 'TRAN' AND recovery_model = 3 
             THEN 0 
          ELSE 1 
        END = 1
--&amp;gt; sauvegarde différentielle impossible pour la base master
  AND CASE WHEN @TYPE_SAUVEGARDE = 'DIFF' AND name = 'master' 
             THEN 0 
          ELSE 1 
        END = 1
-- si sauvegarde transactionnelle demandée, la base doit avoir été initialisée par une sauvegarde précédente
  AND CASE WHEN @TYPE_SAUVEGARDE = 'TRAN' 
  AND last_log_backup_lsn IS NULL   
              THEN 0
           ELSE 1
      END = 1;
-- exécution commande de sauvegarde
EXEC (@SQL);
IF @CONSERVE_JOUR IS NULL
   RETURN;
-- suppression des fichiers de sauvegarde anciens
DECLARE @D DATE;
SET @D = DATEADD(day, @CONSERVE_JOUR * -1, GETDATE());
SET @EXT = 'TRN';
EXEC master.sys.xp_delete_file 0, @DESTINATION, @EXT, @D, 0;
SET @EXT = 'BAK';
EXEC master.sys.xp_delete_file 0, @DESTINATION, @EXT, @D, 0;
SET @EXT = 'DIF';
EXEC master.sys.xp_delete_file 0, @DESTINATION, @EXT, @D, 0;
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

USE msdb;

CREATE PROCEDURE _MNT.P_SAUVE_TOUTES_BASE

@TYPE_SAUVEGARDE CHAR(4),

@DESTINATION NVARCHAR(256),

@CONSERVE_JOUR TINYINT,

@DATABASE NVARCHAR(128) = NULL

/******************************************************************************

* MODULE : MAINTENANCE *

* NATURE : PROCEDURE *

* OBJECT : msdb._MNT.P_SAUVE_TOUTES_BASE *

* CREATE : 2015-12-09 *

* AUTHOR : Frederic Brouard (FBD) *

* VERSION : 1 *

* SYSTEM : No *

* VALID : 2012-2017 *

*******************************************************************************

* Frédéric BROUARD - alias SQLpro - SARL SQL SPOT - SQLpro@sqlspot.com *

* Architecte de données : expertise, audit, conseil, formation, modélisation *

* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *

* blog: http://blog.developpez.com/sqlpro site: http://sqlpro.developpez.com *

* expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *

*******************************************************************************

* PURPOSE : *

* Cette procédure effectue les sauvegardes ordinaires d'une base particulière *

* ou de toutes les bases du serveur : *

* Si le paramètre @DATABASE n'est pas renseigné, la sauvegarde concerne *

* toutes les bases à l'exception de model et tempdb. *

* Les bases systèmes master et msdb sont sauvegardées. *

* Seule les bases n'étant pas en mode de récupération simple peuvent faire *

* l'objet d'une sauvegarde transactionnelle. *

* La base master ne peut faire l'objet d'une sauvegarde différentielle *

* Les bases non concernées sont les bases ayant les caractéristiques *

* suivantes : *

* Les clichés de BD (DATABASE SNAPSHOT) : source_database_id non NULL *

* Bases en lecture seule : is_read_only = 1 *

* Bases "hors ligne" : state_desc &lt;&gt; 'ONLINE' *

* Bases non opérationnelle : is_in_standby = 1 ou is_cleanly_shutdown = 1 *

*******************************************************************************

* PARAMÈTRES EN ENTRÉE : *

* @TYPE_SAUVEGARDE CHAR(4) : *

* Nature de la sauvegarde parmi ... *

* 'FULL' =&gt; complète *

* 'DIFF' =&gt; différentielle *

* 'TRAN' =&gt; transactionnelle *

* @DESTINATION NVARCHAR(256) : *

* Chemin de stockage des fichiers des sauvegardés *

* @CONSERVE_JOUR TINYINT : *

* nombre de jours pendant lesquels les fichiers de sauvegardes sont *

* conservés avant d'être supprimés *

* @DATABASE NVARCHAR(128) = NULL *

* DataBase(s) Name(s) to backup, can be NULL. In this case all databases *

*******************************************************************************

* EXAMPLE : *

* EXEC _MNT.P_SAUVE_TOUTES_BASE 'FULL', 'F:\BACKUP\', 7, NULL; *

* Sauvegarde les bases de données dans le répertoire F:\BACKUP *

* et supprime les fichiers des sauvegardes datant de plus de 7 jours *

*******************************************************************************

* NOTES : *

* Cette procédure manipule les procédures systèmes suivantes : *

* xp_fileexist pour vérifier si le répertoire passé en argument existe *

* sur le système de fichier *

* xp_delete_file pour supprimer les fichiers de sauvegarde obsolète *

******************************************************************************/

SET NOCOUNT ON;

DECLARE @ERROR VARCHAR(2044);

-- vérification paramètre de type de sauvegarde

IF @TYPE_SAUVEGARDE IS NULL

SET @TYPE_SAUVEGARDE = 'FULL';

SET @TYPE_SAUVEGARDE = UPPER(@TYPE_SAUVEGARDE)

IF @TYPE_SAUVEGARDE NOT IN ('FULL', 'DIFF', 'TRAN')

BEGIN

SET @ERROR = 'Procédure msdb._MNT.P_SAUVE_TOUTES_BASE - '

+ 'valeur de paramètre inconnu pour @TYPE_SAUVE : %s';

RAISERROR(@ERROR, 16, 1, @TYPE_SAUVEGARDE);

RETURN;

END;

-- si base passée en paramètre, vérification d'existance de cette base

IF @DATABASE IS NOT NULL

IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DATABASE)

BEGIN

SET @ERROR = 'Procédure msdb._MNT.P_SAUVE_TOUTES_BASE - '

+ 'valeur de paramètre incorrect pour @DATABASE : %s';

RAISERROR(@ERROR, 16, 1, @DATABASE);

RETURN;

END;

-- vérification paramètre de répertoire

IF @DESTINATION IS NULL OR @DESTINATION = ''

BEGIN

SET @ERROR = 'Procédure msdb._MNT.P_SAUVE_TOUTES_BASE - '

+'valeur de paramètre anormal pour @DIR_SAUVE : NULL ou vide !';

RAISERROR(@ERROR, 16, 1);

RETURN;

END

SET @DESTINATION = RTRIM(@DESTINATION);

IF RIGHT(@DESTINATION, 1) &lt;&gt; N'\'

SET @DESTINATION = @DESTINATION + N'\';

DECLARE @T TABLE (FILE_EXISTS BIT, FILE_IS_DIR BIT, PARENT_FILE_EXISTS BIT);

INSERT INTO @T

EXEC master.dbo.xp_fileexist @DESTINATION;

IF NOT EXISTS(SELECT * FROM @T WHERE FILE_IS_DIR = 1)

BEGIN

SET @ERROR = 'Procédure msdb.S_SQLPRO.P_BACKUPS - '

+'valeur de paramètre inconnu pour @DIR_SAUVE : %s '

+'(le répertoire n''existe pas).';

RAISERROR(@ERROR, 16, 1, @DESTINATION);

RETURN;

END

-- création commandes de sauvegarde

DECLARE @DTA NVARCHAR(32), @SQL NVARCHAR(max), @EXT NCHAR(3);

SELECT @DTA = N'_D' + REPLACE(REPLACE(REPLACE(CONVERT(NCHAR(19),

GETDATE(), 121),

N'-', N''),

N':', N''),

N' ', N'_T'),

@SQL = N'',

@EXT = CASE @TYPE_SAUVEGARDE

WHEN 'FULL' THEN N'BAK'

WHEN 'DIFF' THEN N'DIF'

WHEN 'TRAN' THEN N'TRN'

END;

SELECT @SQL = @SQL + N'BACKUP ' +

CASE WHEN @TYPE_SAUVEGARDE = 'TRAN'

THEN N'LOG '

ELSE N'DATABASE '

END + name + N' TO DISK = ''' + @DESTINATION +

+ REPLACE(CAST(SERVERPROPERTY('ServerName')

AS NVARCHAR(30)),'\','_') + N'_' +

name + @DTA +

N'.' + @EXT + N''' ' +

CASE @TYPE_SAUVEGARDE

WHEN 'DIFF'

THEN N' WITH DIFFERENTIAL'

ELSE N''

END + N'; '

FROM sys.databases

AS db

LEFT OUTER JOIN sys.database_recovery_status AS rs

ON db.database_id = rs.database_id

WHERE name = COALESCE(@DATABASE, name)

AND name NOT IN ('model', 'tempdb')

AND state_desc = 'ONLINE'

AND source_database_id IS NULL

AND is_read_only = 0

AND is_in_standby = 0

AND is_cleanly_shutdown = 0

--&gt; sauvegarde transactionnelle impossible si mode de récupération simple

AND CASE

WHEN @TYPE_SAUVEGARDE = 'TRAN' AND recovery_model = 3

THEN 0

ELSE 1

END = 1

--&gt; sauvegarde différentielle impossible pour la base master

AND CASE WHEN @TYPE_SAUVEGARDE = 'DIFF' AND name = 'master'

THEN 0

ELSE 1

END = 1

-- si sauvegarde transactionnelle demandée, la base doit avoir été initialisée par une sauvegarde précédente

AND CASE WHEN @TYPE_SAUVEGARDE = 'TRAN'

AND last_log_backup_lsn IS NULL

THEN 0

ELSE 1

END = 1;

-- exécution commande de sauvegarde

EXEC (@SQL);

IF @CONSERVE_JOUR IS NULL

RETURN;

-- suppression des fichiers de sauvegarde anciens

DECLARE @D DATE;

SET @D = DATEADD(day, @CONSERVE_JOUR * -1, GETDATE());

SET @EXT = 'TRN';

EXEC master.sys.xp_delete_file 0, @DESTINATION, @EXT, @D, 0;

SET @EXT = 'BAK';

EXEC master.sys.xp_delete_file 0, @DESTINATION, @EXT, @D, 0;

SET @EXT = 'DIF';

EXEC master.sys.xp_delete_file 0, @DESTINATION, @EXT, @D, 0;

Côté cible

Deux tables chacune dotée d’un déclencheur…

USE msdb;
GO
CREATE SCHEMA LOG_SHIPPEMENT;                      -- SQL specific schema for log shippement
GO
CREATE TABLE LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB
(SDB_ID                       SMALLINT IDENTITY PRIMARY KEY, 
 SDB_DB_NAME                  sysname UNIQUE,      -- real database name (in case of parasitic characters)
 SDB_SOURCE_SERVER            sysname,             -- corrected source server name (\ must be replaced by _)
 SDB_SOURCE_DATABASE          sysname UNIQUE,      -- corrected database name
 SDB_PATH_TO_BACKUPS          NVARCHAR(512),       -- path of the backups files
 SDB_LAST_RESTORED_BACKFILE   NVARCHAR(512),       -- name of the last backup file that have been restored  
 SDB_LAST_DATETIME_BACKFILE   DATETIME2(0),        -- datetime of the last backup file that have been restored
 SDB_LAST_RESTORE_COMMAND     NVARCHAR(max),       -- Transact SQL command of the last executed restore 
 SDB_LAST_STANDBY_FILE        NVARCHAR(512)        -- file name of the last standby transaction log<br /> SDB_LAST_SESSION_START       DATETIME2(0));       -- datetime of the last execution of proc P_DO_RESTORE
GO<br /><br />CREATE TRIGGER LOG_SHIPPEMENT.E_IU_SDB<br />ON LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB<br />FOR INSERT, UPDATE<br />AS<br />/******************************************************************************<br />* MODULE  : LOG_SHIPPEMENT                                                    *<br />* NATURE  : TRIGGER                                                           *<br />* OBJECT  : msdb.LOG_SHIPPEMENT.E_IU_SDB                                      *<br />* PARENT  : msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB                         *<br />* CREATE  : 2019-01-10                                                        *<br />* AUTHOR  : Frederic Brouard (FBD)                                            *<br />* VERSION : 1                                                                 *<br />* VALID   : 2012-2017                                                         *<br />*******************************************************************************<br />* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *<br />* Architecte de données :  expertise, audit, conseil, formation, modélisation *<br />* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *<br />* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *<br />* expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *<br />*******************************************************************************<br />* PURPOSE : test if pathes are accessible and terminated by "\"               *<br />*******************************************************************************<br />* IMPROVE :                                                                   *<br />*******************************************************************************<br />* BUGFIX  :                                                                   *<br />******************************************************************************/<br />SET NOCOUNT ON;<br />-- adding '\' at the end if needed<br />UPDATE LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB<br />SET    SDB_PATH_TO_BACKUPS = <br />       SDB_PATH_TO_BACKUPS + CASE RIGHT(SDB_PATH_TO_BACKUPS, 1)<br />                                WHEN '\' THEN ''<br />                                ELSE '\'<br />                             END<br />WHERE  SDB_ID IN (SELECT SDB_ID FROM inserted);<br />-- checking if pathes exists<br />DECLARE C CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY<br />FOR SELECT SDB_PATH_TO_BACKUPS<br />    FROM   inserted;<br />DECLARE @P TABLE -- used to test if directory path exists<br />(<br />FILE_EXISTS   BIT, <br />IS_DIR        BIT, <br />PARENT_DIR    BIT<br />);<br />DECLARE @F NVARCHAR(512), @SQLCDM NVARCHAR(max);<br />OPEN C;<br />FETCH C INTO @F;<br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br />   INSERT INTO @P<br />   EXEC xp_fileexist @F;<br />   IF NOT EXISTS(SELECT * FROM @P WHERE IS_DIR = 1)<br />   BEGIN<br />      ROLLBACK;<br />      RAISERROR('[DECLENCHEUR : LOG_SHIPPEMENT.E_IU_FST] Le répertoire %s n''existe pas ou bien n''est pas accessible.', 16, 1, @F);<br />      CLOSE C;<br />      DEALLOCATE C;<br />      RETURN;<br />   END;<br />   FETCH C INTO @F;<br />END<br />CLOSE C;<br />DEALLOCATE C;<br />GO<br /><br />
CREATE TABLE LOG_SHIPPEMENT.T_FILE_STORAGE_FST
(FST_ID                  INT IDENTITY PRIMARY KEY,
 SDB_ID                  SMALLINT NOT NULL         -- FK from LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB
                         REFERENCES LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB (SDB_ID),
 FST_LOGICAL_FILE_NAME   sysname,                  -- logical file name of a database
 FST_PHYSICAL_PATH       NVARCHAR(512) NOT NULL);  -- physical path in destination for the logical file
GO
--===========================================================================--
-- mise en place du trigger sur table LOG_SHIPPEMENT.T_FILE_STORAGE_FST      --
--===========================================================================--
CREATE TRIGGER LOG_SHIPPEMENT.E_IU_FST
ON LOG_SHIPPEMENT.T_FILE_STORAGE_FST
FOR INSERT, UPDATE
AS
/******************************************************************************
* MODULE  : LOG_SHIPPEMENT                                                    *
* NATURE  : TRIGGER                                                           *
* OBJECT  : msdb.LOG_SHIPPEMENT.E_IU_FST                                      *
* PARENT  : msdb.LOG_SHIPPEMENT.T_FILE_STORAGE_FST                            *
* CREATE  : 2019-01-10                                                        *
* AUTHOR  : Frederic Brouard (FBD)                                            *
* VERSION : 1                                                                 *
* VALID   : 2012-2017                                                         *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
* expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *
*******************************************************************************
* PURPOSE : test if pathes are accessible and terminated by "\"               *
*******************************************************************************
* IMPROVE :                                                                   *
*******************************************************************************
* BUGFIX  :                                                                   *
******************************************************************************/
SET NOCOUNT ON;
-- adding '\' at the end if needed
UPDATE LOG_SHIPPEMENT.T_FILE_STORAGE_FST
SET    FST_PHYSICAL_PATH = 
       FST_PHYSICAL_PATH + CASE RIGHT(FST_PHYSICAL_PATH, 1)
                              WHEN '\' THEN ''
                              ELSE '\'
                           END
WHERE  FST_ID IN (SELECT FST_ID FROM inserted);
-- checking if pathes exists
DECLARE C CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR SELECT FST_PHYSICAL_PATH
    FROM   inserted;
DECLARE @P TABLE -- used to test if directory path exists
(
FILE_EXISTS   BIT, 
IS_DIR        BIT, 
PARENT_DIR    BIT
);
DECLARE @F NVARCHAR(512), @SQLCDM NVARCHAR(max);
OPEN C;
FETCH C INTO @F;
WHILE @@FETCH_STATUS = 0
BEGIN
   INSERT INTO @P
   EXEC xp_fileexist @F;
   IF NOT EXISTS(SELECT * FROM @P WHERE IS_DIR = 1)
   BEGIN
      ROLLBACK;
      RAISERROR('[DECLENCHEUR : LOG_SHIPPEMENT.E_IU_FST] Le répertoire %s n''existe pas ou bien n''est pas accessible.', 16, 1, @F);
      CLOSE C;
      DEALLOCATE C;
      RETURN;
   END;
   FETCH C INTO @F;
END
CLOSE C;
DEALLOCATE C;
GO

USE msdb;

CREATE SCHEMA LOG_SHIPPEMENT; -- SQL specific schema for log shippement

CREATE TABLE LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB

(SDB_ID SMALLINT IDENTITY PRIMARY KEY,

SDB_DB_NAME sysname UNIQUE, -- real database name (in case of parasitic characters)

SDB_SOURCE_SERVER sysname, -- corrected source server name (\ must be replaced by _)

SDB_SOURCE_DATABASE sysname UNIQUE, -- corrected database name

SDB_PATH_TO_BACKUPS NVARCHAR(512), -- path of the backups files

SDB_LAST_RESTORED_BACKFILE NVARCHAR(512), -- name of the last backup file that have been restored

SDB_LAST_DATETIME_BACKFILE DATETIME2(0), -- datetime of the last backup file that have been restored

SDB_LAST_RESTORE_COMMAND NVARCHAR(max), -- Transact SQL command of the last executed restore

SDB_LAST_STANDBY_FILE NVARCHAR(512) -- file name of the last standby transaction log SDB_LAST_SESSION_START DATETIME2(0)); -- datetime of the last execution of proc P_DO_RESTORE

GO CREATE TRIGGER LOG_SHIPPEMENT.E_IU_SDB ON LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB FOR INSERT, UPDATE AS /****************************************************************************** * MODULE : LOG_SHIPPEMENT * * NATURE : TRIGGER * * OBJECT : msdb.LOG_SHIPPEMENT.E_IU_SDB * * PARENT : msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB * * CREATE : 2019-01-10 * * AUTHOR : Frederic Brouard (FBD) * * VERSION : 1 * * VALID : 2012-2017 * ******************************************************************************* * Frédéric BROUARD - alias SQLpro - SARL SQL SPOT - SQLpro@sqlspot.com * * Architecte de données : expertise, audit, conseil, formation, modélisation * * tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL * * blog: http://blog.developpez.com/sqlpro site: http://sqlpro.developpez.com * * expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 * ******************************************************************************* * PURPOSE : test if pathes are accessible and terminated by "\" * ******************************************************************************* * IMPROVE : * ******************************************************************************* * BUGFIX : * ******************************************************************************/ SET NOCOUNT ON; -- adding '\' at the end if needed UPDATE LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB SET SDB_PATH_TO_BACKUPS = SDB_PATH_TO_BACKUPS + CASE RIGHT(SDB_PATH_TO_BACKUPS, 1) WHEN '\' THEN '' ELSE '\' END WHERE SDB_ID IN (SELECT SDB_ID FROM inserted); -- checking if pathes exists DECLARE C CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR SELECT SDB_PATH_TO_BACKUPS FROM inserted; DECLARE @P TABLE -- used to test if directory path exists ( FILE_EXISTS BIT, IS_DIR BIT, PARENT_DIR BIT ); DECLARE @F NVARCHAR(512), @SQLCDM NVARCHAR(max); OPEN C; FETCH C INTO @F; WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO @P EXEC xp_fileexist @F; IF NOT EXISTS(SELECT * FROM @P WHERE IS_DIR = 1) BEGIN ROLLBACK; RAISERROR('[DECLENCHEUR : LOG_SHIPPEMENT.E_IU_FST] Le répertoire %s n''existe pas ou bien n''est pas accessible.', 16, 1, @F); CLOSE C; DEALLOCATE C; RETURN; END; FETCH C INTO @F; END CLOSE C; DEALLOCATE C; GO

CREATE TABLE LOG_SHIPPEMENT.T_FILE_STORAGE_FST

(FST_ID INT IDENTITY PRIMARY KEY,

SDB_ID SMALLINT NOT NULL -- FK from LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB

REFERENCES LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB (SDB_ID),

FST_LOGICAL_FILE_NAME sysname, -- logical file name of a database

FST_PHYSICAL_PATH NVARCHAR(512) NOT NULL); -- physical path in destination for the logical file

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

-- mise en place du trigger sur table LOG_SHIPPEMENT.T_FILE_STORAGE_FST --

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

CREATE TRIGGER LOG_SHIPPEMENT.E_IU_FST

ON LOG_SHIPPEMENT.T_FILE_STORAGE_FST

FOR INSERT, UPDATE

/******************************************************************************

* MODULE : LOG_SHIPPEMENT *

* NATURE : TRIGGER *

* OBJECT : msdb.LOG_SHIPPEMENT.E_IU_FST *

* PARENT : msdb.LOG_SHIPPEMENT.T_FILE_STORAGE_FST *

* CREATE : 2019-01-10 *

* AUTHOR : Frederic Brouard (FBD) *

* VERSION : 1 *

* VALID : 2012-2017 *

*******************************************************************************

* Frédéric BROUARD - alias SQLpro - SARL SQL SPOT - SQLpro@sqlspot.com *

* Architecte de données : expertise, audit, conseil, formation, modélisation *

* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *

* blog: http://blog.developpez.com/sqlpro site: http://sqlpro.developpez.com *

* expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *

*******************************************************************************

* PURPOSE : test if pathes are accessible and terminated by "\" *

*******************************************************************************

* IMPROVE : *

*******************************************************************************

* BUGFIX : *

******************************************************************************/

SET NOCOUNT ON;

-- adding '\' at the end if needed

UPDATE LOG_SHIPPEMENT.T_FILE_STORAGE_FST

SET FST_PHYSICAL_PATH =

FST_PHYSICAL_PATH + CASE RIGHT(FST_PHYSICAL_PATH, 1)

WHEN '\' THEN ''

ELSE '\'

END

WHERE FST_ID IN (SELECT FST_ID FROM inserted);

-- checking if pathes exists

DECLARE C CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY

FOR SELECT FST_PHYSICAL_PATH

FROM inserted;

DECLARE @P TABLE -- used to test if directory path exists

(

FILE_EXISTS BIT,

IS_DIR BIT,

PARENT_DIR BIT

);

DECLARE @F NVARCHAR(512), @SQLCDM NVARCHAR(max);

OPEN C;

FETCH C INTO @F;

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO @P

EXEC xp_fileexist @F;

IF NOT EXISTS(SELECT * FROM @P WHERE IS_DIR = 1)

BEGIN

ROLLBACK;

RAISERROR('[DECLENCHEUR : LOG_SHIPPEMENT.E_IU_FST] Le répertoire %s n''existe pas ou bien n''est pas accessible.', 16, 1, @F);

CLOSE C;

DEALLOCATE C;

RETURN;

END;

FETCH C INTO @F;

END

CLOSE C;

DEALLOCATE C;

La première (LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB) liste les bases à « log shipper » avec les paramètres suivants :

SDB_DB_NAME : le nom réel de la base
SDB_SOURCE_SERVER : nom du serveur source dans lequel le caractère \ a été remplacé par un _
SDB_SOURCE_DATABASE : nom corrigé de la base (les caractères illicite doivent avoir été remplacé par _
SDB_PATH_TO_BACKUPS : chemin du stockage des fichiers de sauvegarde
SDB_LAST_RESTORED_BACKFILE : nom du fichier de la dernière sauvegarde restaurée
SDB_LAST_DATETIME_BACKFILE : dateheure de la dernière sauvegarde restaurée
SDB_LAST_RESTORE_COMMAND : code Transact SQL de la dernière sauvegarde restaurée
SDB_LAST_STANDBY_FILE : nom du fichier des transactions abandonnées par le STANDBY

La seconde (LOG_SHIPPEMENT.T_FILE_STORAGE_FST) liste les fichiers du stockage et n’est à utiliser que si l’arborescence du stockage est différente entre le serveur source et le serveur cible :

SDB_ID : clef étrangère référençant la précédente table (LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB)
FST_LOGICAL_FILE_NAME : nom logique du fichier de stockage
FST_PHYSICAL_PATH : chemin de stockage sur le serveur cible pour le fichier logique considéré

Exemple d’utilisation :

INSERT INTO LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB 
(SDB_DB_NAME, SDB_SOURCE_SERVER, SDB_SOURCE_DATABASE, SDB_PATH_TO_BACKUPS)
VALUES ('ABAC_EFS_DO001', 'FRVBN01-WSV072_MSSQL2016PROD01', 'MA_BASE_TEST', '\\FILER\SQLBackup\');
GO

INSERT INTO LOG_SHIPPEMENT.T_FILE_STORAGE_FST 
VALUES (1,'MA_BASE_TEST',     'D:\DATA_SQL\'),
       (1,'MA_BASE_TEST_log', 'E:\TRAN_SQL\');
GO

INSERT INTO LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB

(SDB_DB_NAME, SDB_SOURCE_SERVER, SDB_SOURCE_DATABASE, SDB_PATH_TO_BACKUPS)

VALUES ('ABAC_EFS_DO001', 'FRVBN01-WSV072_MSSQL2016PROD01', 'MA_BASE_TEST', '\\FILER\SQLBackup\');

INSERT INTO LOG_SHIPPEMENT.T_FILE_STORAGE_FST

VALUES (1,'MA_BASE_TEST', 'D:\DATA_SQL\'),

(1,'MA_BASE_TEST_log', 'E:\TRAN_SQL\');

Ce qui fait que seule la base « MA BASE TEST » sera « log shippé » et mes fichiers logiques placés dans les bons répertoires de destination.

Et nos procédures…

CREATE PROCEDURE LOG_SHIPPEMENT.P_DO_RESTORE 
   @DB_NAME     sysname, 
   @DEBUG       BIT = 0
AS
/******************************************************************************
* MODULE  : LOG_SHIPPEMENT                                                    *
* NATURE  : PROCEDURE                                                         *
* OBJECT  : msdb.LOG_SHIPPEMENT.P_DO_RESTORE                                  *
* CREATE  : 2019-01610                                                        *
* AUTHOR  : Frederic Brouard (FBD)                                            *
* VERSION : 1                                                                 *
* SYSTEM  : No                                                                *
* VALID   : 2016-2017 without correction,                                     *
*           2012-2014 with the following corrections :                        *
*              in DECLARE @H TABLE, do not specify the following columns :    *
*                 KeyAlgorithm             nvarchar(32),                      *
*                 EncryptorThumbprint      varbinary(20),                     *
*                 EncryptorType            nvarchar(32)                       *
*              in DECLARE @D TABLE, do not specify the following column :     *
*                 SnapshotURL              nvarchar(360)                      *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
* expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *
*******************************************************************************
* PURPOSE : implement log shipping for a database                             *
*******************************************************************************
* INPUTS  :                                                                   *
*    @DB_NAME the name of the database that will be log shipped               *
*    @DEBUG : if 1 PRINT and SELECT a maximum of steps while executing code   *
*******************************************************************************
* EXAMPLE :                                                                   *
*    EXEC msdb.LOG_SHIPPEMENT.P_DO_RESTORE 'DB_test', 1;                      *
*******************************************************************************
* NOTES   :                                                                   *
*   All the errors while executing the procedure, are logged into event log   *
*   The backup process must produce file with a file name pattern such as :   *
*      instance_name + _ + database_name +'_' + datetime formated as :        *
*         Daaaammjj_Thhmmss + file_extension                                  *
*   Sample : SERVERNAME_INSTANCE_DB_NAME_D20190106_T231234.BAK                *
*      in which :                                                             *
*   the instance SQL server name is "SERVERNAME_INSTANCE" (char "\" =&amp;gt; "_")   *
*   the database name is DB_NAME                                              *
*   and the file must contains only one backup                                *
*   Notice that the backup process is done by msdb._MNT.P_SAUVE_TOUTES_BASE   *
*   from the source server                                                    *
*******************************************************************************
* FUTUR   :                                                                   *
*    1) add parameter @RETENTION TINYINT to retain n log backup files         *
*    2) supports differential backups                                         *
*******************************************************************************
* IMPROVE :                                                                   *
*******************************************************************************
* BUGFIX  :                                                                   *
******************************************************************************/
IF @DEBUG = 0
   SET NOCOUNT ON;
DECLARE @MODE              CHAR(3),                
        -- BAK or TRN value depending if database is created or not
        @ADVCFG            BIT,                    
        -- value of "show advanced options" in sys.configurations
        @CMDSHELL          BIT,                    
        -- value of "xp_cmdshell" in sys.configurations
        @PW_TEMPLATE       NVARCHAR(4000)          
        -- template of PowerShell code to retrieve backup files (pattern above)
           = N'powershell.exe -Command " get-childitem  ''###*.~~~'' | Select-Object {$_.CreationTime.ToString(''yyyy-MM-ddThh:MM:ss'')+'';''+$_.Name} ',
        @PW_COMMAND        NVARCHAR(4000),         
        -- PowerShell command that is the completed template
        @PATH_TO_BACKUPS   NVARCHAR(512),          
        -- path to the backup files for this database
        @SERVER_NAME       sysname,                
        -- corrected name of the SQL source instance used to tag the backup file name
        @DB_PATTERN        sysname,                
        -- corrected name of the database source used to tag the backup file name
        @FILE              NVARCHAR(256),          
        -- one complete backup file name to process
        @FDATE             DATETIME2(0),           
        -- date of the backup file
        @SQLCMD            NVARCHAR(max),          
        -- SQL dynamic command
        @ERROR             BIT,                    
        -- an error occurred while restoring
        @ERRNB             SMALLINT = 0,           
        -- number of error while applying restore command
        @ERRMSG            NVARCHAR(1024),         
        -- error messages concatened
        @FPASSNB           SMALLINT = 0;           
        -- number of passed files
PRINT '*** BEGIN OF LOG SHIPPING PROCESS FOR DATABASE ' + @DB_NAME + ' ***';
---------------------------
-- retrieving parameters --
---------------------------
SELECT @PATH_TO_BACKUPS = SDB_PATH_TO_BACKUPS,
       @SERVER_NAME = SDB_SOURCE_SERVER,
       @DB_PATTERN = SDB_SOURCE_DATABASE 
FROM   msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB 
WHERE SDB_DB_NAME = @DB_NAME;
IF @DEBUG = 1
BEGIN
   PRINT '@PATH_TO_BACKUPS : ' + @PATH_TO_BACKUPS;
   PRINT '@SERVER_NAME : ' + @SERVER_NAME;
   PRINT '@DB_PATTERN : ' + @DB_PATTERN;
END;
-----------------------
-- table decarations --
-----------------------
DECLARE @P TABLE 
-- used to test if directory path exists
(
FILE_EXISTS   BIT, 
IS_DIR        BIT, 
PARENT_DIR    BIT
);
DECLARE @T TABLE 
-- used to store list of files
(
L NVARCHAR(1000)
);
DECLARE @F TABLE 
-- used to store files to process
(
F_ID     INT IDENTITY PRIMARY KEY, 
F_DATE   DATETIME2(0), 
F_NAME   VARCHAR(256), 
F_DONE   BIT);
DECLARE @H TABLE 
-- used to get backup file header
(
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,
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,
containment              tinyint,           
KeyAlgorithm             nvarchar(32),
EncryptorThumbprint      varbinary(20),
EncryptorType            nvarchar(32)
);
DECLARE @D TABLE
-- used to get backup file list
(
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), 
SnapshotURL              nvarchar(360)
);
---------------
-- checkings --
---------------
-- path terminated by '\' ?, if not, add it.
SET @PATH_TO_BACKUPS = @PATH_TO_BACKUPS +
                       CASE RIGHT(@PATH_TO_BACKUPS, 1) 
                          WHEN '\' THEN '' 
                          ELSE '\' 
                       END;
-- directory exists and accessible ?
INSERT INTO @P
EXEC xp_fileexist @PATH_TO_BACKUPS;
IF NOT EXISTS(SELECT * FROM @P WHERE IS_DIR = 1)
BEGIN
   RAISERROR('[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] Le répertoire %s n''existe pas ou bien n''est pas accessible.', 16, 1, @PATH_TO_BACKUPS) WITH LOG;
   RETURN;
END;
------------------------------------
-- which state has the database ? --
------------------------------------
-- database is actuelly exploited. No needs for log shippement
IF EXISTS(SELECT * 
          FROM   sys.databases 
          WHERE  name = @DB_NAME AND 
                 state = 0)
BEGIN
   RAISERROR('[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] La base %s est actuellement en production. Impossible de la mettre en récupération de journaux ("log shipping").', 10, 1, @DB_NAME) WITH LOG;
   RETURN;
END;
-- database has an incompatible state. Do not process
IF EXISTS(SELECT * 
          FROM   sys.databases 
          WHERE  name = @DB_NAME AND 
                 state &amp;gt; 1)
BEGIN
   RAISERROR('[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] La base %s est actuellement dans un mode incompatible pour la mettre en récupération de journaux ("log shipping").', 10, 1, @DB_NAME) WITH LOG;
   RETURN;
END;
-- database does not exists, needs to be created
IF NOT EXISTS(SELECT * 
              FROM   sys.databases 
              WHERE  name = @DB_NAME)
   SET @MODE = 'BAK'
ELSE
   SET @MODE = 'TRN';
--------------------------------------
-- retrieving database backup files --
--------------------------------------
SET @PW_COMMAND = 
   REPLACE(REPLACE(@PW_TEMPLATE, '###', @PATH_TO_BACKUPS + 
                   @SERVER_NAME + '_' + @DB_PATTERN), '~~~', @MODE);
IF @DEBUG = 1
   PRINT '@PW_COMMAND : ' + @PW_COMMAND;
SELECT @ADVCFG = CAST(value_in_use AS BIT)
FROM   sys.configurations
WHERE  name = 'show advanced options';
IF @ADVCFG = 0
  EXEC ('EXEC sp_configure ''show advanced options'', 1;RECONFIGURE;');
SELECT @CMDSHELL = CAST(value_in_use AS BIT)
FROM   sys.configurations
WHERE  name = 'xp_cmdshell';
IF @CMDSHELL = 0
  EXEC ('EXEC sp_configure ''xp_cmdshell'', 1;RECONFIGURE;');
INSERT INTO @T
EXEC xp_cmdshell @PW_COMMAND;
-- cleaning retrieved files
DELETE FROM @T 
WHERE L IS NULL OR L NOT LIKE '%.' + @MODE COLLATE Latin1_General_100_CI_AI;
IF @DEBUG = 1
   SELECT L AS FILE_RETRIEVED FROM @T;
-- there is no file to process
IF NOT EXISTS(SELECT * FROM @T)
BEGIN
   RAISERROR ('[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] Aucun fichier à traiter pour le log shipping de la base %s.', 10, 1, @DB_NAME) WITH LOG;
   IF @CMDSHELL = 0
      EXEC ('EXEC sp_configure ''xp_cmdshell'', 0;RECONFIGURE;');
   IF @ADVCFG = 0
      EXEC ('EXEC sp_configure ''show advanced options'', @;RECONFIGURE;');
   RETURN;
END;
-- adding file date
INSERT INTO @F (F_NAME)
SELECT RIGHT(L, LEN(L) - 20)
FROM   @T;
WITH 
T AS
(
SELECT F_ID, REPLACE(REPLACE(SUBSTRING(F_NAME, LEN(F_NAME) -19, 16), 
                             '_', ' '), 'T', '') AS F_PARSE
FROM   @F)
UPDATE F
SET    F_DATE = CAST(LEFT(F_PARSE, 4) + '-' + 
                     SUBSTRING(F_PARSE, 5, 2) + '-' +
                     SUBSTRING(F_PARSE, 7, 3) +
                     SUBSTRING(F_PARSE, 10, 2) + ':' + 
                     SUBSTRING(F_PARSE, 12, 2) + ':' + 
                     RIGHT(F_PARSE, 2) AS DATETIME2(0))
FROM   T AS U
       JOIN @F AS F
          ON U.F_ID = F.F_ID;
IF @DEBUG = 1    
   SELECT * FROM @F;
-------------------------------------------------------------------
-- first case : database does not exists, so needs to be created --
-------------------------------------------------------------------
IF @MODE = 'BAK'
BEGIN
   -- searching the most recent .BAK file
   SELECT @FDATE = MAX(F_DATE) FROM @F;
   SELECT @FILE = F_NAME FROM @F WHERE F_DATE = @FDATE;
   -- checking if there is only one baclup in the backup file
   SET @SQLCMD = N'RESTORE HEADERONLY FROM DISK = ''' +  
       @PATH_TO_BACKUPS + @FILE + ''';'
   INSERT INTO @H EXEC (@SQLCMD);
   IF @DEBUG = 1 
      SELECT * FROM @H;
   
   IF EXISTS(SELECT NULL FROM @H HAVING COUNT(*) &amp;gt; 1)
   BEGIN
      RAISERROR ('[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] La procédure ne prends pas en compte les fichiers de sauvegarde comportant plus d''une sauvegarde (%s).', 10, 1, @FILE) WITH LOG;
      IF @CMDSHELL = 0
         EXEC ('EXEC sp_configure ''xp_cmdshell'', 0;RECONFIGURE;');
      IF @ADVCFG = 0
         EXEC ('EXEC sp_configure ''show advanced options'', @;RECONFIGURE;');      
      RETURN;
   END;
   -- there is only one backup in the file
   -- getting path of the files
   SET @SQLCMD = N'RESTORE FILELISTONLY FROM DISK = ''' + 
       @PATH_TO_BACKUPS + @FILE + ''';'
   INSERT INTO @D EXEC (@SQLCMD);
   IF @DEBUG = 1 
      SELECT * FROM @D;
   -- il y a une redirection ? =&amp;gt; 
   WITH D AS
   (
   SELECT LogicalName AS LOGICAL_FILE_NAME, 
          PhysicalName,
          REVERSE(LEFT(REVERSE(PhysicalName), 
                       CHARINDEX('\', REVERSE(PhysicalName)) - 1)) 
             AS PHYSICAL_FILE_NAME
   FROM   @D
   )
   SELECT LOGICAL_FILE_NAME,
          COALESCE(FST_PHYSICAL_PATH + PHYSICAL_FILE_NAME, PhysicalName) 
             AS DIRECTION
   INTO #TFBF
   FROM   D
          LEFT OUTER JOIN msdb.LOG_SHIPPEMENT.T_FILE_STORAGE_FST AS S 
             ON D.LOGICAL_FILE_NAME = S.FST_LOGICAL_FILE_NAME
          LEFT OUTER JOIN msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB AS B 
             ON S.SDB_ID = B.SDB_ID AND SDB_DB_NAME = @DB_NAME;
   IF @DEBUG = 1 
      SELECT * FROM #TFBF;
   SET @SQLCMD = N'RESTORE DATABASE [' + @DB_NAME + '] FROM DISK = ''' + 
                 @PATH_TO_BACKUPS + @FILE + ''' WITH NORECOVERY, STATS = 10';
   SELECT @SQLCMD = @SQLCMD + ', MOVE ''' + LOGICAL_FILE_NAME +''' TO ''' + 
          DIRECTION + ''''
   FROM   #TFBF;
   
   SET @SQLCMD = @SQLCMD + ';';
   IF @DEBUG = 1 
      PRINT '@SQLCMD : ' + @SQLCMD;
   SET @ERROR = 0;
   BEGIN TRY
      EXEC (@SQLCMD);
   END TRY
   BEGIN CATCH
      SET @ERROR = 1;
      SET @ERRNB = @ERRNB + 1;
      SET @ERRMSG = 
         N'[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] RESTORE ERROR =&amp;gt; ' 
         + ERROR_MESSAGE();
      RAISERROR('%s', 10, 1) WITH LOG;
   END CATCH
   IF @ERROR = 1
   BEGIN
      IF @CMDSHELL = 0
         EXEC ('EXEC sp_configure ''xp_cmdshell'', 0;RECONFIGURE;');
      IF @ADVCFG = 0
         EXEC ('EXEC sp_configure ''show advanced options'', @;RECONFIGURE;');
      RETURN;
   END;
   SET @FPASSNB = @FPASSNB + 1;
   -- setting the last restored file in the parameter table
   UPDATE msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB
   SET    SDB_LAST_RESTORED_BACKFILE = @FILE,
          SDB_LAST_DATETIME_BACKFILE = @FDATE,
          SDB_LAST_RESTORE_COMMAND = @SQLCMD,
          SDB_LAST_STANDBY_FILE = NULL
   WHERE  SDB_DB_NAME = @DB_NAME;
   -- looking for complementary .TRN files
   SET @PW_COMMAND = REPLACE(REPLACE(@PW_TEMPLATE, 
                                    '###', @PATH_TO_BACKUPS + 
                                           @SERVER_NAME + '_' + @DB_PATTERN),
                             '~~~', 'TRN');
   IF @DEBUG = 1
      PRINT '@PW_COMMAND (2) : ' + @PW_COMMAND;
   
   DELETE FROM @T;
   INSERT INTO @T
   EXEC xp_cmdshell @PW_COMMAND;
   -- cleaning retrieved files
   DELETE FROM @T 
   WHERE L IS NULL OR L NOT LIKE '%.TRN' COLLATE Latin1_General_100_CI_AI;
   -- there is no file to process
   IF NOT EXISTS(SELECT * FROM @T)
   BEGIN
      IF @CMDSHELL = 0
         EXEC ('EXEC sp_configure ''xp_cmdshell'', 0;RECONFIGURE;');
      IF @ADVCFG = 0
         EXEC ('EXEC sp_configure ''show advanced options'', @;RECONFIGURE;');
      RETURN;
   END;
   IF @DEBUG = 1
      SELECT L AS FILE_RETRIEVED_COMPLEMENT FROM @T;
   -- adding file date
   INSERT INTO @F (F_NAME)
   SELECT RIGHT(L, LEN(L) - 20)
   FROM   @T;
   WITH 
   T AS
   (
   SELECT F_ID, REPLACE(REPLACE(SUBSTRING(F_NAME, LEN(F_NAME) -19, 16), 
                        '_', ' '), 'T', '') AS F_PARSE
   FROM   @F)
   UPDATE F
   SET    F_DATE = CAST(LEFT(F_PARSE, 4) + '-' + 
                        SUBSTRING(F_PARSE, 5, 2) + '-' +
                        SUBSTRING(F_PARSE, 7, 3) +
                        SUBSTRING(F_PARSE, 10, 2) + ':' + 
                        SUBSTRING(F_PARSE, 12, 2) + ':' + 
                        RIGHT(F_PARSE, 2) AS DATETIME2(0))
   FROM   T AS U
          JOIN @F AS F
             ON U.F_ID = F.F_ID;
   
   -- deleting backup file oldest thant the last restored one
   DELETE FROM @F
   WHERE  F_DATE &amp;lt;= (SELECT SDB_LAST_DATETIME_BACKFILE 
                     FROM   msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB  
                     WHERE  SDB_DB_NAME = @DB_NAME);
   
   IF @DEBUG = 1    
      SELECT F_NAME AS F_NAME_COMPLEMENT, F_DATE AS F_DATE_COMPLEMENT FROM @F;
   
   -- if no more file, quit
   IF NOT EXISTS(SELECT * FROM @F)
   BEGIN
      IF @CMDSHELL = 0
         EXEC ('EXEC sp_configure ''xp_cmdshell'', 0;RECONFIGURE;');
      IF @ADVCFG = 0
         EXEC ('EXEC sp_configure ''show advanced options'', @;RECONFIGURE;');
      RETURN;
   END;
END --&amp;gt; end of case 1 first restore with.BAK  
    
-- case 2 transactionnal restoration 
WHILE EXISTS(SELECT * FROM @F WHERE F_DONE IS NULL)
BEGIN
   -- searching the first .TRN file
   SELECT @FDATE = MIN(F_DATE) FROM @F WHERE F_DONE IS NULL;
   SELECT @FILE = F_NAME FROM @F WHERE F_DATE = @FDATE;
   UPDATE @F SET F_DONE = 0 WHERE F_NAME = @FILE;
   -- checking if there is only one backup in the backup file
   DELETE FROM @H;
   SET @SQLCMD = N'RESTORE HEADERONLY FROM DISK = ''' + 
                 @PATH_TO_BACKUPS + @FILE + ''';'
   INSERT INTO @H EXEC (@SQLCMD);
   IF @DEBUG = 1 
      SELECT * FROM @H;
   
   IF EXISTS(SELECT NULL FROM @H HAVING COUNT(*) &amp;gt; 1)
   BEGIN
      RAISERROR ('[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] La procédure ne prends pas en compte les fichiers de sauvegarde comportant plus d''une sauvegarde (%s).', 10, 1, @FILE) WITH LOG;
      IF @CMDSHELL = 0
         EXEC ('EXEC sp_configure ''xp_cmdshell'', 0;RECONFIGURE;');
      IF @ADVCFG = 0
         EXEC ('EXEC sp_configure ''show advanced options'', @;RECONFIGURE;');
      RETURN;
   END
   -- there is only one backup in the file
   -- getting path of the files
   DELETE FROM @D;
   SET @SQLCMD = N'RESTORE FILELISTONLY FROM DISK = ''' + 
                 @PATH_TO_BACKUPS + @FILE + ''';'
   INSERT INTO @D EXEC (@SQLCMD);
   IF @DEBUG = 1 
      SELECT * FROM @D;
   -- il y a une redirection ? =&amp;gt; 
   WITH D AS
   (
   SELECT LogicalName AS LOGICAL_FILE_NAME, 
          PhysicalName,
          REVERSE(LEFT(REVERSE(PhysicalName), 
                          CHARINDEX('\', REVERSE(PhysicalName)) - 1)) 
             AS PHYSICAL_FILE_NAME
   FROM   @D
   )
   SELECT LOGICAL_FILE_NAME,
          COALESCE(FST_PHYSICAL_PATH + PHYSICAL_FILE_NAME, PhysicalName) 
             AS DIRECTION
   INTO   #TFBT
   FROM   D
          LEFT OUTER JOIN msdb.LOG_SHIPPEMENT.T_FILE_STORAGE_FST AS S 
             ON D.LOGICAL_FILE_NAME = S.FST_LOGICAL_FILE_NAME
          LEFT OUTER JOIN msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB AS B 
             ON S.SDB_ID = B.SDB_ID AND SDB_DB_NAME = @DB_NAME;
   IF @DEBUG = 1 
      SELECT * FROM #TFBT;
   SET @SQLCMD = N'RESTORE LOG [' + @DB_NAME + '] FROM DISK = ''' + 
                 @PATH_TO_BACKUPS + @FILE + ''' WITH NORECOVERY, STATS = 30';
   SELECT @SQLCMD = @SQLCMD + ', MOVE ''' + LOGICAL_FILE_NAME +''' TO ''' + 
                    DIRECTION + ''''
   FROM   #TFBT;
   
   SET @SQLCMD = @SQLCMD + ';';
   IF @DEBUG = 1 
      PRINT '@SQLCMD : ' + @SQLCMD;
   SET @ERROR = 0;
   BEGIN TRY
      EXEC (@SQLCMD);
   END TRY
   BEGIN CATCH
      SET @ERROR = 1;
      SET @ERRNB = @ERRNB + 1;
      SET @ERRMSG = N'[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] RESTORE ERROR =&amp;gt; '
                  + ERROR_MESSAGE();
      RAISERROR('%s', 10, 1) WITH LOG;
   END CATCH
   IF @ERROR = 1
   BEGIN
      IF @CMDSHELL = 0
         EXEC ('EXEC sp_configure ''xp_cmdshell'', 0;RECONFIGURE;');
      IF @ADVCFG = 0
         EXEC ('EXEC sp_configure ''show advanced options'', @;RECONFIGURE;');
      RETURN;
   END;
   -- setting the last restored file in the parameter table
   UPDATE msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB
   SET    SDB_LAST_RESTORED_BACKFILE = @FILE,
          SDB_LAST_DATETIME_BACKFILE = @FDATE,
          SDB_LAST_RESTORE_COMMAND = @SQLCMD,
          SDB_LAST_STANDBY_FILE = NULL
   WHERE  SDB_DB_NAME = @DB_NAME;
   SET @FPASSNB = @FPASSNB + 1;
   UPDATE @F SET F_DONE = 1 WHERE F_NAME = @FILE;
   DROP TABLE #TFBT;
END
IF @CMDSHELL = 0
   EXEC ('EXEC sp_configure ''xp_cmdshell'', 0;RECONFIGURE;');
IF @ADVCFG = 0
   EXEC ('EXEC sp_configure ''show advanced options'', @;RECONFIGURE;');
PRINT 'Files restored : ' + CAST(@FPASSNB AS VARCHAR(16));
PRINT '*** END OF LOG SHIPPING PROCESS FOR DATABASE ' + @DB_NAME + ' ***';
IF @ERROR = 1
   RAISERROR('Le processus de "log shipping" a rencontré des erreurs, Lisez les entrées du journal d''erreur débutant par "[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] RESTORE ERROR =&amp;gt; "', 16, 1)
GO
--===========================================================================--
-- mise en place de finalisation du log shipping                             --
--===========================================================================--
CREATE PROCEDURE LOG_SHIPPEMENT.P_FORCE_RECOVERY 
   @DB_NAME     sysname
AS
/******************************************************************************
* MODULE  : LOG_SHIPPEMENT                                                    *
* NATURE  : PROCEDURE                                                         *
* OBJECT  : msdb.LOG_SHIPPEMENT.P_FORCE_RECOVERY                              *
* CREATE  : 2019-01610                                                        *
* AUTHOR  : Frederic Brouard (FBD)                                            *
* VERSION : 1                                                                 *
* SYSTEM  : No                                                                *
* VALID   : 2017-2012                                                         *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
* expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *
*******************************************************************************
* PURPOSE : redo the last backup in RECOVERY mode to set the db as read/write *
*******************************************************************************
* INPUTS  :                                                                   *
*    @DB_NAME the name of the database that will be finally active            *
*******************************************************************************
* EXAMPLE :                                                                   *
*    EXEC msdb.LOG_SHIPPEMENT.P_FORCE_RECOVERY 'DB_test';                     *
*******************************************************************************
* FUTUR   :                                                                   *
*    1) add parameter @FORCE_LAST (BIT) to restore last retained backups      *
*******************************************************************************
* IMPROVE :                                                                   *
*******************************************************************************
* BUGFIX  :                                                                   *
******************************************************************************/
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(max);
SELECT @SQL = SDB_LAST_RESTORE_COMMAND
FROM   msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB
WHERE  SDB_DB_NAME = @DB_NAME;
SET  @SQL = REPLACE(@SQL, ' NORECOVERY,', '');
EXEC (@SQL);
GO
--===========================================================================--
-- passage au mode lecture seule de la base log shipped                      --
--===========================================================================--
 
CREATE PROCEDURE LOG_SHIPPEMENT.P_FORCE_STANDBY 
   @DB_NAME     sysname
AS
/******************************************************************************
* MODULE  : LOG_SHIPPEMENT                                                    *
* NATURE  : PROCEDURE                                                         *
* OBJECT  : msdb.LOG_SHIPPEMENT.P_FORCE_STANDBY                               *
* CREATE  : 2019-01610                                                        *
* AUTHOR  : Frederic Brouard (FBD)                                            *
* VERSION : 1                                                                 *
* SYSTEM  : No                                                                *
* VALID   : 2017-2012                                                         *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
* expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *
*******************************************************************************
* PURPOSE : redo the last backup in STANDBY and generate the standby file     *
*******************************************************************************
* INPUTS  :                                                                   *
*    @DB_NAME the name of the database that will be finally readable          *
*******************************************************************************
* EXAMPLE :                                                                   *
*    EXEC msdb.LOG_SHIPPEMENT.P_FORCE_STANDBY 'DB_test';                      *
*******************************************************************************
* IMPROVE :                                                                   *
*******************************************************************************
* BUGFIX  :                                                                   *
******************************************************************************/
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(max), 
        @FILENAME NVARCHAR(512), 
        @STANDBY NVARCHAR(1024);
SELECT @SQL = SDB_LAST_RESTORE_COMMAND
FROM   msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB
WHERE  SDB_DB_NAME = @DB_NAME;
SELECT @STANDBY = SDB_PATH_TO_BACKUPS ,
       @FILENAME = SDB_SOURCE_SERVER + '_' + 
                   SDB_SOURCE_DATABASE + '_' + 
                   REPLACE(REPLACE(CAST(CAST(GETDATE() AS DATETIME2(0)) 
                                   AS CHAR(19)), ':', ''), ' ', 'T') +
                   '_UNDO.BAK' 
FROM   msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB
WHERE  SDB_DB_NAME = @DB_NAME;
SET @STANDBY = @STANDBY + @FILENAME;
SET  @SQL = REPLACE(@SQL, ' NORECOVERY,', ' STANDBY=''' + @STANDBY + ''',' );
BEGIN TRY
   EXEC (@SQL);
   UPDATE msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB
   SET    SDB_LAST_STANDBY_FILE = @FILENAME
   FROM   msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB
   WHERE  SDB_DB_NAME = @DB_NAME;
END TRY
BEGIN CATCH
   THROW;
END CATCH
GO
CREATE PROCEDURE LOG_SHIPPEMENT.P_FORCE_LOGSHIP 
   @DB_NAME     sysname
AS
/******************************************************************************
* MODULE  : LOG_SHIPPEMENT                                                    *
* NATURE  : PROCEDURE                                                         *
* OBJECT  : msdb.LOG_SHIPPEMENT.P_FORCE_LOGSHIP                               *
* CREATE  : 2019-01610                                                        *
* AUTHOR  : Frederic Brouard (FBD)                                            *
* VERSION : 1                                                                 *
* SYSTEM  : No                                                                *
* VALID   : 2017-2012                                                         *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
* expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *
*******************************************************************************
* PURPOSE : reset the database in NORECOVERY mode to restart log shippement   *
*******************************************************************************
* INPUTS  :                                                                   *
*    @DB_NAME the name of the database that will be reset as log shipped      *
*******************************************************************************
* EXAMPLE :                                                                   *
*    EXEC msdb.LOG_SHIPPEMENT.P_FORCE_LOGSHIP 'DB_test';                      *
*******************************************************************************
* IMPROVE :                                                                   *
*******************************************************************************
* BUGFIX  :                                                                   *
******************************************************************************/
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(max);
SELECT @SQL = SDB_LAST_RESTORE_COMMAND
FROM   msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB
WHERE  SDB_DB_NAME = @DB_NAME;
BEGIN TRY
   EXEC (@SQL);
   UPDATE msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB
   SET    SDB_LAST_STANDBY_FILE = NULL
   FROM   msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB
   WHERE  SDB_DB_NAME = @DB_NAME;
END TRY
BEGIN CATCH
   THROW;
END CATCH
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

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

CREATE PROCEDURE LOG_SHIPPEMENT.P_DO_RESTORE

@DB_NAME sysname,

@DEBUG BIT = 0

/******************************************************************************

* MODULE : LOG_SHIPPEMENT *

* NATURE : PROCEDURE *

* OBJECT : msdb.LOG_SHIPPEMENT.P_DO_RESTORE *

* CREATE : 2019-01610 *

* AUTHOR : Frederic Brouard (FBD) *

* VERSION : 1 *

* SYSTEM : No *

* VALID : 2016-2017 without correction, *

* 2012-2014 with the following corrections : *

* in DECLARE @H TABLE, do not specify the following columns : *

* KeyAlgorithm nvarchar(32), *

* EncryptorThumbprint varbinary(20), *

* EncryptorType nvarchar(32) *

* in DECLARE @D TABLE, do not specify the following column : *

* SnapshotURL nvarchar(360) *

*******************************************************************************

* Frédéric BROUARD - alias SQLpro - SARL SQL SPOT - SQLpro@sqlspot.com *

* Architecte de données : expertise, audit, conseil, formation, modélisation *

* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *

* blog: http://blog.developpez.com/sqlpro site: http://sqlpro.developpez.com *

* expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *

*******************************************************************************

* PURPOSE : implement log shipping for a database *

*******************************************************************************

* INPUTS : *

* @DB_NAME the name of the database that will be log shipped *

* @DEBUG : if 1 PRINT and SELECT a maximum of steps while executing code *

*******************************************************************************

* EXAMPLE : *

* EXEC msdb.LOG_SHIPPEMENT.P_DO_RESTORE 'DB_test', 1; *

*******************************************************************************

* NOTES : *

* All the errors while executing the procedure, are logged into event log *

* The backup process must produce file with a file name pattern such as : *

* instance_name + _ + database_name +'_' + datetime formated as : *

* Daaaammjj_Thhmmss + file_extension *

* Sample : SERVERNAME_INSTANCE_DB_NAME_D20190106_T231234.BAK *

* in which : *

* the instance SQL server name is "SERVERNAME_INSTANCE" (char "\" =&gt; "_") *

* the database name is DB_NAME *

* and the file must contains only one backup *

* Notice that the backup process is done by msdb._MNT.P_SAUVE_TOUTES_BASE *

* from the source server *

*******************************************************************************

* FUTUR : *

* 1) add parameter @RETENTION TINYINT to retain n log backup files *

* 2) supports differential backups *

*******************************************************************************

* IMPROVE : *

*******************************************************************************

* BUGFIX : *

******************************************************************************/

IF @DEBUG = 0

SET NOCOUNT ON;

DECLARE @MODE CHAR(3),

-- BAK or TRN value depending if database is created or not

@ADVCFG BIT,

-- value of "show advanced options" in sys.configurations

@CMDSHELL BIT,

-- value of "xp_cmdshell" in sys.configurations

@PW_TEMPLATE NVARCHAR(4000)

-- template of PowerShell code to retrieve backup files (pattern above)

= N'powershell.exe -Command " get-childitem ''###*.~~~'' | Select-Object {$_.CreationTime.ToString(''yyyy-MM-ddThh:MM:ss'')+'';''+$_.Name} ',

@PW_COMMAND NVARCHAR(4000),

-- PowerShell command that is the completed template

@PATH_TO_BACKUPS NVARCHAR(512),

-- path to the backup files for this database

@SERVER_NAME sysname,

-- corrected name of the SQL source instance used to tag the backup file name

@DB_PATTERN sysname,

-- corrected name of the database source used to tag the backup file name

@FILE NVARCHAR(256),

-- one complete backup file name to process

@FDATE DATETIME2(0),

-- date of the backup file

@SQLCMD NVARCHAR(max),

-- SQL dynamic command

@ERROR BIT,

-- an error occurred while restoring

@ERRNB SMALLINT = 0,

-- number of error while applying restore command

@ERRMSG NVARCHAR(1024),

-- error messages concatened

@FPASSNB SMALLINT = 0;

-- number of passed files

PRINT '*** BEGIN OF LOG SHIPPING PROCESS FOR DATABASE ' + @DB_NAME + ' ***';

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

-- retrieving parameters --

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

SELECT @PATH_TO_BACKUPS = SDB_PATH_TO_BACKUPS,

@SERVER_NAME = SDB_SOURCE_SERVER,

@DB_PATTERN = SDB_SOURCE_DATABASE

FROM msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB

WHERE SDB_DB_NAME = @DB_NAME;

IF @DEBUG = 1

BEGIN

PRINT '@PATH_TO_BACKUPS : ' + @PATH_TO_BACKUPS;

PRINT '@SERVER_NAME : ' + @SERVER_NAME;

PRINT '@DB_PATTERN : ' + @DB_PATTERN;

END;

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

-- table decarations --

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

DECLARE @P TABLE

-- used to test if directory path exists

(

FILE_EXISTS BIT,

IS_DIR BIT,

PARENT_DIR BIT

);

DECLARE @T TABLE

-- used to store list of files

(

L NVARCHAR(1000)

);

DECLARE @F TABLE

-- used to store files to process

(

F_ID INT IDENTITY PRIMARY KEY,

F_DATE DATETIME2(0),

F_NAME VARCHAR(256),

F_DONE BIT);

DECLARE @H TABLE

-- used to get backup file header

(

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,

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,

containment tinyint,

KeyAlgorithm nvarchar(32),

EncryptorThumbprint varbinary(20),

EncryptorType nvarchar(32)

);

DECLARE @D TABLE

-- used to get backup file list

(

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),

SnapshotURL nvarchar(360)

);

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

-- checkings --

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

-- path terminated by '\' ?, if not, add it.

SET @PATH_TO_BACKUPS = @PATH_TO_BACKUPS +

CASE RIGHT(@PATH_TO_BACKUPS, 1)

WHEN '\' THEN ''

ELSE '\'

END;

-- directory exists and accessible ?

INSERT INTO @P

EXEC xp_fileexist @PATH_TO_BACKUPS;

IF NOT EXISTS(SELECT * FROM @P WHERE IS_DIR = 1)

BEGIN

RAISERROR('[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] Le répertoire %s n''existe pas ou bien n''est pas accessible.', 16, 1, @PATH_TO_BACKUPS) WITH LOG;

RETURN;

END;

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

-- which state has the database ? --

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

-- database is actuelly exploited. No needs for log shippement

IF EXISTS(SELECT *

FROM sys.databases

WHERE name = @DB_NAME AND

state = 0)

BEGIN

RAISERROR('[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] La base %s est actuellement en production. Impossible de la mettre en récupération de journaux ("log shipping").', 10, 1, @DB_NAME) WITH LOG;

RETURN;

END;

-- database has an incompatible state. Do not process

IF EXISTS(SELECT *

FROM sys.databases

WHERE name = @DB_NAME AND

state &gt; 1)

BEGIN

RAISERROR('[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] La base %s est actuellement dans un mode incompatible pour la mettre en récupération de journaux ("log shipping").', 10, 1, @DB_NAME) WITH LOG;

RETURN;

END;

-- database does not exists, needs to be created

IF NOT EXISTS(SELECT *

FROM sys.databases

WHERE name = @DB_NAME)

SET @MODE = 'BAK'

ELSE

SET @MODE = 'TRN';

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

-- retrieving database backup files --

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

SET @PW_COMMAND =

REPLACE(REPLACE(@PW_TEMPLATE, '###', @PATH_TO_BACKUPS +

@SERVER_NAME + '_' + @DB_PATTERN), '~~~', @MODE);

IF @DEBUG = 1

PRINT '@PW_COMMAND : ' + @PW_COMMAND;

SELECT @ADVCFG = CAST(value_in_use AS BIT)

FROM sys.configurations

WHERE name = 'show advanced options';

IF @ADVCFG = 0

EXEC ('EXEC sp_configure ''show advanced options'', 1;RECONFIGURE;');

SELECT @CMDSHELL = CAST(value_in_use AS BIT)

FROM sys.configurations

WHERE name = 'xp_cmdshell';

IF @CMDSHELL = 0

EXEC ('EXEC sp_configure ''xp_cmdshell'', 1;RECONFIGURE;');

INSERT INTO @T

EXEC xp_cmdshell @PW_COMMAND;

-- cleaning retrieved files

DELETE FROM @T

WHERE L IS NULL OR L NOT LIKE '%.' + @MODE COLLATE Latin1_General_100_CI_AI;

IF @DEBUG = 1

SELECT L AS FILE_RETRIEVED FROM @T;

-- there is no file to process

IF NOT EXISTS(SELECT * FROM @T)

BEGIN

RAISERROR ('[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] Aucun fichier à traiter pour le log shipping de la base %s.', 10, 1, @DB_NAME) WITH LOG;

IF @CMDSHELL = 0

EXEC ('EXEC sp_configure ''xp_cmdshell'', 0;RECONFIGURE;');

IF @ADVCFG = 0

EXEC ('EXEC sp_configure ''show advanced options'', @;RECONFIGURE;');

RETURN;

END;

-- adding file date

INSERT INTO @F (F_NAME)

SELECT RIGHT(L, LEN(L) - 20)

FROM @T;

WITH

T AS

(

SELECT F_ID, REPLACE(REPLACE(SUBSTRING(F_NAME, LEN(F_NAME) -19, 16),

'_', ' '), 'T', '') AS F_PARSE

FROM @F)

UPDATE F

SET F_DATE = CAST(LEFT(F_PARSE, 4) + '-' +

SUBSTRING(F_PARSE, 5, 2) + '-' +

SUBSTRING(F_PARSE, 7, 3) +

SUBSTRING(F_PARSE, 10, 2) + ':' +

SUBSTRING(F_PARSE, 12, 2) + ':' +

RIGHT(F_PARSE, 2) AS DATETIME2(0))

FROM T AS U

JOIN @F AS F

ON U.F_ID = F.F_ID;

IF @DEBUG = 1

SELECT * FROM @F;

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

-- first case : database does not exists, so needs to be created --

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

IF @MODE = 'BAK'

BEGIN

-- searching the most recent .BAK file

SELECT @FDATE = MAX(F_DATE) FROM @F;

SELECT @FILE = F_NAME FROM @F WHERE F_DATE = @FDATE;

-- checking if there is only one baclup in the backup file

SET @SQLCMD = N'RESTORE HEADERONLY FROM DISK = ''' +

@PATH_TO_BACKUPS + @FILE + ''';'

INSERT INTO @H EXEC (@SQLCMD);

IF @DEBUG = 1

SELECT * FROM @H;

IF EXISTS(SELECT NULL FROM @H HAVING COUNT(*) &gt; 1)

BEGIN

RAISERROR ('[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] La procédure ne prends pas en compte les fichiers de sauvegarde comportant plus d''une sauvegarde (%s).', 10, 1, @FILE) WITH LOG;

IF @CMDSHELL = 0

EXEC ('EXEC sp_configure ''xp_cmdshell'', 0;RECONFIGURE;');

IF @ADVCFG = 0

EXEC ('EXEC sp_configure ''show advanced options'', @;RECONFIGURE;');

RETURN;

END;

-- there is only one backup in the file

-- getting path of the files

SET @SQLCMD = N'RESTORE FILELISTONLY FROM DISK = ''' +

@PATH_TO_BACKUPS + @FILE + ''';'

INSERT INTO @D EXEC (@SQLCMD);

IF @DEBUG = 1

SELECT * FROM @D;

-- il y a une redirection ? =&gt;

WITH D AS

(

SELECT LogicalName AS LOGICAL_FILE_NAME,

PhysicalName,

REVERSE(LEFT(REVERSE(PhysicalName),

CHARINDEX('\', REVERSE(PhysicalName)) - 1))

AS PHYSICAL_FILE_NAME

FROM @D

)

SELECT LOGICAL_FILE_NAME,

COALESCE(FST_PHYSICAL_PATH + PHYSICAL_FILE_NAME, PhysicalName)

AS DIRECTION

INTO #TFBF

FROM D

LEFT OUTER JOIN msdb.LOG_SHIPPEMENT.T_FILE_STORAGE_FST AS S

ON D.LOGICAL_FILE_NAME = S.FST_LOGICAL_FILE_NAME

LEFT OUTER JOIN msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB AS B

ON S.SDB_ID = B.SDB_ID AND SDB_DB_NAME = @DB_NAME;

IF @DEBUG = 1

SELECT * FROM #TFBF;

SET @SQLCMD = N'RESTORE DATABASE [' + @DB_NAME + '] FROM DISK = ''' +

@PATH_TO_BACKUPS + @FILE + ''' WITH NORECOVERY, STATS = 10';

SELECT @SQLCMD = @SQLCMD + ', MOVE ''' + LOGICAL_FILE_NAME +''' TO ''' +

DIRECTION + ''''

FROM #TFBF;

SET @SQLCMD = @SQLCMD + ';';

IF @DEBUG = 1

PRINT '@SQLCMD : ' + @SQLCMD;

SET @ERROR = 0;

BEGIN TRY

EXEC (@SQLCMD);

END TRY

BEGIN CATCH

SET @ERROR = 1;

SET @ERRNB = @ERRNB + 1;

SET @ERRMSG =

N'[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] RESTORE ERROR =&gt; '

+ ERROR_MESSAGE();

RAISERROR('%s', 10, 1) WITH LOG;

END CATCH

IF @ERROR = 1

BEGIN

IF @CMDSHELL = 0

EXEC ('EXEC sp_configure ''xp_cmdshell'', 0;RECONFIGURE;');

IF @ADVCFG = 0

EXEC ('EXEC sp_configure ''show advanced options'', @;RECONFIGURE;');

RETURN;

END;

SET @FPASSNB = @FPASSNB + 1;

-- setting the last restored file in the parameter table

UPDATE msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB

SET SDB_LAST_RESTORED_BACKFILE = @FILE,

SDB_LAST_DATETIME_BACKFILE = @FDATE,

SDB_LAST_RESTORE_COMMAND = @SQLCMD,

SDB_LAST_STANDBY_FILE = NULL

WHERE SDB_DB_NAME = @DB_NAME;

-- looking for complementary .TRN files

SET @PW_COMMAND = REPLACE(REPLACE(@PW_TEMPLATE,

'###', @PATH_TO_BACKUPS +

@SERVER_NAME + '_' + @DB_PATTERN),

'~~~', 'TRN');

IF @DEBUG = 1

PRINT '@PW_COMMAND (2) : ' + @PW_COMMAND;

DELETE FROM @T;

INSERT INTO @T

EXEC xp_cmdshell @PW_COMMAND;

-- cleaning retrieved files

DELETE FROM @T

WHERE L IS NULL OR L NOT LIKE '%.TRN' COLLATE Latin1_General_100_CI_AI;

-- there is no file to process

IF NOT EXISTS(SELECT * FROM @T)

BEGIN

IF @CMDSHELL = 0

EXEC ('EXEC sp_configure ''xp_cmdshell'', 0;RECONFIGURE;');

IF @ADVCFG = 0

EXEC ('EXEC sp_configure ''show advanced options'', @;RECONFIGURE;');

RETURN;

END;

IF @DEBUG = 1

SELECT L AS FILE_RETRIEVED_COMPLEMENT FROM @T;

-- adding file date

INSERT INTO @F (F_NAME)

SELECT RIGHT(L, LEN(L) - 20)

FROM @T;

WITH

T AS

(

SELECT F_ID, REPLACE(REPLACE(SUBSTRING(F_NAME, LEN(F_NAME) -19, 16),

'_', ' '), 'T', '') AS F_PARSE

FROM @F)

UPDATE F

SET F_DATE = CAST(LEFT(F_PARSE, 4) + '-' +

SUBSTRING(F_PARSE, 5, 2) + '-' +

SUBSTRING(F_PARSE, 7, 3) +

SUBSTRING(F_PARSE, 10, 2) + ':' +

SUBSTRING(F_PARSE, 12, 2) + ':' +

RIGHT(F_PARSE, 2) AS DATETIME2(0))

FROM T AS U

JOIN @F AS F

ON U.F_ID = F.F_ID;

-- deleting backup file oldest thant the last restored one

DELETE FROM @F

WHERE F_DATE &lt;= (SELECT SDB_LAST_DATETIME_BACKFILE

FROM msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB

WHERE SDB_DB_NAME = @DB_NAME);

IF @DEBUG = 1

SELECT F_NAME AS F_NAME_COMPLEMENT, F_DATE AS F_DATE_COMPLEMENT FROM @F;

-- if no more file, quit

IF NOT EXISTS(SELECT * FROM @F)

BEGIN

IF @CMDSHELL = 0

EXEC ('EXEC sp_configure ''xp_cmdshell'', 0;RECONFIGURE;');

IF @ADVCFG = 0

EXEC ('EXEC sp_configure ''show advanced options'', @;RECONFIGURE;');

RETURN;

END;

END --&gt; end of case 1 first restore with.BAK

-- case 2 transactionnal restoration

WHILE EXISTS(SELECT * FROM @F WHERE F_DONE IS NULL)

BEGIN

-- searching the first .TRN file

SELECT @FDATE = MIN(F_DATE) FROM @F WHERE F_DONE IS NULL;

SELECT @FILE = F_NAME FROM @F WHERE F_DATE = @FDATE;

UPDATE @F SET F_DONE = 0 WHERE F_NAME = @FILE;

-- checking if there is only one backup in the backup file

DELETE FROM @H;

SET @SQLCMD = N'RESTORE HEADERONLY FROM DISK = ''' +

@PATH_TO_BACKUPS + @FILE + ''';'

INSERT INTO @H EXEC (@SQLCMD);

IF @DEBUG = 1

SELECT * FROM @H;

IF EXISTS(SELECT NULL FROM @H HAVING COUNT(*) &gt; 1)

BEGIN

RAISERROR ('[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] La procédure ne prends pas en compte les fichiers de sauvegarde comportant plus d''une sauvegarde (%s).', 10, 1, @FILE) WITH LOG;

IF @CMDSHELL = 0

EXEC ('EXEC sp_configure ''xp_cmdshell'', 0;RECONFIGURE;');

IF @ADVCFG = 0

EXEC ('EXEC sp_configure ''show advanced options'', @;RECONFIGURE;');

RETURN;

END

-- there is only one backup in the file

-- getting path of the files

DELETE FROM @D;

SET @SQLCMD = N'RESTORE FILELISTONLY FROM DISK = ''' +

@PATH_TO_BACKUPS + @FILE + ''';'

INSERT INTO @D EXEC (@SQLCMD);

IF @DEBUG = 1

SELECT * FROM @D;

-- il y a une redirection ? =&gt;

WITH D AS

(

SELECT LogicalName AS LOGICAL_FILE_NAME,

PhysicalName,

REVERSE(LEFT(REVERSE(PhysicalName),

CHARINDEX('\', REVERSE(PhysicalName)) - 1))

AS PHYSICAL_FILE_NAME

FROM @D

)

SELECT LOGICAL_FILE_NAME,

COALESCE(FST_PHYSICAL_PATH + PHYSICAL_FILE_NAME, PhysicalName)

AS DIRECTION

INTO #TFBT

FROM D

LEFT OUTER JOIN msdb.LOG_SHIPPEMENT.T_FILE_STORAGE_FST AS S

ON D.LOGICAL_FILE_NAME = S.FST_LOGICAL_FILE_NAME

LEFT OUTER JOIN msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB AS B

ON S.SDB_ID = B.SDB_ID AND SDB_DB_NAME = @DB_NAME;

IF @DEBUG = 1

SELECT * FROM #TFBT;

SET @SQLCMD = N'RESTORE LOG [' + @DB_NAME + '] FROM DISK = ''' +

@PATH_TO_BACKUPS + @FILE + ''' WITH NORECOVERY, STATS = 30';

SELECT @SQLCMD = @SQLCMD + ', MOVE ''' + LOGICAL_FILE_NAME +''' TO ''' +

DIRECTION + ''''

FROM #TFBT;

SET @SQLCMD = @SQLCMD + ';';

IF @DEBUG = 1

PRINT '@SQLCMD : ' + @SQLCMD;

SET @ERROR = 0;

BEGIN TRY

EXEC (@SQLCMD);

END TRY

BEGIN CATCH

SET @ERROR = 1;

SET @ERRNB = @ERRNB + 1;

SET @ERRMSG = N'[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] RESTORE ERROR =&gt; '

+ ERROR_MESSAGE();

RAISERROR('%s', 10, 1) WITH LOG;

END CATCH

IF @ERROR = 1

BEGIN

IF @CMDSHELL = 0

EXEC ('EXEC sp_configure ''xp_cmdshell'', 0;RECONFIGURE;');

IF @ADVCFG = 0

EXEC ('EXEC sp_configure ''show advanced options'', @;RECONFIGURE;');

RETURN;

END;

-- setting the last restored file in the parameter table

UPDATE msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB

SET SDB_LAST_RESTORED_BACKFILE = @FILE,

SDB_LAST_DATETIME_BACKFILE = @FDATE,

SDB_LAST_RESTORE_COMMAND = @SQLCMD,

SDB_LAST_STANDBY_FILE = NULL

WHERE SDB_DB_NAME = @DB_NAME;

SET @FPASSNB = @FPASSNB + 1;

UPDATE @F SET F_DONE = 1 WHERE F_NAME = @FILE;

DROP TABLE #TFBT;

END

IF @CMDSHELL = 0

EXEC ('EXEC sp_configure ''xp_cmdshell'', 0;RECONFIGURE;');

IF @ADVCFG = 0

EXEC ('EXEC sp_configure ''show advanced options'', @;RECONFIGURE;');

PRINT 'Files restored : ' + CAST(@FPASSNB AS VARCHAR(16));

PRINT '*** END OF LOG SHIPPING PROCESS FOR DATABASE ' + @DB_NAME + ' ***';

IF @ERROR = 1

RAISERROR('Le processus de "log shipping" a rencontré des erreurs, Lisez les entrées du journal d''erreur débutant par "[PROCEDURE : LOG_SHIPPEMENT.P_DO_RESTORE] RESTORE ERROR =&gt; "', 16, 1)

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

-- mise en place de finalisation du log shipping --

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

CREATE PROCEDURE LOG_SHIPPEMENT.P_FORCE_RECOVERY

@DB_NAME sysname

/******************************************************************************

* MODULE : LOG_SHIPPEMENT *

* NATURE : PROCEDURE *

* OBJECT : msdb.LOG_SHIPPEMENT.P_FORCE_RECOVERY *

* CREATE : 2019-01610 *

* AUTHOR : Frederic Brouard (FBD) *

* VERSION : 1 *

* SYSTEM : No *

* VALID : 2017-2012 *

*******************************************************************************

* Frédéric BROUARD - alias SQLpro - SARL SQL SPOT - SQLpro@sqlspot.com *

* Architecte de données : expertise, audit, conseil, formation, modélisation *

* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *

* blog: http://blog.developpez.com/sqlpro site: http://sqlpro.developpez.com *

* expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *

*******************************************************************************

* PURPOSE : redo the last backup in RECOVERY mode to set the db as read/write *

*******************************************************************************

* INPUTS : *

* @DB_NAME the name of the database that will be finally active *

*******************************************************************************

* EXAMPLE : *

* EXEC msdb.LOG_SHIPPEMENT.P_FORCE_RECOVERY 'DB_test'; *

*******************************************************************************

* FUTUR : *

* 1) add parameter @FORCE_LAST (BIT) to restore last retained backups *

*******************************************************************************

* IMPROVE : *

*******************************************************************************

* BUGFIX : *

******************************************************************************/

SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(max);

SELECT @SQL = SDB_LAST_RESTORE_COMMAND

FROM msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB

WHERE SDB_DB_NAME = @DB_NAME;

SET @SQL = REPLACE(@SQL, ' NORECOVERY,', '');

EXEC (@SQL);

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

-- passage au mode lecture seule de la base log shipped --

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

CREATE PROCEDURE LOG_SHIPPEMENT.P_FORCE_STANDBY

@DB_NAME sysname

/******************************************************************************

* MODULE : LOG_SHIPPEMENT *

* NATURE : PROCEDURE *

* OBJECT : msdb.LOG_SHIPPEMENT.P_FORCE_STANDBY *

* CREATE : 2019-01610 *

* AUTHOR : Frederic Brouard (FBD) *

* VERSION : 1 *

* SYSTEM : No *

* VALID : 2017-2012 *

*******************************************************************************

* Frédéric BROUARD - alias SQLpro - SARL SQL SPOT - SQLpro@sqlspot.com *

* Architecte de données : expertise, audit, conseil, formation, modélisation *

* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *

* blog: http://blog.developpez.com/sqlpro site: http://sqlpro.developpez.com *

* expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *

*******************************************************************************

* PURPOSE : redo the last backup in STANDBY and generate the standby file *

*******************************************************************************

* INPUTS : *

* @DB_NAME the name of the database that will be finally readable *

*******************************************************************************

* EXAMPLE : *

* EXEC msdb.LOG_SHIPPEMENT.P_FORCE_STANDBY 'DB_test'; *

*******************************************************************************

* IMPROVE : *

*******************************************************************************

* BUGFIX : *

******************************************************************************/

SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(max),

@FILENAME NVARCHAR(512),

@STANDBY NVARCHAR(1024);

SELECT @SQL = SDB_LAST_RESTORE_COMMAND

FROM msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB

WHERE SDB_DB_NAME = @DB_NAME;

SELECT @STANDBY = SDB_PATH_TO_BACKUPS ,

@FILENAME = SDB_SOURCE_SERVER + '_' +

SDB_SOURCE_DATABASE + '_' +

REPLACE(REPLACE(CAST(CAST(GETDATE() AS DATETIME2(0))

AS CHAR(19)), ':', ''), ' ', 'T') +

'_UNDO.BAK'

FROM msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB

WHERE SDB_DB_NAME = @DB_NAME;

SET @STANDBY = @STANDBY + @FILENAME;

SET @SQL = REPLACE(@SQL, ' NORECOVERY,', ' STANDBY=''' + @STANDBY + ''',' );

BEGIN TRY

EXEC (@SQL);

UPDATE msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB

SET SDB_LAST_STANDBY_FILE = @FILENAME

FROM msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB

WHERE SDB_DB_NAME = @DB_NAME;

END TRY

BEGIN CATCH

THROW;

END CATCH

CREATE PROCEDURE LOG_SHIPPEMENT.P_FORCE_LOGSHIP

@DB_NAME sysname

/******************************************************************************

* MODULE : LOG_SHIPPEMENT *

* NATURE : PROCEDURE *

* OBJECT : msdb.LOG_SHIPPEMENT.P_FORCE_LOGSHIP *

* CREATE : 2019-01610 *

* AUTHOR : Frederic Brouard (FBD) *

* VERSION : 1 *

* SYSTEM : No *

* VALID : 2017-2012 *

*******************************************************************************

* Frédéric BROUARD - alias SQLpro - SARL SQL SPOT - SQLpro@sqlspot.com *

* Architecte de données : expertise, audit, conseil, formation, modélisation *

* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *

* blog: http://blog.developpez.com/sqlpro site: http://sqlpro.developpez.com *

* expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *

*******************************************************************************

* PURPOSE : reset the database in NORECOVERY mode to restart log shippement *

*******************************************************************************

* INPUTS : *

* @DB_NAME the name of the database that will be reset as log shipped *

*******************************************************************************

* EXAMPLE : *

* EXEC msdb.LOG_SHIPPEMENT.P_FORCE_LOGSHIP 'DB_test'; *

*******************************************************************************

* IMPROVE : *

*******************************************************************************

* BUGFIX : *

******************************************************************************/

SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(max);

SELECT @SQL = SDB_LAST_RESTORE_COMMAND

FROM msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB

WHERE SDB_DB_NAME = @DB_NAME;

BEGIN TRY

EXEC (@SQL);

UPDATE msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB

SET SDB_LAST_STANDBY_FILE = NULL

FROM msdb.LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB

WHERE SDB_DB_NAME = @DB_NAME;

END TRY

BEGIN CATCH

THROW;

END CATCH

La procédure LOG_SHIPPEMENT.P_DO_RESTORE effectue automatiquement toutes les tâches continues du « log shippement » :

si la base n’existe pas, recherche la dernière sauvegarde complète, la restaure et applique la restauration de toutes les sauvegardes des journaux de transactions complémentaires.
si la base existe déjà en mode de restauration, la complète avec toutes les sauvegardes transactionnelles complémentaires

Elle doit être planifiée à la même fréquence que les sauvegardes transactionnelles (par exemple toutes les 10 minutes)

Les procédures suivantes, ont les fonctions indiquées :

LOG_SHIPPEMENT.P_FORCE_STANDBY : place la base passée en argument, en mode « veille » avec lecture seule (STANDBY) lorsqu’elle est en état de restauration. À n’utiliser que temporairement pour lire les données.
LOG_SHIPPEMENT.P_FORCE_LOGSHIP : replace la base passée en argument, en mode « restauration » pour appliquer à nouveau le processus de « log shippement », si la base est dans l’état « veille » avec lecture seule (STANDBY).
LOG_SHIPPEMENT.P_FORCE_RECOVERY : place la base passée en argument, en mode de production lorsqu’elle est en état de restauration.

La dernière procédure est celle à utiliser pour remettre la ou les base(s) en état de produire afin d’assurer la reprise d’activité.

Lien de téléchargement du Log Shipping automatisé

Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL * Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com * * le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ * * Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans * * Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation * * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

Un processus de « Log Shipping » automatisé dans le cadre d’un PRA

La mécanique

Quelques rappels

Quelques subtilités

La solution d’automatisation

Côté source

Côté cible

Articles récents

Commentaires récents

Archives

Catégories

Méta