ary_server_name,  
    @primary_database_name, 
    @maintenance_plan_id, 
    @backup_threshold,
    @threshold_alert,
    @threshold_alert_enabled,
    N'first_file_000000000000.trn',
    GETDATE (),
    @planned_outage_start_time,
    @planned_outage_end_time,
    @planned_outage_weekday_mask,
    NULL)

  SELECT @primary_id = @@IDENTITY

  EXECUTE msdb.dbo.sp_add_log_shipping_monitor_jobs
END
go

/**************************************************************/
/* sp_add_log_shipping_secondary                              */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_add_log_shipping_secondary...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_add_log_shipping_secondary' AND type = N'P'))
  drop procedure sp_add_log_shipping_secondary
go
CREATE PROCEDURE sp_add_log_shipping_secondary
  @primary_id                  INT,
  @secondary_server_name       sysname,
  @secondary_database_name     sysname,
  @secondary_plan_id           UNIQUEIDENTIFIER,
  @copy_enabled                BIT              = 1,
  @load_enabled                BIT              = 1,
  @out_of_sync_threshold       INT              = 60,
  @threshold_alert             INT              = 14421,
  @threshold_alert_enabled     BIT              = 1,
  @planned_outage_start_time   INT              = 0,
  @planned_outage_end_time     INT              = 0,
  @planned_outage_weekday_mask INT              = 0,
  @allow_role_change           BIT              = 0 
AS
BEGIN
  SET NOCOUNT ON
  IF NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_primaries where primary_id = @primary_id)
  BEGIN
    RAISERROR (14262, 16, 1, N'primary_id', N'msdb.dbo.log_shipping_primaries')
    RETURN(1)
  END

  INSERT INTO msdb.dbo.log_shipping_secondaries (
    primary_id,
    secondary_server_name,
    secondary_database_name,
    last_copied_filename,
    last_loaded_filename,
    last_copied_last_updated,
    last_loaded_last_updated,
    secondary_plan_id,
    copy_enabled,
    load_enabled,
    out_of_sync_threshold,
    threshold_alert,
    threshold_alert_enabled,
    planned_outage_start_time,
    planned_outage_end_time,
    planned_outage_weekday_mask,
    allow_role_change)
   VALUES (@primary_id,
    @secondary_server_name,
    @secondary_database_name,
    N'first_file_000000000000.trn',
    N'first_file_000000000000.trn',
    GETDATE (),
    GETDATE (),
    @secondary_plan_id,
    @copy_enabled,
    @load_enabled,
    @out_of_sync_threshold,
    @threshold_alert,
    @threshold_alert_enabled,
    @planned_outage_start_time,
    @planned_outage_end_time,
    @planned_outage_weekday_mask,
    @allow_role_change)
END
go

/**************************************************************/
/* sp_delete_log_shipping_monitor_jobs                        */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_delete_log_shipping_monitor_jobs...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_delete_log_shipping_monitor_jobs' AND type = N'P')  )
  drop procedure sp_delete_log_shipping_monitor_jobs
go
CREATE PROCEDURE sp_delete_log_shipping_monitor_jobs AS
BEGIN
  DECLARE @backup_job_name sysname
  SET NOCOUNT ON
  SET @backup_job_name = N'Log Shipping Alert Job - Backup'
  IF (EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE name = @backup_job_name))
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'Log Shipping Alert Job - Backup'

  DECLARE @restore_job_name sysname
  SET @restore_job_name = 'Log Shipping Alert Job - Restore'
  IF (EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE name = @restore_job_name))
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'Log Shipping Alert Job - Restore'
END
go

/**************************************************************/
/* sp_delete_log_shipping_primary                             */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_delete_log_shipping_primary...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_delete_log_shipping_primary' AND type = N'P')  )
  drop procedure sp_delete_log_shipping_primary
go
CREATE PROCEDURE sp_delete_log_shipping_primary 
  @primary_server_name sysname,
  @primary_database_name sysname,
  @delete_secondaries BIT = 0
AS BEGIN
  DECLARE @primary_id INT

  SET NOCOUNT ON

  SELECT @primary_id = primary_id 
    FROM msdb.dbo.log_shipping_primaries 
    WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name
  IF (@primary_id IS NULL)
    RETURN (0)

  BEGIN TRANSACTION
  IF (EXISTS (SELECT * FROM msdb.dbo.log_shipping_secondaries WHERE primary_id = @primary_id))
  BEGIN
    IF (@delete_secondaries = 0)
    BEGIN
      RAISERROR (14429,-1,-1)
      goto rollback_quit
    END
    DELETE FROM msdb.dbo.log_shipping_secondaries WHERE primary_id = @primary_id
    IF (@@ERROR <> 0)
      GOTO rollback_quit
  END
  DELETE FROM msdb.dbo.log_shipping_primaries WHERE primary_id = @primary_id
  IF (@@ERROR <> 0)
    GOTO rollback_quit

  COMMIT TRANSACTION
  DECLARE @i INT
  SELECT @i = COUNT(*) FROM msdb.dbo.log_shipping_primaries
  IF (@i=0)
    EXECUTE msdb.dbo.sp_delete_log_shipping_monitor_jobs
  RETURN (0)

