Log shipping on SQL Server 2000 Standard edition

Log shipping is not supported on SQL Server 2000 Standard edition. But there are undocumented system stored procedures to create maintenance plans and setup log shipping on the standard edition.

First create the two stored procedures below. (The SPs are designed to keep backups on Primary for 4 days and on Secondary for 72 Hours. The job frequency is 15min. If needed change the SPs as commented.)

SP - 1 on PRIMARY SERVER:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[CreateLogJob] Script Date: 12/09/2009 09:55:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[CreateLogJob] @ServerNm varchar(255), @DBNm varchar(255)
as

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
declare @JobName varchar(255)
declare @JobCommand varchar(255)
declare @NameStr varchar(255)

SELECT @NameStr = @DBNm + ' Log Ship Trans Log Backup'
SELECT @JobName = N'' + @NameStr
SELECT @JobCommand = N'EXECUTE master.dbo.xp_sqlmaint ''-D ' + @DBNm + ' -WriteHistory -BkUpMedia DISK -BkUpLog \\' + @ServerNm + '\Logship\ -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"''' -- RETENTION ON PRIMARY IS 4 DAYS.

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = @JobName)
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = @JobName
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
@job_name = @JobName,
@owner_login_name = N'sa',
@description = N'Backup Transaction Log for Log Shipping',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 0,
@delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @JobCommand,

@database_name = N'master',
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 4,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID,
@start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = N'Schedule 1',
@enabled = 1,
@freq_type = 4,
@active_start_date = 20000801,
@active_start_time = 0,
@freq_interval = 15, -- Every 15 mins
@freq_subday_type = 4,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_end_date = 99991231,
@active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID,
@server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

SP - 2 on SECONDARY SERVER:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[CreateLogFilePlans] Script Date: 12/09/2009 09:55:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[CreateLogFilePlans] @ServerNm varchar(255), @BackupServerNm varchar(255), @DBNm varchar(255)
as

declare @SourceDirStr varchar(255)
declare @DestDirStr varchar(255)
declare @NameStr varchar(255)
declare @cmd varchar(255)

set @SourceDirStr = '\\' + @ServerNm + '\Logship\' + @DBNm + ''
set @DestDirStr = '\\' + @BackupServerNm + '\Logship\' + @DBNm + ''
set @NameStr = @DBNm + ' Log_Shipping'
set @cmd = 'mkdir \\' + @BackupServerNm + '\Logship\' + @DBNm + ''

BEGIN
-- Create directory for log files
exec xp_cmdshell @cmd

PRINT 'Creating log file plans...'

exec msdb.dbo.sp_create_backup_movement_plan
@name = @NameStr, -- Log shipping plan name
@source_dir = @SourceDirStr, -- path to P1 tran logs
@dest_dir = @DestDirStr, -- location to copy P1 logs to
@sub_dir = 0, -- 0 = dest_dir is absolute path
@load_job_freq = 15, -- Load job frequency in minutes
@copy_job_freq = 15 -- copy job frequency in minutes

exec msdb.dbo.sp_add_db_to_backup_movement_plan
@plan_id = NULL, -- Plan ID, optional param
@plan_name = @NameStr, -- Log shipping plan name,equal to @name above
@source_db = @DBNm, -- Primary db name
@dest_db = @DBNm, -- Secondary db name
@load_delay = 0, -- load delay for load job in minutes
@load_all = 1, -- set to 1
@source_server = @ServerNm, -- source server
@retention_period = 72 -- time before deleting t-logs from secondary server

exec ('use msdb grant insert on backup_movement_plan_history to public')
END

After creating the SPs run them in the below syntax.

On PRIMARY:
EXEC [dbo].[CreateLogJob] *PRIMARY_SERVER_NAME*, *DBNAME*

On SECONDARY:
EXEC [dbo].[CreateLogFilePlans] *PRIMARY_SERVER_NAME*, *SECONDARY_SERVER_NAME*, *DBNAME*

-Manu