I had decided to build a manual Logshipping instead of using the built-in MS LogShipping for my better control in terms of current connection controls.
--// Create tbLogShippingControl - I built a table to control the LogShipping
================================================================================
--// Create tbLogShippingControl - I built a table to control the LogShipping
================================================================================
USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbLogShippingControl](
[LogShippingID] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar](60) NULL,
[BackupPathFolder] [varchar](255) NULL,
[BackupFileExtention] [varchar](10) NULL,
[LastFullBackupSetId] [int] NULL,
[FullLogShippingPathControl] [varchar](255) NULL,
[FullBackupPathName] [varchar](400) NULL,
[BackupFileName] [varchar](255) NULL,
[RetriveCounter] [int] NULL,
[ErrorNumber] [int] NULL,
[ErrorMessage] [varchar](400) NULL,
[ApplicationRemoved] [varchar](400) NULL,
[CreatedDateOn] [datetime] NULL,
[isRestored] [char](1) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbLogShippingControl] ADD DEFAULT ('N') FOR [isRestored]
GO
--// Create sp_DBA_CreateLogShippingTrace - To insert the results (Log)
================================================================================
USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[sp_DBA_CreateLogShippingTrace] (
@DatabaseName varchar(60),
@BackupPathFolder varchar(255),
@BackupFileExtention varchar(10),
@LastFullBackupSetId int,
@FullLogShippingPathControl varchar(255),
@FullBackupPathName varchar(400),
@BackupFileName varchar(255),
@RetriveCounter Int,
@ErrorNumber int,
@ErrorMessage varchar(400),
@IsRestored char(1),
@ApplicationRemoved varchar(400),
@CreatedDateOn dateTime )
As
/*****************************************************************************
Name: dbo.sp_DBA_CreateLogShippingTrace
Registers the logshipping result
******************************************************************************
Change History
******************************************************************************
Date: Author: Description:
----- ------- -------------------
11 JuL 2018 Leandro Buffone Created Initial Version
******************************************************************************
Usage:
exec dbo.spCreateLogShippingTrace @Setting = @DatabaseName = ''
@BackupPathFolder = ''
@BackupFileExtention = ''
@LastFullBackupSetId = ''
@FullLogShippingPathControl = ''
@FullBackupPathName = ''
@BackupFileName = ''
@RetriveCounter = ''
@ErrorNumber = ''
@ErrorMessage = '',
@IsRestored = 'N',
@ApplicationRemoved = '',
@CreatedDateOn = ''
******************************************************************************/
Insert tbLogShippingControl (
DatabaseName,
BackupPathFolder,
BackupFileExtention,
LastFullBackupSetId,
FullLogShippingPathControl,
FullBackupPathName,
BackupFileName,
RetriveCounter,
ErrorNumber,
ErrorMessage,
IsRestored,
ApplicationRemoved,
CreatedDateOn
)
Select
@DatabaseName,
@BackupPathFolder,
@BackupFileExtention,
@LastFullBackupSetId,
@FullLogShippingPathControl,
@FullBackupPathName,
@BackupFileName,
@RetriveCounter,
@ErrorNumber,
@ErrorMessage,
@IsRestored,
@ApplicationRemoved,
@CreatedDateOn
--// Create sp_DBA_CreateLogShipping - To build and load the LogShipping
================================================================================
USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[sp_DBA_CreateLogShipping]
@DatabaseName varchar(60) = 'DATABASENAME01',
@BackupPathFolder varchar(255) = '\\BackupServerAddress\DATABASENAME01\',
@LogShippingType varchar(4) = null, --// Ex: IND or FULL Or LOG
@BackupFileExtention varchar(10) = 'bak',
@FullLogShippingPathControl varchar(255) = 'D:\SQL\LogShipping\CRM.shp',
@ForceLogLoadingFile varchar(255) = null, --// Ex: 'DATABASENAME01_backup_2018_07_11_091503_8310209.trn'
@DatabaseNewPathDataLocation varchar(255) = null, --// Ex: 'D:\SQL\Data\DATABASENAME01.mdf',
@DatabaseNewPathLogLocation varchar(255) = null, --// Ex: 'D:\SQL\Data\DATABASENAME01.ldf',
@DatabaseNewLogicalDataName varchar(255) = null, --// Ex: 'mscrm',
@DatabaseNewLogicalLogName varchar(255) = null --// Ex: 'mscrm_log'
As
/*****************************************************************************
Name: dbo.sp_DBA_CreateLogShipping
Create and update the transactions in LogShipping
******************************************************************************
Change History
******************************************************************************
Date: Author: Description:
----- ------- -------------------
11 JuL 2018 Leandro Buffone Created Initial Version
******************************************************************************
Usage:
--// Full - Restore - Different Location
exec dbo.sp_DBA_CreateLogShipping @DatabaseName = 'DATABASENAME01',
@BackupPathFolder = '\\BackupServerAddress\DATABASENAME01\',
@LogShippingType = 'FULL',
@BackupFileExtention = 'bak',
@FullLogShippingPathControl = 'D:\SQL\LogShipping\CRM.shp',
@ForceLogLoadingFile = null,
@DatabaseNewPathDataLocation = 'D:\SQL\Data\DATABASENAME01.mdf',
@DatabaseNewPathLogLocation = 'D:\SQL\Data\DATABASENAME01.ldf',
@DatabaseNewLogicalDataName = 'mscrm',
@DatabaseNewLogicalLogName = 'mscrm_log'
--// IND - Force Log Transaction
exec dbo.sp_DBA_CreateLogShipping @DatabaseName = 'DATABASENAME01',
@BackupPathFolder = '\\BackupServerAddress\DATABASENAME01\',
@LogShippingType = 'IND',
@BackupFileExtention = 'trn',
@FullLogShippingPathControl = 'D:\SQL\LogShipping\CRM.shp',
@ForceLogLoadingFile = 'DATABASENAME01_backup_2018_07_17_154501_7817057.trn'
--// LOG - Restore a log transaction
exec dbo.sp_DBA_CreateLogShipping @DatabaseName = 'DATABASENAME01',
@BackupPathFolder = '\\BackupServerAddress\DATABASENAME01\',
@LogShippingType = 'LOG',
@BackupFileExtention = 'trn',
@FullLogShippingPathControl = 'D:\SQL\LogShipping\CRM.shp'
Shows the backup control sources:
select Name from LOGSHIPPING.msdb.dbo.backupset where database_name = 'DATABASENAME01' and type = 'L' and len(Name)>0 and Backup_Set_Id> 17723 and (Select count(1) From tbLogShippingControl c where c.BackupFileName = name and isRestored = 'Y') = 0 order by backup_start_date
Shows the Logshipping logs:
Select * FROM [DBA].[dbo].[tbLogShippingControl] order by [CreatedDateOn] desc
Updated the Log:
update [DBA].[dbo].[tbLogShippingControl] set isRestored = 'Y' where logshippingID < 1997
******************************************************************************/
Declare @LastFullBackupName varchar(150) = '',
@LastFullBackupSetId int = 0,
@FullBackupPathName varchar(400) = '',
@ApplicationRemoved varchar(400) = '',
@RetriveCounter Int = 0,
@CreatedDateOn DateTime,
@ErrorNumber Int = 0, -- Success
@ErrorMessage varchar(400) = '',
@BackupFileName varchar(255) = '',
@IsRestored char(1) = 'N'
If @DatabaseName like 'DATABASENAME02%' Begin
--// Load the source backup historical from the main host
Select top 1
@LastFullBackupName = name,
@LastFullBackupSetId = Backup_Set_Id
from [LOGSHIPPINGCG2].msdb.dbo.backupset
where database_name = @DatabaseName
and type = 'D'
order by backup_start_date desc
If @LogShippingType = 'IND' Begin --// Individual and Forced Log Loading
Set @FullBackupPathName = concat(@BackupPathFolder,@ForceLogLoadingFile)
Exec sp_DBA_KillDB @DatabaseName
Begin Try
Set @CreatedDateOn = GetDate()
Restore database @DatabaseName from disk=@FullBackupPathName WITH REPLACE, STATS, STANDBY=@FullLogShippingPathControl
Set @ErrorNumber = ''
Set @ErrorMessage = ''
Set @IsRestored = 'Y'
Update tbLogShippingControl
Set isRestored = @IsRestored
where BackupFileName = @BackupFileName
exec dbo.sp_DBA_CreateLogShippingTrace @DatabaseName,
@BackupPathFolder,
@BackupFileExtention,
@LastFullBackupSetId,
@FullLogShippingPathControl,
@FullBackupPathName,
@BackupFileName,
@RetriveCounter,
@ErrorNumber,
@ErrorMessage,
@IsRestored,
@ApplicationRemoved,
@CreatedDateOn
End Try
Begin Catch
Set @CreatedDateOn = GetDate()
Set @ErrorNumber = ERROR_NUMBER()
Set @ErrorMessage = ERROR_MESSAGE()
Set @IsRestored = 'N'
Update tbLogShippingControl
Set isRestored = @IsRestored
where BackupFileName = @BackupFileName
exec dbo.sp_DBA_CreateLogShippingTrace @DatabaseName,
@BackupPathFolder,
@BackupFileExtention,
@LastFullBackupSetId,
@FullLogShippingPathControl,
@FullBackupPathName,
@BackupFileName,
@RetriveCounter,
@ErrorNumber,
@ErrorMessage,
@IsRestored,
@ApplicationRemoved,
@CreatedDateOn
RAISERROR ('LogShipping - Individual and Forced Log Loading Errors', 16, 1)
End Catch
End
Else If @LogShippingType = 'FULL' Begin --// FULL == LogShipping
Set @FullBackupPathName = concat(@BackupPathFolder,@LastFullBackupName,'.',@BackupFileExtention)
Exec sp_DBA_KillDB @DatabaseName
Begin Try
Set @CreatedDateOn = GetDate()
If @DatabaseNewLogicalDataName is null and @DatabaseNewLogicalLogName is null Begin
Restore database @DatabaseName from disk=@FullBackupPathName WITH REPLACE, STATS, STANDBY=@FullLogShippingPathControl
End
Else --// Change the file location
Begin
Restore database @DatabaseName from disk=@FullBackupPathName
WITH
Move @DatabaseNewLogicalDataName TO @DatabaseNewPathDataLocation,
Move @DatabaseNewLogicalLogName TO @DatabaseNewPathLogLocation,
REPLACE, STATS, STANDBY=@FullLogShippingPathControl
End
Set @ErrorNumber = ''
Set @ErrorMessage = ''
Set @IsRestored = 'Y'
Update tbLogShippingControl
Set isRestored = @IsRestored
where BackupFileName = @BackupFileName
exec dbo.sp_DBA_CreateLogShippingTrace @DatabaseName,
@BackupPathFolder,
@BackupFileExtention,
@LastFullBackupSetId,
@FullLogShippingPathControl,
@FullBackupPathName,
@BackupFileName,
@RetriveCounter,
@ErrorNumber,
@ErrorMessage,
@IsRestored,
@ApplicationRemoved,
@CreatedDateOn
End Try
Begin Catch
Set @CreatedDateOn = GetDate()
Set @ErrorNumber = ERROR_NUMBER()
Set @ErrorMessage = ERROR_MESSAGE()
Set @IsRestored = 'N'
Update tbLogShippingControl
Set isRestored = @IsRestored
where BackupFileName = @BackupFileName
exec dbo.sp_DBA_CreateLogShippingTrace @DatabaseName,
@BackupPathFolder,
@BackupFileExtention,
@LastFullBackupSetId,
@FullLogShippingPathControl,
@FullBackupPathName,
@BackupFileName,
@RetriveCounter,
@ErrorNumber,
@ErrorMessage,
@IsRestored,
@ApplicationRemoved,
@CreatedDateOn
RAISERROR ('LogShipping - Full Backup Loading Errors', 16, 1)
End Catch
End
Else If @LogShippingType = 'LOG' Begin --// LOG == LogShipping
Declare CUR CURSOR FOR
select Name from [LOGSHIPPINGCG2].msdb.dbo.backupset
where database_name = @DatabaseName
and type = 'L'
and len(Name)>0
and Backup_Set_Id> @LastFullBackupSetId
and (Select count(1) From tbLogShippingControl c where c.BackupFileName = name and isRestored = 'Y') = 0
order by backup_start_date
Set @ErrorNumber = 0
Open CUR
Fetch Next From CUR
Into @BackupFileName
While @@FETCH_STATUS = 0 and @ErrorNumber = 0
Begin
Set @FullBackupPathName = concat(@BackupPathFolder,@BackupFileName,'.',@BackupFileExtention)
While (Select count(1) from Master..SysProcesses Where DB_Name(DbId) = @DatabaseName and status in ('runnable','suspended')) > 0 and @RetriveCounter < = 5 Begin
WaitFor Delay '00:00:30' -- 30 seconds x 5 times = 2:30 (minutes)
Set @RetriveCounter += 1
Set @ApplicationRemoved = (Select top 1 [Program_Name] from Master..SysProcesses Where DB_Name(DbId) = @DatabaseName and status in ('runnable','suspended'))
End
Exec sp_DBA_KillDB @DatabaseName
Begin Try
Restore Log @DatabaseName from disk=@FullBackupPathName WITH STANDBY=@FullLogShippingPathControl
Set @CreatedDateOn = GetDate()
Set @IsRestored = 'Y'
Set @ErrorNumber = ''
Set @ErrorMessage = ''
Update tbLogShippingControl
Set isRestored = @IsRestored
where BackupFileName = @BackupFileName
exec dbo.sp_DBA_CreateLogShippingTrace @DatabaseName,
@BackupPathFolder,
@BackupFileExtention,
@LastFullBackupSetId,
@FullLogShippingPathControl,
@FullBackupPathName,
@BackupFileName,
@RetriveCounter,
@ErrorNumber,
@ErrorMessage,
@IsRestored,
@ApplicationRemoved,
@CreatedDateOn
End Try
Begin Catch
Set @CreatedDateOn = GetDate()
Set @ErrorNumber = ERROR_NUMBER()
Set @ErrorMessage = ERROR_MESSAGE()
Set @IsRestored = 'N'
If @ErrorNumber > 0 Begin
Update tbLogShippingControl
Set isRestored = @IsRestored
where BackupFileName = @BackupFileName
exec dbo.sp_DBA_CreateLogShippingTrace @DatabaseName,
@BackupPathFolder,
@BackupFileExtention,
@LastFullBackupSetId,
@FullLogShippingPathControl,
@FullBackupPathName,
@BackupFileName,
@RetriveCounter,
@ErrorNumber,
@ErrorMessage,
@IsRestored,
@ApplicationRemoved,
@CreatedDateOn
RAISERROR ('LogShipping - Log Backup Loading Errors', 16, 1)
End
End Catch
Fetch Next From CUR
Into @BackupFileName
End
Close CUR
Deallocate CUR
End
End
If @DatabaseName = 'DATABASENAME01' Begin
--// Load the source backup historical from the main host
Select top 1
@LastFullBackupName = name,
@LastFullBackupSetId = Backup_Set_Id
from [LOGSHIPPING].msdb.dbo.backupset
where database_name = @DatabaseName
and type = 'D'
order by backup_start_date desc
If @LogShippingType = 'IND' Begin --// Individual and Forced Log Loading
Set @FullBackupPathName = concat(@BackupPathFolder,@ForceLogLoadingFile)
Exec sp_DBA_KillDB @DatabaseName
Begin Try
Set @CreatedDateOn = GetDate()
Restore database @DatabaseName from disk=@FullBackupPathName WITH REPLACE, STATS, STANDBY=@FullLogShippingPathControl
Set @ErrorNumber = ''
Set @ErrorMessage = ''
Set @IsRestored = 'Y'
Update tbLogShippingControl
Set isRestored = @IsRestored
where BackupFileName = @BackupFileName
exec dbo.sp_DBA_CreateLogShippingTrace @DatabaseName,
@BackupPathFolder,
@BackupFileExtention,
@LastFullBackupSetId,
@FullLogShippingPathControl,
@FullBackupPathName,
@BackupFileName,
@RetriveCounter,
@ErrorNumber,
@ErrorMessage,
@IsRestored,
@ApplicationRemoved,
@CreatedDateOn
End Try
Begin Catch
Set @CreatedDateOn = GetDate()
Set @ErrorNumber = ERROR_NUMBER()
Set @ErrorMessage = ERROR_MESSAGE()
Set @IsRestored = 'N'
Update tbLogShippingControl
Set isRestored = @IsRestored
where BackupFileName = @BackupFileName
exec dbo.sp_DBA_CreateLogShippingTrace @DatabaseName,
@BackupPathFolder,
@BackupFileExtention,
@LastFullBackupSetId,
@FullLogShippingPathControl,
@FullBackupPathName,
@BackupFileName,
@RetriveCounter,
@ErrorNumber,
@ErrorMessage,
@IsRestored,
@ApplicationRemoved,
@CreatedDateOn
RAISERROR ('LogShipping - Individual and Forced Log Loading Errors', 16, 1)
End Catch
End
Else If @LogShippingType = 'FULL' Begin --// FULL == LogShipping
Set @FullBackupPathName = concat(@BackupPathFolder,@LastFullBackupName,'.',@BackupFileExtention)
Exec sp_DBA_KillDB @DatabaseName
Begin Try
Set @CreatedDateOn = GetDate()
If @DatabaseNewLogicalDataName is null and @DatabaseNewLogicalLogName is null Begin
Restore database @DatabaseName from disk=@FullBackupPathName WITH REPLACE, STATS, STANDBY=@FullLogShippingPathControl
End
Else --// Change the file location
Begin
Restore database @DatabaseName from disk=@FullBackupPathName
WITH
Move @DatabaseNewLogicalDataName TO @DatabaseNewPathDataLocation,
Move @DatabaseNewLogicalLogName TO @DatabaseNewPathLogLocation,
REPLACE, STATS, STANDBY=@FullLogShippingPathControl
End
Set @ErrorNumber = ''
Set @ErrorMessage = ''
Set @IsRestored = 'Y'
Update tbLogShippingControl
Set isRestored = @IsRestored
where BackupFileName = @BackupFileName
exec dbo.sp_DBA_CreateLogShippingTrace @DatabaseName,
@BackupPathFolder,
@BackupFileExtention,
@LastFullBackupSetId,
@FullLogShippingPathControl,
@FullBackupPathName,
@BackupFileName,
@RetriveCounter,
@ErrorNumber,
@ErrorMessage,
@IsRestored,
@ApplicationRemoved,
@CreatedDateOn
End Try
Begin Catch
Set @CreatedDateOn = GetDate()
Set @ErrorNumber = ERROR_NUMBER()
Set @ErrorMessage = ERROR_MESSAGE()
Set @IsRestored = 'N'
Update tbLogShippingControl
Set isRestored = @IsRestored
where BackupFileName = @BackupFileName
exec dbo.sp_DBA_CreateLogShippingTrace @DatabaseName,
@BackupPathFolder,
@BackupFileExtention,
@LastFullBackupSetId,
@FullLogShippingPathControl,
@FullBackupPathName,
@BackupFileName,
@RetriveCounter,
@ErrorNumber,
@ErrorMessage,
@IsRestored,
@ApplicationRemoved,
@CreatedDateOn
RAISERROR ('LogShipping - Full Backup Loading Errors', 16, 1)
End Catch
End
Else If @LogShippingType = 'LOG' Begin --// LOG == LogShipping
Declare CUR CURSOR FOR
select Name from LOGSHIPPING.msdb.dbo.backupset
where database_name = @DatabaseName
and type = 'L'
and len(Name)>0
and Backup_Set_Id> @LastFullBackupSetId
and (Select count(1) From tbLogShippingControl c where c.BackupFileName = name and isRestored = 'Y') = 0
order by backup_start_date
Set @ErrorNumber = 0
Open CUR
Fetch Next From CUR
Into @BackupFileName
While @@FETCH_STATUS = 0 and @ErrorNumber = 0
Begin
Set @FullBackupPathName = concat(@BackupPathFolder,@BackupFileName,'.',@BackupFileExtention)
While (Select count(1) from Master..SysProcesses Where DB_Name(DbId) = 'DATABASENAME01' and status in ('runnable','suspended')) > 0 and @RetriveCounter < = 5 Begin
WaitFor Delay '00:00:30' -- 30 seconds x 5 times = 2:30 (minutes)
Set @RetriveCounter += 1
Set @ApplicationRemoved = (Select top 1 [Program_Name] from Master..SysProcesses Where DB_Name(DbId) = 'DATABASENAME01' and status in ('runnable','suspended'))
End
Exec sp_DBA_KillDB @DatabaseName
Begin Try
Restore Log @DatabaseName from disk=@FullBackupPathName WITH STANDBY=@FullLogShippingPathControl
Set @CreatedDateOn = GetDate()
Set @IsRestored = 'Y'
Set @ErrorNumber = ''
Set @ErrorMessage = ''
Update tbLogShippingControl
Set isRestored = @IsRestored
where BackupFileName = @BackupFileName
exec dbo.sp_DBA_CreateLogShippingTrace @DatabaseName,
@BackupPathFolder,
@BackupFileExtention,
@LastFullBackupSetId,
@FullLogShippingPathControl,
@FullBackupPathName,
@BackupFileName,
@RetriveCounter,
@ErrorNumber,
@ErrorMessage,
@IsRestored,
@ApplicationRemoved,
@CreatedDateOn
End Try
Begin Catch
Set @CreatedDateOn = GetDate()
Set @ErrorNumber = ERROR_NUMBER()
Set @ErrorMessage = ERROR_MESSAGE()
Set @IsRestored = 'N'
If @ErrorNumber > 0 Begin
Update tbLogShippingControl
Set isRestored = @IsRestored
where BackupFileName = @BackupFileName
exec dbo.sp_DBA_CreateLogShippingTrace @DatabaseName,
@BackupPathFolder,
@BackupFileExtention,
@LastFullBackupSetId,
@FullLogShippingPathControl,
@FullBackupPathName,
@BackupFileName,
@RetriveCounter,
@ErrorNumber,
@ErrorMessage,
@IsRestored,
@ApplicationRemoved,
@CreatedDateOn
RAISERROR ('LogShipping - Log Backup Loading Errors', 16, 1)
End
End Catch
Fetch Next From CUR
Into @BackupFileName
End
Close CUR
Deallocate CUR
End
End
--// Create SQL Job - For Full Restore (once per day)
================================================================================
exec dbo.sp_DBA_CreateLogShipping @DatabaseName = 'Database01',
@BackupPathFolder = '\\MyBackupServerAddress\Database01\',
@LogShippingType = 'FULL',
@BackupFileExtention = 'bak',
@FullLogShippingPathControl = 'D:\SQL\LogShipping\CRM.shp',
@ForceLogLoadingFile = null,
@DatabaseNewPathDataLocation = 'D:\SQL\Data\Database01.mdf',
@DatabaseNewPathLogLocation = 'E:\SQL\Log\Database01.ldf',
@DatabaseNewLogicalDataName = 'Database01',
@DatabaseNewLogicalLogName = 'Database01_log'
--// Create SQL Job - For Log Restore (once 15 minutes in my scenario)
================================================================================
exec dbo.sp_DBA_CreateLogShipping @DatabaseName = 'Database01',
@BackupPathFolder = '\\MyBackupServerAddress\Database01\Database01\',
@LogShippingType = 'LOG',
@BackupFileExtention = 'trn',
@FullLogShippingPathControl = 'D:\SQL\LogShipping\CRM.shp'