rollback_quit:
  ROLLBACK TRANSACTION
  RETURN(1) -- error
END
go

/**************************************************************/
/* sp_delete_log_shipping_secondary                           */
/**************************************************************/
PRINT ''
PRINT 'Creating sp_delete_log_shipping_secondary...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_delete_log_shipping_secondary' AND type = N'P')  )
  drop procedure sp_delete_log_shipping_secondary
go
CREATE PROCEDURE sp_delete_log_shipping_secondary 
  @secondary_server_name   sysname,
  @secondary_database_name sysname
AS BEGIN
  SET NOCOUNT ON
  DELETE FROM msdb.dbo.log_shipping_secondaries WHERE 
    secondary_server_name   = @secondary_server_name AND
    secondary_database_name = @secondary_database_name
END
go

/**************************************************************/
/* sp_log_shipping_in_sync                                    */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_log_shipping_in_sync...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_log_shipping_in_sync' AND type = N'P')  )
  drop procedure sp_log_shipping_in_sync
go
CREATE PROCEDURE sp_log_shipping_in_sync
  @last_updated        DATETIME,
  @compare_with        DATETIME,
  @threshold           INT,
  @outage_start_time   INT,
  @outage_end_time     INT,
  @outage_weekday_mask INT,
  @enabled             BIT = 1,
  @delta               INT = NULL OUTPUT
AS BEGIN
  SET NOCOUNT ON
  DECLARE @cur_time INT

  SELECT @delta = DATEDIFF (mi, @last_updated, @compare_with)
  -- in sync
  IF (@delta <= @threshold)
    RETURN (0) -- in sync

  IF (@enabled = 0) 
    RETURN(0) -- in sync

  IF (@outage_weekday_mask & DATEPART(dw, GETDATE ()) > 0) -- potentially in outage window
  BEGIN
    SELECT @cur_time = DATEPART (hh, GETDATE()) * 10000 +
                       DATEPART (mi, GETDATE()) * 100 + 
                       DATEPART (ss, GETDATE())
	  -- outage doesn't span midnight
    IF (@outage_start_time < @outage_end_time)
    BEGIN
      IF (@cur_time >= @outage_start_time AND @cur_time < @outage_end_time)
        RETURN(1) -- in outage
    END
	  -- outage does span midnight
	ELSE IF (@outage_start_time > @outage_end_time)
	BEGIN
	  IF (@cur_time >= @outage_start_time OR @cur_time < @outage_end_time)
	    RETURN(1) -- in outage
	END
  END
  RETURN(-1 ) -- not in outage, not in sync
END
go

/**************************************************************/
/* sp_log_shipping_get_date_from_file                         */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_log_shipping_get_date_from_file...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_log_shipping_get_date_from_file' AND type = N'P')  )
  drop procedure sp_log_shipping_get_date_from_file
go
CREATE PROCEDURE sp_log_shipping_get_date_from_file 
  @db_name sysname,
  @filename NVARCHAR (500),
  @file_date DATETIME OUTPUT
AS
BEGIN
  SET NOCOUNT ON

  DECLARE @tempname NVARCHAR (500)
  IF (LEN (@filename) - (LEN(@db_name) + LEN ('_tlog_')) <= 0)
    RETURN(1) -- filename string isn't long enough
  SELECT @tempname = RIGHT (@filename, LEN (@filename) - (LEN(@db_name) + LEN ('_tlog_')))
  IF (CHARINDEX ('.',@tempname,0) > 0)
    SELECT @tempname = LEFT (@tempname, CHARINDEX ('.',@tempname,0) - 1)
  IF (LEN (@tempname) <>  8 AND LEN (@tempname) <> 12)
    RETURN (1) -- error must be yyyymmddhhmm or yyyymmdd
  IF (ISNUMERIC (@tempname) = 0 OR CHARINDEX ('.',@tempname,0) <> 0 OR CONVERT (FLOAT,SUBSTRING (@tempname, 1,8)) < 1 )
    RETURN (1) -- must be numeric, can't contain any '.' etc
  SELECT @file_date = CONVERT (DATETIME,SUBSTRING (@tempname, 1,8),112)
  IF (LEN (@tempname) = 12)
  BEGIN
    SELECT @file_date = DATEADD (hh, CONVERT (INT, SUBSTRING (@tempname,9,2)),@file_date)
    SELECT @file_date = DATEADD (mi, CONVERT (INT, SUBSTRING (@tempname,11,2)),@file_date)
  END
  RETURN (0) -- success
END
go

/**************************************************************/
/* sp_get_log_shipping_monitor_info                           */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_get_log_shipping_monitor_info...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_get_log_shipping_monitor_info' AND type = N'P')  )
  drop procedure sp_get_log_shipping_monitor_info
go
CREATE PROCEDURE sp_get_log_shipping_monitor_info
  @primary_server_name     sysname = N'%',
  @primary_database_name   sysname = N'%',
  @secondary_server_name   sysname = N'%',
  @secondary_database_name sysname = N'%'
