/* defaire USE msdb; DROP TABLE LOG_SHIPPEMENT.T_FILE_STORAGE_FST; DROP TABLE LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB; DROP PROCEDURE LOG_SHIPPEMENT.P_DO_RESTORE; DROP PROCEDURE LOG_SHIPPEMENT.P_FORCE_RECOVERY; DROP PROCEDURE LOG_SHIPPEMENT.P_FORCE_STANDBY; DROP PROCEDURE LOG_SHIPPEMENT.P_FORCE_LOGSHIP; DROP SCHEMA LOG_SHIPPEMENT; */ --===========================================================================-- -- mise en place des schémas SQL et des tables -- --===========================================================================-- 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 --===========================================================================-- -- mise en place du trigger sur table LOG_SHIPPEMENT.T_SOURCE_DATABASE_SDB -- --===========================================================================-- 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 --===========================================================================-- -- mise en place des procédures de gestion du log shipping -- --===========================================================================-- 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 automatic 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', 0; * ******************************************************************************* * NOTES : * * All the errors while executing the procedure, are logged into event log * * The backup process must produce a backup file with a pattern name such as * * instance_name + _ + database_name +'_' + datetime + file_extension * * and datetime must be formated as : Daaaammjj_Thhmmss * * 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 * ******************************************************************************* * 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 ------------------------------------ -- 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; 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; -------------------------------------- -- retrieving database backup files -- -------------------------------------- -- 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'; 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 : * * add parameter @FORCE_LAST (BIT) to restore last retained backups * ******************************************************************************* * IMPROVE : * ******************************************************************************* * BUGFIX : * ******************************************************************************/ SET NOCOUNT ON; IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DB_NAME) BEGIN RAISERROR ('[PROCEDURE : LOG_SHIPPEMENT.P_FORCE_RECOVERY] La base %s n''existe pas sur ce serveur.', 16, 1, @DB_NAME); RETURN; END IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DB_NAME AND state = 1) BEGIN RAISERROR ('[PROCEDURE : LOG_SHIPPEMENT.P_FORCE_RECOVERY] La base %s n''est pas en état de récupération (restauration) en ne peut donc pas basculer en production.', 16, 1, @DB_NAME); RETURN; END 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; IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DB_NAME) BEGIN RAISERROR ('[PROCEDURE : LOG_SHIPPEMENT.P_FORCE_RECOVERY] La base %s n''existe pas sur ce serveur.', 16, 1, @DB_NAME); RETURN; END IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DB_NAME AND state = 1) BEGIN RAISERROR ('[PROCEDURE : LOG_SHIPPEMENT.P_FORCE_RECOVERY] La base %s n''est pas en état de récupération (restauration) en ne peut donc pas basculer en veille en mode READ ONLY.', 16, 1, @DB_NAME); RETURN; END 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 NORECERY 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; IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DB_NAME) BEGIN RAISERROR ('[PROCEDURE : LOG_SHIPPEMENT.P_FORCE_RECOVERY] La base %s n''existe pas sur ce serveur.', 16, 1, @DB_NAME); RETURN; END IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DB_NAME AND is_in_standby = 1) BEGIN RAISERROR ('[PROCEDURE : LOG_SHIPPEMENT.P_FORCE_RECOVERY] La base %s n''est pas en état de récupération (restauration) en ne peut donc pas basculer en veille en mode READ ONLY.', 16, 1, @DB_NAME); RETURN; END 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