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

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';

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;
  • 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;

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;

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;

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';

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;

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 <> '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' => complète                                                 *             
*          'DIFF' => différentielle                                           * 
*          'TRAN' => 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) <> 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
--> 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
--> 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

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

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

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 "\" => "_")   *
*   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 > 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(*) > 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 ? => 
   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 => ' 
         + 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 <= (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 --> 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(*) > 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 ? => 
   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 => '
                  + 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 => "', 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

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

 

 

Ce contenu a été publié dans Procédure de maintenance, Procédures MS SQL Server, scripts SQL, SQL Server, Uncategorized. Vous pouvez le mettre en favoris avec ce permalien.