AS BEGIN
  SET NOCOUNT ON
  CREATE TABLE #lsp (
    primary_server_name            sysname       COLLATE database_default NOT NULL,
    primary_database_name          sysname       COLLATE database_default NOT NULL,
    secondary_server_name          sysname       COLLATE database_default NOT NULL,
    secondary_database_name        sysname       COLLATE database_default NOT NULL,
    backup_threshold               INT           NOT NULL,
    backup_threshold_alert         INT           NOT NULL,
    backup_threshold_alert_enabled BIT           NOT NULL,
    last_backup_filename           NVARCHAR(500) COLLATE database_default NOT NULL,
    last_backup_last_updated       DATETIME      NOT NULL,
    backup_outage_start_time       INT           NOT NULL,
    backup_outage_end_time         INT           NOT NULL,
    backup_outage_weekday_mask     INT           NOT NULL,
    backup_in_sync                 INT           NULL, -- 0 = in sync, -1 = out of sync, 1 = in outage window
    backup_delta                   INT           NULL,
    last_copied_filename           NVARCHAR(500) COLLATE database_default NOT NULL,
    last_copied_last_updated       DATETIME      NOT NULL,
    last_loaded_filename           NVARCHAR(500) COLLATE database_default NOT NULL,
    last_loaded_last_updated       DATETIME      NOT NULL,
    copy_delta                     INT           NULL,
    copy_enabled                   BIT           NOT NULL,
    load_enabled                   BIT           NOT NULL,
    out_of_sync_threshold          INT           NOT NULL,
    load_threshold_alert           INT           NOT NULL,
    load_threshold_alert_enabled   BIT           NOT NULL,
    load_outage_start_time         INT           NOT NULL,
    load_outage_end_time           INT           NOT NULL,
    load_outage_weekday_mask       INT           NOT NULL,
    load_in_sync                   INT           NULL, -- 0 = in sync, -1 = out of sync, 1 = in outage window
    load_delta                     INT           NULL,
    maintenance_plan_id		         UNIQUEIDENTIFIER NULL,
    secondary_plan_id              UNIQUEIDENTIFIER NOT NULL)

  INSERT INTO #lsp

 SELECT
    primary_server_name,
    primary_database_name,
    secondary_server_name,
    secondary_database_name,
    backup_threshold,
    p.threshold_alert,
    p.threshold_alert_enabled,
    last_backup_filename,
    p.last_updated,
    p.planned_outage_start_time,
    p.planned_outage_end_time,
    p.planned_outage_weekday_mask,
    NULL,
    NULL,
    last_copied_filename,
    last_copied_last_updated,
    last_loaded_filename,
    last_loaded_last_updated,
    NULL,
    copy_enabled,
    load_enabled,
    out_of_sync_threshold,
    s.threshold_alert,
    s.threshold_alert_enabled,
    s.planned_outage_start_time,
    s.planned_outage_weekday_mask,
    s.planned_outage_end_time,
    NULL,
    NULL,
    maintenance_plan_id,
    secondary_plan_id
  FROM msdb.dbo.log_shipping_primaries p, msdb.dbo.log_shipping_secondaries s
  WHERE 
    p.primary_id = s.primary_id AND
    primary_server_name LIKE @primary_server_name AND
    primary_database_name LIKE @primary_database_name AND
    secondary_server_name LIKE @secondary_server_name AND
    secondary_database_name LIKE @secondary_database_name

  DECLARE @load_in_sync                   INT
  DECLARE @backup_in_sync                 INT
  DECLARE @_primary_server_name           sysname 
  DECLARE @_primary_database_name         sysname 
  DECLARE @_secondary_server_name         sysname
  DECLARE @_secondary_database_name       sysname
  DECLARE @last_loaded_last_updated       DATETIME
  DECLARE @last_loaded_filename           NVARCHAR (500)
  DECLARE @last_copied_filename           NVARCHAR (500)
  DECLARE @last_backup_last_updated       DATETIME
  DECLARE @last_backup_filename           NVARCHAR (500)
  DECLARE @backup_outage_start_time       INT
  DECLARE @backup_outage_end_time         INT
  DECLARE @backup_outage_weekday_mask     INT
  DECLARE @backup_threshold               INT
  DECLARE @backup_threshold_alert_enabled BIT
  DECLARE @load_outage_start_time         INT
  DECLARE @load_outage_end_time           INT
  DECLARE @load_outage_weekday_mask       INT
  DECLARE @load_threshold                 INT
  DECLARE @load_threshold_alert_enabled   BIT
  DECLARE @backupdt                       DATETIME
  DECLARE @restoredt                      DATETIME
  DECLARE @copydt                         DATETIME
  DECLARE @rv                             INT
  DECLARE @dt                             DATETIME
  DECLARE @copy_delta                     INT
  DECLARE @load_delta                     INT
  DECLARE @backup_delta                   INT
  DECLARE @last_copied_last_updated       DATETIME

  SELECT @dt = GETDATE ()

  DECLARE sync_update CURSOR FOR
    SELECT 
      primary_server_name, 
      primary_database_name, 
      secondary_server_name, 
      secondary_database_name,
      last_backup_filename,
      last_backup_last_updated,
      last_loaded_filename,
      last_loaded_last_updated,
      backup_outage_start_time,
      backup_outage_end_time,
      backup_outage_weekday_mask,
      backup_threshold,
      backup_threshold_alert_enabled,
      load_outage_start_time,
      load_outage_end_time,
      out_of_sync_threshold,
      load_outage_weekday_mask,
      load_threshold_alert_enabled,
      last_copied_filename,
      last_copied_last_updated
    FROM #lsp
    FOR READ ONLY

  OPEN sync_update

