In summary, the main steps are as follows;
1- on primary database: create a job to backup transaction logs regularly
2- on primary database: share the transaction log backup directory.
3- backup and restore the database to be log shipped from prımary to secondary database
4- on secondary database: create the following structure (tables and procedures)
5- on secondary database: create the copy and load jobs via the created structure
6- on secondary database: monitor the process
There are 3 stored procedures and 3 tables to create;
USE msdb
CREATE TABLE backup_movement_plans
(
plan_id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY CLUSTERED,
plan_name sysname NULL,
source_dir NVARCHAR(256) NOT NULL,
destination_dir NVARCHAR(256) NOT NULL,
database_subdir BIT NOT NULL DEFAULT (1)
)
USE msdb
CREATE TABLE backup_movement_plan_databases
(
plan_id UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES backup_movement_plans(plan_id),
source_database sysname NOT NULL,
destination_database sysname NOT NULL,
source_server sysname NOT NULL DEFAULT (@@servername),
load_delay INT NOT NULL DEFAULT(0), -- In minutes
load_all BIT NOT NULL DEFAULT(1),
retention_period INT NOT NULL DEFAULT(48), -- In hours
last_file_copied NVARCHAR(256) NULL,
date_last_copied DATETIME NULL,
last_file_loaded NVARCHAR(256) NULL,
date_last_loaded DATETIME NULL
)
USE msdb
CREATE TABLE backup_movement_plan_history
(
sequence_id INT NOT NULL IDENTITY UNIQUE CLUSTERED,
plan_id UNIQUEIDENTIFIER NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'),
plan_name sysname NOT NULL DEFAULT ('All ad-hoc plans'),
destination_server sysname NOT NULL DEFAULT (@@servername),
source_server sysname NOT NULL DEFAULT (@@servername),
source_database sysname NOT NULL,
destination_database sysname NOT NULL,
activity BIT NOT NULL DEFAULT (0),
succeeded BIT NOT NULL DEFAULT (1),
num_files INT NOT NULL DEFAULT (0),
last_file NVARCHAR(256) NULL,
end_time DATETIME NOT NULL DEFAULT (GETDATE()),
duration INT NULL DEFAULT (0),
error_number INT NOT NULL DEFAULT (0),
message NVARCHAR(512) NULL
)
USE msdb
CREATE PROCEDURE sp_create_backup_movement_plan
@name sysname,
@source_dir VARCHAR(256),
@dest_dir VARCHAR(256),
@sub_dir BIT = 1, -- Each database has it's own sub-directory
@load_job_freq INT = 5, -- In Minutes
@copy_job_freq INT = 5 -- In Minutes
AS
BEGIN
BEGIN TRANSACTION
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
DECLARE @PlanID uniqueidentifier
DECLARE @CopyJobName sysname
DECLARE @LoadJobName sysname
DECLARE @CopyCommand VARCHAR(500)
DECLARE @LoadCommand VARCHAR(500)
DECLARE @ReturnCode INT
-- Create a GUID for the plan
SELECT @PlanID = NEWID()
-- Check if a plan with the same name exists
IF (EXISTS (SELECT *
FROM msdb.dbo.backup_movement_plans
WHERE plan_name = @name ))
BEGIN
RAISERROR('A backup movement plan with the same name already exists. Specify a different name.'', 16, 1)
GOTO QuitWithRollback
END
-- Insert plan in the table
INSERT msdb.dbo.backup_movement_plans
(plan_id, plan_name, source_dir, destination_dir, database_subdir)
VALUES
(@PlanID, @name, @source_dir, @dest_dir, @sub_dir)
SELECT @CopyJobName = N'Copy Job For ' + @name
SELECT @LoadJobName = N'Load Job For ' + @name
SELECT @CopyCommand = N'EXECUTE master.dbo.xp_sqlmaint ''-CopyPlanName "' + @name + '" '' '
SELECT @LoadCommand = N'EXECUTE master.dbo.xp_sqlmaint ''-LoadPlanName "' + @name + '" '' '
-- Create the load job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_name = @LoadJobName
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_name = @LoadJobName,
@step_id=1,
@step_name = N'step1',
@command = @LoadCommand,
@subsystem = N'TSQL',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2, @flags = 4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_name = @LoadJobName,
@freq_subday_interval = @load_job_freq,
@name = N'sch1',
@enabled = 1,
@freq_type = 4,
@active_start_date = 19980402,
@active_start_time = 0,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_end_date = 99991231,
@active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_name = @LoadJobName, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Create the Copy Job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_name = @CopyJobName
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_name = @CopyJobName,
@step_id = 1,
@step_name = N'step1',
@command = @CopyCommand,
@subsystem = N'TSQL',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2,
@flags = 4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_name = @CopyJobName,
@freq_subday_interval = @copy_job_freq,
@name = N'sch1',
@enabled = 1,
@freq_type = 4,
@active_start_date = 19980402,
@active_start_time = 0,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_end_date = 99991231,
@active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_name = @CopyJobName, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
END
USE msdb
CREATE PROCEDURE sp_add_db_to_backup_movement_plan
@plan_id uniqueidentifier = NULL,
@plan_name sysname = NULL,
@source_db sysname,
@dest_db sysname,
@load_delay INT = 0, -- In Minutes
@load_all BIT = 1,
@source_server sysname = @@servername,
@retention_period INT = 48 -- In Hours. 0 implies do not delete files
AS
BEGIN
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
DECLARE @PlanID uniqueidentifier
if((@plan_id IS NULL) AND (@plan_name IS NULL))
BEGIN
RAISERROR('You must supply the plan name or the plan id.', 16, 1)
RETURN(1)
END
IF (@plan_id IS NULL)
BEGIN
IF (NOT EXISTS (SELECT *
FROM msdb.dbo.backup_movement_plans
WHERE plan_name = @plan_name ) )
BEGIN
RAISERROR('Backup movement plan with this name was not found.', 16, 1)
RETURN(1)
END
IF (SELECT COUNT(*)
FROM msdb.dbo.backup_movement_plans
WHERE plan_name = @plan_name) > 1
BEGIN
RAISERROR('There are more than one backup movement plans with this name.', 16, 1)
RETURN(1)
END
SELECT @PlanID = plan_id
FROM msdb.dbo.backup_movement_plans
WHERE plan_name = @plan_name
END
ELSE
BEGIN
SELECT @PlanID = @plan_id
IF (NOT EXISTS (SELECT *
FROM msdb.dbo.backup_movement_plans
WHERE plan_id = @plan_id ) )
BEGIN
RAISERROR('Backup movement plan with this id.', 16, 1)
RETURN(1)
END
IF (SELECT COUNT(*)
FROM msdb.dbo.backup_movement_plans
WHERE plan_id = @plan_id) > 1
BEGIN
RAISERROR('There are more than one backup movement plans with this id.', 16, 1)
RETURN(1)
END
END
IF (EXISTS ( SELECT *
FROM msdb.dbo.backup_movement_plan_databases
WHERE plan_id = @PlanID AND source_database = @source_db AND destination_database = @dest_db ))
BEGIN
RAISERROR('These databases are already included in this plan', 16, 1)
RETURN(1)
END
INSERT msdb.dbo.backup_movement_plan_databases
(plan_id, source_database, destination_database, load_delay, load_all, source_server, retention_period)
VALUES
(@PlanID, @source_db, @dest_db, @load_delay, @load_all, @source_server, @retention_period)
END
After creating the infrastructure you can create the transaction log backup copy and load jobs by running the following script. After running the script logshipping started via the created jobs and the process can be monitored by creating and using the following stored procedure.
exec msdb..sp_create_backup_movement_plan @name = "DB01_logshipping", @source_dir = "\\PRMDB01\trnlogs", @dest_dir = "D:\logshipping\", @sub_dir = "DB01", @load_job_freq =30, @copy_job_freq = 30 exec msdb..sp_add_db_to_backup_movement_plan @plan_name = "DB01_logshipping", @source_db = "DB01", @dest_db = "DB01", @load_delay = 10, @load_all = 1, @source_server = 'PRMYDB01', @retention_period = 30 --in days
Here is the stored procedure to monitor the log shipping process.
USE msdb
CREATE PROCEDURE dbo.sp_log_ship_status
@p_svr varchar( 30 ) = NULL,
@p_db varchar( 30 )= NULL
AS
Begin
set nocount on
DECLARE @dest_db char(30),
@history_id int,
@time_delta int
CREATE TABLE #table ( destination_db CHAR(30),
time_delta INT)
DECLARE log_ship_cursor CURSOR
FOR SELECT destination_database
from backup_movement_plan_databases
OPEN log_ship_cursor
FETCH NEXT FROM log_ship_cursor into @dest_db
WHILE @@FETCH_STATUS = 0
BEGIN
set nocount on
select @history_id = (select max(restore_history_id)
from restorehistory
where destination_database_name = @dest_db)
select @time_delta = (select datediff(mi, (select backup_start_date
from backupset
where backup_set_id = (select backup_set_id
from restorehistory
where restore_history_id = @history_id)), getdate()))
INSERT INTO #table VALUES( @dest_db, @time_delta)
FETCH NEXT from log_ship_cursor into @dest_db
end
close log_ship_cursor
DEALLOCATE log_ship_cursor
SELECT "Primary Srv" = CONVERT(char(30),source_server),
"Primary DB" = CONVERT(char(30),source_database),
"Secondary DB" = CONVERT(char(30),destination_database),
"Delta" = time_delta,
"Load All" = CASE WHEN (load_all = 0) THEN "No" ELSE "Yes" end,
"Load Delay" = load_delay,
"Save Period" = retention_period,
"Last File Copied" = CONVERT(char(75),last_file_copied),
"Copy Logged Time" = date_last_copied,
"Last File Loaded" = CONVERT(char(75),last_file_loaded),
"Load Logged Time" = date_last_loaded
FROM msdb..backup_movement_plan_databases,
#table
WHERE (@p_svr is NULL or source_server like @p_svr)
AND (@p_db is NULL or source_database like @p_db)
AND destination_database = destination_db
drop table #table
END
-------------------------
-- it seems it is working
-------------------------
dbo.log_ship_status
/*
Primary Srv Primary DB Secondary DB Delta Load All Load Delay Save Period Last File Copied Copy Logged Time Last File Loaded Load Logged Time
----------- --------------- ----------------- -------- -------- ----------- ----------- ------------------------------------------------ ----------------------- ------------------------------------------- -----------------------
crmsbdb01 siebeldb siebeldb 34 Yes 1 60 \\crmsbdb01\t-logs\siebeldb\siebeldb_tlog_201... 2011-07-13 15:10:01.610 E:\t-logs\siebeldb\siebeldb_tlog_2011071314... 2011-07-13 15:00:12.563
crmsbdb01 IVR_INTEGRATE IVR_INTEGRATE 34 Yes 1 60 \\crmsbdb01\t-logs\IVR_INTEGRATE\IVR_INTEGRAT... 2011-07-13 15:10:03.313 E:\t-logs\IVR_INTEGRATE\IVR_INTEGRATE_tlog_... 2011-07-13 14:50:07.627
*/