loop:
  FETCH NEXT FROM sync_update INTO
    @_primary_server_name, 
    @_primary_database_name, 
    @_secondary_server_name, 
    @_secondary_database_name,
    @last_backup_filename,
    @last_backup_last_updated,
    @last_loaded_filename,
    @last_loaded_last_updated,
    @backup_outage_start_time,
    @backup_outage_end_time,
    @backup_outage_weekday_mask,
    @backup_threshold,
    @backup_threshold_alert_enabled,
    @load_outage_start_time,
    @load_outage_end_time,
    @load_threshold,
    @load_outage_weekday_mask,
    @load_threshold_alert_enabled,
    @last_copied_filename,
    @last_copied_last_updated

  IF @@fetch_status <> 0
    GOTO _loop

  EXECUTE @rv = sp_log_shipping_get_date_from_file @_primary_database_name, @last_backup_filename, @backupdt OUTPUT
  IF (@rv <> 0)
    SElECT @backupdt = @last_backup_last_updated
  EXECUTE @rv = sp_log_shipping_get_date_from_file @_primary_database_name, @last_loaded_filename, @restoredt OUTPUT
  IF  (@rv <> 0)
    SElECT @restoredt = @last_loaded_last_updated
  EXECUTE @rv = sp_log_shipping_get_date_from_file @_primary_database_name, @last_copied_filename, @copydt OUTPUT
  IF  (@rv <> 0)
    SElECT @copydt = @last_copied_last_updated
  
  EXECUTE @load_in_sync = msdb.dbo.sp_log_shipping_in_sync
    @restoredt,
    @backupdt,
    @load_threshold,
    @load_outage_start_time,
    @load_outage_end_time,
    @load_outage_weekday_mask,
    @load_threshold_alert_enabled,
    @load_delta OUTPUT

  EXECUTE @backup_in_sync = msdb.dbo.sp_log_shipping_in_sync
    @last_backup_last_updated,
    @dt,
    @backup_threshold,
    @backup_outage_start_time,
    @backup_outage_end_time,
    @backup_outage_weekday_mask,
    @backup_threshold_alert_enabled,
    @backup_delta OUTPUT

  EXECUTE msdb.dbo.sp_log_shipping_in_sync
    @copydt,
    @backupdt,
    1,0,0,0,0,
    @copy_delta OUTPUT

  UPDATE #lsp 
  SET backup_in_sync = @backup_in_sync, load_in_sync  = @load_in_sync, 
    copy_delta = @copy_delta, load_delta = @load_delta, backup_delta = @backup_delta
  WHERE primary_server_name = @_primary_server_name AND
    secondary_server_name = @_secondary_server_name AND
    primary_database_name = @_primary_database_name AND
    secondary_database_name = @_secondary_database_name 
  GOTO loop
_loop:
  CLOSE sync_update
  DEALLOCATE sync_update
  SELECT * FROM #lsp
END
go

/**************************************************************/
/* sp_update_log_shipping_monitor_info                        */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_update_log_shipping_monitor_info...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_update_log_shipping_monitor_info' AND type = N'P')  )
  DROP PROCEDURE sp_update_log_shipping_monitor_info
go
CREATE PROCEDURE sp_update_log_shipping_monitor_info
  @primary_server_name                 sysname,
  @primary_database_name               sysname,
  @secondary_server_name               sysname,
  @secondary_database_name             sysname,
  @backup_threshold                    INT = NULL,
  @backup_threshold_alert              INT = NULL,
  @backup_threshold_alert_enabled      BIT = NULL,
  @backup_outage_start_time            INT = NULL,
  @backup_outage_end_time              INT = NULL,
  @backup_outage_weekday_mask          INT = NULL,
  @copy_enabled                        BIT = NULL,
  @load_enabled                        BIT = NULL,
  @out_of_sync_threshold               INT = NULL,
  @out_of_sync_threshold_alert         INT = NULL,
  @out_of_sync_threshold_alert_enabled BIT = NULL,
  @out_of_sync_outage_start_time       INT = NULL,
  @out_of_sync_outage_end_time         INT = NULL,
  @out_of_sync_outage_weekday_mask     INT = NULL
AS BEGIN
  SET NOCOUNT ON
  DECLARE @_backup_threshold                    INT
  DECLARE @_backup_threshold_alert              INT
  DECLARE @_backup_threshold_alert_enabled      BIT
  DECLARE @_backup_outage_start_time            INT
  DECLARE @_backup_outage_end_time              INT
  DECLARE @_backup_outage_weekday_mask          INT
  DECLARE @_copy_enabled                        BIT
  DECLARE @_load_enabled                        BIT
  DECLARE @_out_of_sync_threshold               INT
  DECLARE @_out_of_sync_threshold_alert         INT
  DECLARE @_out_of_sync_threshold_alert_enabled BIT
  DECLARE @_out_of_sync_outage_start_time       INT
  DECLARE @_out_of_sync_outage_end_time         INT
  DECLARE @_out_of_sync_outage_weekday_mask     INT

  -- check that the primary exists
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_primaries WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name))
  BEGIN
    DECLARE @pp sysname
    SELECT @pp = @primary_server_name + N'.' + @primary_database_name
    RAISERROR (14262, 16, 1, N'primary_server_name.primary_database_name', @pp)
    RETURN (1) -- error
  END

  -- check that the secondary exists
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_secondaries WHERE secondary_server_name = @secondary_server_name AND secondary_database_name = @secondary_database_name))
  BEGIN
    DECLARE @sp sysname
    SELECT @sp = @secondary_server_name + N'.' + @secondary_database_name
    RAISERROR (14262, 16, 1, N'secondary_server_name.secondary_database_name', @sp)
    RETURN (1) -- error
  END

  -- load the original variables

 SELECT
    @_backup_threshold                    = backup_threshold,
    @_backup_threshold_alert              = p.threshold_alert,
    @_backup_threshold_alert_enabled      = p.threshold_alert_enabled,
    @_backup_outage_start_time            = p.planned_outage_start_time,
    @_backup_outage_end_time              = p.planned_outage_end_time,
    @_backup_outage_weekday_mask          = p.planned_outage_weekday_mask,
    @_copy_enabled                        = copy_enabled,
    @_load_enabled                        = load_enabled,
    @_out_of_sync_threshold               = out_of_sync_threshold,
    @_out_of_sync_threshold_alert         = s.threshold_alert,
    @_out_of_sync_threshold_alert_enabled = s.threshold_alert_enabled,
    @_out_of_sync_outage_start_time       = s.planned_outage_start_time,
    @_out_of_sync_outage_weekday_mask     = s.planned_outage_weekday_mask,
    @_out_of_sync_outage_end_time         = s.planned_outage_end_time
  FROM msdb.dbo.log_shipping_primaries p, msdb.dbo.log_shipping_secondaries s
  WHERE 
    p.primary_id            = s.primary_id           AND
    primary_server_name     = @primary_server_name   AND
    primary_database_name   = @primary_database_name AND
    secondary_server_name   = @secondary_server_name AND
    secondary_database_name = @secondary_database_name

  SELECT @_backup_threshold                    = ISNULL (@backup_threshold,                    @_backup_threshold)
  SELECT @_backup_threshold_alert              = ISNULL (@backup_threshold_alert,              @_backup_threshold_alert)
  SELECT @_backup_threshold_alert_enabled      = ISNULL (@backup_threshold_alert_enabled,      @_backup_threshold_alert_enabled)
  SELECT @_backup_outage_start_time            = ISNULL (@backup_outage_start_time,            @_backup_outage_start_time)
  SELECT @_backup_outage_end_time              = ISNULL (@backup_outage_end_time,              @_backup_outage_end_time)
  SELECT @_backup_outage_weekday_mask          = ISNULL (@backup_outage_weekday_mask,          @_backup_outage_weekday_mask)
  SELECT @_copy_enabled                        = ISNULL (@copy_enabled,                        @_copy_enabled)
  SELECT @_load_enabled                        = ISNULL (@load_enabled,                        @_load_enabled)
  SELECT @_out_of_sync_threshold               = ISNULL (@out_of_sync_threshold,               @_out_of_sync_threshold)
  SELECT @_out_of_sync_threshold_alert         = ISNULL (@out_of_sync_threshold_alert,         @_out_of_sync_threshold_alert)
  SELECT @_out_of_sync_threshold_alert_enabled = ISNULL (@out_of_sync_threshold_alert_enabled, @_out_of_sync_threshold_alert_enabled)
  SELECT @_out_of_sync_outage_start_time       = ISNULL (@out_of_sync_outage_start_time,       @_out_of_sync_outage_start_time)
  SELECT @_out_of_sync_outage_end_time         = ISNULL (@out_of_sync_outage_end_time,         @_out_of_sync_outage_end_time)
  SELECT @_out_of_sync_outage_weekday_mask     = ISNULL (@out_of_sync_outage_weekday_mask,     @_out_of_sync_outage_weekday_mask)

  -- updates
  UPDATE msdb.dbo.log_shipping_primaries SET
    backup_threshold            = @_backup_threshold,
    threshold_alert             = @_backup_threshold_alert,
    threshold_alert_enabled     = @_backup_threshold_alert_enabled,
    planned_outage_start_time   = @_backup_outage_start_time,
    planned_outage_end_time     = @_backup_outage_end_time,
    planned_outage_weekday_mask = @_backup_outage_weekday_mask
  WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name

  UPDATE msdb.dbo.log_shipping_secondaries SET
    copy_enabled                = @_copy_enabled,
    load_enabled                = @_load_enabled,
    out_of_sync_threshold       = @_out_of_sync_threshold,
    threshold_alert             = @_out_of_sync_threshold_alert,
    threshold_alert_enabled     = @_out_of_sync_threshold_alert_enabled,
    planned_outage_start_time   = @_out_of_sync_outage_start_time,
    planned_outage_end_time     = @_out_of_sync_outage_weekday_mask,
    planned_outage_weekday_mask = @_out_of_sync_outage_end_time
  WHERE secondary_server_name = @secondary_server_name AND secondary_database_name = @secondary_database_name
RETURN(0)
END
go

/**************************************************************/
/* sp_delete_log_shipping_monitor_info                        */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_delete_log_shipping_monitor_info...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_delete_log_shipping_monitor_info' AND type = N'P')  )
  DROP PROCEDURE sp_delete_log_shipping_monitor_info
go
CREATE PROCEDURE sp_delete_log_shipping_monitor_info
  @primary_server_name                 sysname,
  @primary_database_name               sysname,
  @secondary_server_name               sysname,
  @secondary_database_name             sysname
AS BEGIN
  -- check that the primary exists
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_primaries WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name))
  BEGIN
    DECLARE @pp sysname
    SELECT @pp = @primary_server_name + N'.' + @primary_database_name
    RAISERROR (14262, 16, 1, N'primary_server_name.primary_database_name', @pp)
    RETURN (1) -- error
  END

  -- check that the secondary exists
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_secondaries WHERE secondary_server_name = @secondary_server_name AND secondary_database_name = @secondary_database_name))
  BEGIN
    DECLARE @sp sysname
    SELECT @sp = @secondary_server_name + N'.' + @secondary_database_name
    RAISERROR (14262, 16, 1, N'secondary_server_name.secondary_database_name', @sp)
    RETURN (1) -- error
  END

  BEGIN TRANSACTION

  -- delete the secondary
  DELETE FROM msdb.dbo.log_shipping_secondaries WHERE secondary_server_name = @secondary_server_name AND secondary_database_name = @secondary_database_name
  IF (@@error <> 0)
    goto rollback_quit

  -- if there are no more secondaries for this primary then delete it
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_primaries p, msdb.dbo.log_shipping_secondaries s WHERE p.primary_id = s.primary_id AND primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name))
  BEGIN
    DELETE FROM msdb.dbo.log_shipping_primaries WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name
    IF (@@error <> 0)
      goto rollback_quit
  END
 COMMIT TRANSACTION
 RETURN (0)

rollback_quit:
  ROLLBACK TRANSACTION
  RETURN(1) -- Failure
END
go 

/**************************************************************/
/* sp_remove_log_shipping_monitor_account                     */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_remove_log_shipping_monitor_account...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_remove_log_shipping_monitor_account' AND type = N'P')  )
  DROP PROCEDURE sp_remove_log_shipping_monitor_account
go

CREATE PROCEDURE sp_remove_log_shipping_monitor_account
AS
BEGIN
  SET NOCOUNT ON
  EXECUTE sp_dropuser N'log_shipping_monitor_probe'
  EXECUTE sp_droplogin N'log_shipping_monitor_probe'
END
go

/**************************************************************/
/* sp_log_shipping_monitor_backup                             */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_log_shipping_monitor_backup...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_log_shipping_monitor_backup' AND type = N'P')  )
  drop procedure sp_log_shipping_monitor_backup
go

CREATE PROCEDURE sp_log_shipping_monitor_backup AS
BEGIN
  DECLARE @primary_id                  sysname
  DECLARE @primary_server_name         sysname 
  DECLARE @primary_database_name       sysname 
  DECLARE @maintenance_plan_id         UNIQUEIDENTIFIER
  DECLARE @backup_threshold            INT
  DECLARE @threshold_alert             INT 
  DECLARE @threshold_alert_enabled     BIT 
  DECLARE @last_backup_filename        sysname 
  DECLARE @last_updated                DATETIME
  DECLARE @planned_outage_start_time   INT
  DECLARE @planned_outage_end_time     INT 
  DECLARE @planned_outage_weekday_mask INT
  DECLARE @sync_status                 INT
  DECLARE @backup_delta                INT
  DECLARE @delta_string                NVARCHAR (10)
  DECLARE @dt                             DATETIME

  SELECT @dt = GETDATE ()

  SET NOCOUNT ON

  DECLARE bmlsp_cur CURSOR FOR
    SELECT primary_id, 
           primary_server_name, 
           primary_database_name, 
    	   maintenance_plan_id, 
           backup_threshold, 
           threshold_alert, 
           threshold_alert_enabled, 
           last_backup_filename, 
           last_updated,
           planned_outage_start_time, 
           planned_outage_end_time, 
           planned_outage_weekday_mask 
    FROM msdb.dbo.log_shipping_primaries
    FOR READ ONLY

  OPEN bmlsp_cur
loop:
  FETCH NEXT FROM bmlsp_cur 
  INTO @primary_id, 
       @primary_server_name, 
	   @primary_database_name, 
	   @maintenance_plan_id,
       @backup_threshold, 
	   @threshold_alert, 
	   @threshold_alert_enabled, 
	   @last_backup_filename, 
	   @last_updated, 
  	   @planned_outage_start_time,
       @planned_outage_end_time, 
	   @planned_outage_weekday_mask

  IF @@FETCH_STATUS <> 0 -- nothing more to fetch, finish the loop
    GOTO _loop

  EXECUTE @sync_status = sp_log_shipping_in_sync
    @last_updated,
    @dt,
	  @backup_threshold,
  	@planned_outage_start_time,
  	@planned_outage_end_time,
    @planned_outage_weekday_mask,
  	@threshold_alert_enabled,
  	@backup_delta OUTPUT

   IF (@sync_status < 0)
   BEGIN
     SELECT @delta_string = CONVERT (NVARCHAR(10), @backup_delta)
     RAISERROR (@threshold_alert, 16, 1, @primary_server_name, @primary_database_name, @delta_string)
   END

  GOTO loop
_loop:
  CLOSE bmlsp_cur
  DEALLOCATE bmlsp_cur
END
go

/**************************************************************/
/* sp_log_shipping_monitor_restore                            */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_log_shipping_monitor_restore...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_log_shipping_monitor_restore' AND type = N'P')  )
  drop procedure sp_log_shipping_monitor_restore
go
CREATE PROCEDURE sp_log_shipping_monitor_restore AS
BEGIN
  SET NOCOUNT ON
  DECLARE @primary_id                  INT
  DECLARE @secondary_server_name       sysname
  DECLARE @secondary_database_name     sysname
  DECLARE @secondary_plan_id           UNIQUEIDENTIFIER
  DECLARE @out_of_sync_threshold       INT 
  DECLARE @threshold_alert             INT 
  DECLARE @threshold_alert_enabled     BIT 
  DECLARE @last_loaded_filename        NVARCHAR (500)
  DECLARE @last_backup_filename        NVARCHAR (500) 
  DECLARE @primary_database_name       sysname
  DECLARE @last_loaded_last_updated    DATETIME
  DECLARE @last_backup_last_updated    DATETIME
  DECLARE @planned_outage_start_time   INT 
  DECLARE @planned_outage_end_time     INT 
  DECLARE @planned_outage_weekday_mask INT
  DECLARE @sync_status                 INT
  DECLARE @sync_delta                  INT
  DECLARE @delta_string                NVARCHAR(10)

  SET NOCOUNT ON
  DECLARE @backupdt  DATETIME
  DECLARE @restoredt DATETIME
  DECLARE @rv        INT
  DECLARE rmlsp_cur CURSOR FOR
    SELECT s.primary_id, 
      s.secondary_server_name, 
      s.secondary_database_name, 
      s.secondary_plan_id, 
      s.out_of_sync_threshold, 
      s.threshold_alert, 
      s.threshold_alert_enabled, 
      s.last_loaded_filename, 
      s.last_loaded_last_updated,
      p.last_backup_filename,
      p.last_updated,
      p.primary_database_name,
      s.planned_outage_start_time, 
      s.planned_outage_end_time, 
      s.planned_outage_weekday_mask 
    FROM msdb.dbo.log_shipping_secondaries s 
    INNER JOIN msdb.dbo.log_shipping_primaries p 
    ON s.primary_id = p.primary_id
    FOR READ ONLY

  OPEN rmlsp_cur
loop:
  FETCH NEXT FROM rmlsp_cur 
  INTO @primary_id, 
  	   @secondary_server_name, 
   	   @secondary_database_name, 
		   @secondary_plan_id, 
       @out_of_sync_threshold, 
		   @threshold_alert, 
		   @threshold_alert_enabled, 
		   @last_loaded_filename, 
		   @last_loaded_last_updated,
       @last_backup_filename,
       @last_backup_last_updated,
       @primary_database_name,
       @planned_outage_start_time, 
		   @planned_outage_end_time, 
		   @planned_outage_weekday_mask 

  IF @@FETCH_STATUS <> 0 -- nothing more to fetch, finish the loop
    GOTO _loop

  EXECUTE @rv = sp_log_shipping_get_date_from_file @primary_database_name, @last_backup_filename, @backupdt OUTPUT
  IF (@rv <> 0)
    SELECT @backupdt = @last_backup_last_updated
  
  EXECUTE @rv = sp_log_shipping_get_date_from_file @primary_database_name, @last_loaded_filename, @restoredt OUTPUT
  IF  (@rv <> 0)
    SELECT @restoredt = @last_loaded_last_updated

  EXECUTE @sync_status = sp_log_shipping_in_sync
    @restoredt,
    @backupdt,
	  @out_of_sync_threshold,
	  @planned_outage_start_time,
	  @planned_outage_end_time,
    @planned_outage_weekday_mask,
    @threshold_alert_enabled,
    @sync_delta OUTPUT

   IF (@sync_status < 0)
   BEGIN
     SELECT @delta_string = CONVERT (NVARCHAR(10), @sync_delta)
     RAISERROR (@threshold_alert, 16, 1, @secondary_server_name, @secondary_database_name, @delta_string)
   END

  GOTO loop
_loop:
  CLOSE rmlsp_cur
  DEALLOCATE rmlsp_cur
END
go

/**************************************************************/
/* sp_change_monitor_role                                     */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_change_monitor_role...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_change_monitor_role' AND type = N'P')  )
  DROP PROCEDURE sp_change_monitor_role
go
CREATE PROCEDURE sp_change_monitor_role
  @primary_server     sysname,
  @secondary_server   sysname,
  @database           sysname,
  @new_source         NVARCHAR (128)
AS BEGIN
  SET NOCOUNT ON

  BEGIN TRANSACTION

  -- drop the secondary
  DELETE FROM msdb.dbo.log_shipping_secondaries 
    WHERE secondary_server_name = @secondary_server AND secondary_database_name = @database

  IF (@@ROWCOUNT <> 1)
  BEGIN
      ROLLBACK TRANSACTION
      RAISERROR (14442,-1,-1)
      return(1)
  END

  -- let everyone know that we are the new primary
  UPDATE msdb.dbo.log_shipping_primaries 
    SET primary_server_name = @secondary_server, primary_database_name = @database, source_directory = @new_source
    WHERE primary_server_name = @primary_server AND primary_database_name = @database

  IF (@@ROWCOUNT <> 1)
  BEGIN
      ROLLBACK TRANSACTION
      RAISERROR (14442,-1,-1)
      return(1)
  END
  COMMIT TRANSACTION

END
go

/**************************************************************/
/* sp_create_log_shipping_monitor_account                     */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_create_log_shipping_monitor_account...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_create_log_shipping_monitor_account' AND type = N'P')  )
  drop procedure sp_create_log_shipping_monitor_account
go
CREATE PROCEDURE sp_create_log_shipping_monitor_account @password sysname
AS
BEGIN
  DECLARE @rv INT
  SET NOCOUNT ON
-- raise an error if the password already exists
  if exists(select * from master.dbo.syslogins where loginname = N'log_shipping_monitor_probe')
  begin
    raiserror(15025,-1,-1,N'log_shipping_monitor_probe')
    RETURN (1) -- error
  end

  IF (@password = N'')
  BEGIN
    EXECUTE @rv = sp_addlogin N'log_shipping_monitor_probe', @defdb = N'msdb'
    IF @@error <>0 or @rv <> 0
      RETURN (1) -- error
  END
  ELSE
  BEGIN
    EXECUTE @rv = sp_addlogin N'log_shipping_monitor_probe', @password, N'msdb'
    IF @@error <>0 or @rv <> 0
      RETURN (1) -- error
  END

  EXECUTE @rv = sp_grantdbaccess N'log_shipping_monitor_probe', N'log_shipping_monitor_probe'
  IF @@error <>0 or @rv <> 0
    RETURN (1) -- error

  GRANT UPDATE ON log_shipping_primaries   TO log_shipping_monitor_probe
  GRANT UPDATE ON log_shipping_secondaries TO log_shipping_monitor_probe
  GRANT SELECT ON log_shipping_primaries   TO log_shipping_monitor_probe
  GRANT SELECT ON log_shipping_secondaries TO log_shipping_monitor_probe

  RETURN (0)
END
go

GRANT EXECUTE ON sp_get_log_shipping_monitor_info TO PUBLIC

/**************************************************************/
/* Turn 'System Object' marking OFF                           */
/**************************************************************/
PRINT ''
EXECUTE master.dbo.sp_MS_upd_sysobj_category 2
go

EXECUTE master.dbo.sp_configure N'allow updates', 0
go
RECONFIGURE WITH OVERRIDE
go

PRINT ''
PRINT '----------------------------------'
PRINT 'Execution of INSTMSDB.SQL complete'
PRINT '----------------------------------'
go

DUMP TRANSACTION msdb WITH NO_LOG
go
CHECKPOINT
go
tCompany/imgs1/images/img_subject1_14x30.gif - 80 - 61.138.111.61 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.1.4322) 200 0 0
2007-06-20 09:19:44 W3SVC1470895087 58.61.156.23 GET /seller/info.asp info_id=11488 80 - 60.183.216.132 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1) 200 0 0
2007-06-20 09:19:44 W3SVC1470895087 58.61.156.23 GET /company/1/index.asp id=3317 80 - 220.181.19.174 Sogou+web+spider/3.0(+http://www.sogou.com/docs/help/webmasters.htm#07) 200 0 0
2007-06-20 09:19:44 W3SVC1470895087 58.61.156.23 GET /css/css.css - 80 - 60.183.216.132 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1) 200 0 0
2007-06-20 09:19:44 W3SVC1470895087 58.61.156.23 GET /trustadv/rempost.asp id=3431 80 - 220.181.19.159 Sogou+web+spider/3.0(+http://www.sogou.com/docs/help/webmasters.htm#07) 302 0 0
2007-06-20 09:19:44 W3SVC1470895087 58.61.156.23 GET /company/1/company.asp id=5937 80 - 61.135.163.148 Baiduspider+(+http://www.baidu.com/search/spider.htm) 200 0 0
2007-06-20 09:19:4ÿþ/*

** Copyright Microsoft, Inc. 1994 - 2000

** All Rights Reserved.

*/



USE master

GO

if exists (select * from sysdatabases where name='Northwind')

		drop database Northwind

go



DECLARE @device_directory NVARCHAR(520)

SELECT @de