-- Stored Procedure: dbo.InitSLSLogShipping
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InitSLSLogShipping]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InitSLSLogShipping]
GO




CREATE PROCEDURE [dbo].[InitSLSLogShipping] 
 @DatabaseName sysname      --Name of the database to ship logs
 , @StandbyServer sysname     --Standby server to receive/apply the logs
 , @MinuteInterval tinyint = 15    --How often to ship the log in minutes
 , @BackupLocationPath varchar(255) = null --UNC backup path to location of the backup files
 , @NotifyOnFail varchar(100) -- email when things go bad
 , @HoursToKeep tinyint = 1                --How long to leave backup files on disk after restore in hours
 , @MinutesAcceptableLatency tinyint = 60  -- don't fail until process is behind by less that minutes
 , @NotifyOnLatent varchar(100) -- email when it's probably in a waiting state
 , @BackupThreads tinyint = 1    --Number of Threads to use for SQL LiteSpeed Backup, Use 1 if unsure
 , @BackupPriority tinyint = 0    --Base Priority of SQL LiteSpeed Backup, Use 0 if unsure   
 , @debug bit = 0
AS
/*******************************************************************************************************
* admin.dbo.InitSLSLogShipping 
* Creator:       Bill Wunder
* Date:          3-28-2003
*
* Project:       
* Project Mgr:   
* Dev Contact:   
*
* Description:   Initialize Primary and Standby servers for SQLLiteSpeed based log shipping 
*                  
* Notes:         See DBAConfigSLSLogShippingprocedure for prerequisites external to SQL Server.
*         DBAConfigSLSLogShippingprocedure must have been executed on primary SQL Server
*                      before this procedure is executed. This procedure is always executed at the
*                      primary SQL Server
*                      ONLY A sql server login that is a sysadmin can execute this procedure. I believe 
*         this limitation can be eliminated with Active Directory enabled. 
* Usage:
      EXECUTE admin.dbo.InitSLSLogShipping 
   @DatabaseName = 'pubs'
   , @StandByServer = 'A_ZEUS'
   , @NotifyOnLatent = 'bill.wunder@wallst.com'
   , @NotifyOnFail = 'bill.wunder@wallst.com'

   , @BackupLocationPath = '\\A_ZEUS\SLS_TLogs'
   , @MinuteInterval = 15
   , @HoursToKeep = 1
   , @MinutesAcceptableLatency = 60
   , @BackupThreads = 1
   , @BackupPriority = 0
   , @debug = 1

*
*
* Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
DECLARE 
 @BackupFileName varchar(1024)
 , @UndoFileName varchar(255)
 , @RecoveryFlag bit
 , @rc int
 , @ec int
 , @hr int
 , @SqlStr nvarchar(1024)
 , @JobCategory varchar(50)
 , @JobName varchar(1024)
 , @JobDesc varchar(128)
 , @JobCmd varchar(2048)
 , @JobCmd2 varchar(2048)
 , @JobOwner sysname
 , @OutputFile varchar(1024)
 , @StandByLinkedServer sysname
 , @PrimaryLinkedServer sysname
 , @ErrDesc varchar(8000)
 , @Err varchar(1024)

---------------------------------------------
-- create temp tables
---------------------------------------------

---------------------------------------------
-- set session variables
---------------------------------------------
set xact_abort on
SET NOCOUNT ON

---------------------------------------------
-- body of stored procedure
---------------------------------------------
-- use the default backup location for this standby server if not specified for this database
if @BackupLocationPath is null
 select @BackupLocationPath = LogShippingShare
 from admin.dbo.SLSLogShippingConfig
 where StandByServer = @StandbyServer

if substring(reverse(@BackupLocationPath),1,1) <> '\'
 set @BackupLocationPath = @BackupLocationPath + '\'

set @BackupFileName = @BackupLocationPath + @DatabaseName + '_Full.SLS'
-- probably no good reason to take full backup to STANDBY since the first log will shipped does so in this proc
set @UndoFileName = null--@BackupLocationPath + @DatabaseName + '_Full.tuf'
set @RecoveryFlag = null--0
set @JobCategory = 'SQL LiteSpeed LogShipping'
set @JobName = 'SQL LiteSpeed Log Shipping - ' + @DatabaseName
set @JobDesc = 'Backup transaction logs using SQL LiteSpeed and restore logs to ' + @StandbyServer 
set @JobCmd = 'EXEC  admin.dbo.GetSLSLogPrimary @DatabaseName = ' + char(39) + @DatabaseName + char(39) 
set @JobCmd2 = 'exec admin.dbo.NotifySLSLogShippingFailure ' + char(39) + @DatabaseName + char(39) 
-- these guys could have hyphens in server names so wrap in square brackets
set @StandByLinkedServer = 'lnkSLSLogShipping__To__' + @StandbyServer + '__' + @DatabaseName + ''
set @PrimaryLinkedServer = 'lnkSLSLogShipping__From__' + @@servername + '__' + @DatabaseName + ''
set @hr = 0

select @JobOwner = LogShippingUser
from admin.dbo.SLSLogShippingConfig
where StandbyServer = @StandbyServer

if @debug = 1
 print 'Validate user security level'

if not(IS_SRVROLEMEMBER( 'sysadmin') = 1)
 begin
  set @Err = suser_sname() + ' is not a member of sysadmin fixed server role on ' + @@servername
  goto ErrorHandler
 end

if datalength(suser_sid()) > 16
 begin
  set @Err = suser_sname() + ' is a Windows authenticated user on ' + @@servername 
   + '. SLS Log shipping supports only SQL Logins to avoid delegation issues in non AD environments.'
  goto ErrorHandler
 end

if @debug = 1
 print 'Define log shipping specific Linked Server ' + @StandbyLinkedServer + ' from ' + @@servername + ' to ' + @StandbyServer

exec @rc =  admin.dbo.AddLinkedServer
  @LinkedServerName = @StandbyLinkedServer
  , @ServerName = @StandbyServer
  , @QueryTimeout = 14400
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'Failed to add linked server ' + @StandByLinkedServer+ ' on ' + @@servername
  goto ErrorHandler
 end

set @SQLStr = 'exec @hr = [' + @StandbyLinkedServer + '].admin.dbo.AddLinkedServer
  @LinkedServerName = [' + @PrimaryLinkedServer + ']
  , @ServerName = ''' + @@servername + '''
  , @QueryTimeout = 14400'
if @debug = 1
 begin
  print 'Define log shipping specific Linked Server ' + @PrimaryLinkedServer + ' from ' + @StandbyServer + ' to ' + @@servername
   + char(13) + char(10) + 'Statement executed: ' 
   + char(13) + char(10) + char(9) + @SQLStr
 end
exec @rc = sp_executesql @SQLStr, N'@hr int OUTPUT', @hr OUTPUT
set @ec = @@error
if @rc <> 0 or @ec <> 0 or @hr <> 0
 begin
  set @Err = 'Failed to add linked server ' + @PrimaryLinkedServer + ' on ' + @StandbyServer
  goto ErrorHandler
 end

set @SQLStr = 'dir ' + @BackupLocationPath
if @debug = 1
 begin
  print 'Verify access to the file system backup folder ' + @BackupLocationPath + ' from ' + @@servername
   + char(13) + char(10) + 'Statement executed: ' 
   + char(13) + char(10) + char(9) + @SQLStr
  exec @rc = master.dbo.xp_cmdshell @SQLStr
 end
else
 exec @rc = master.dbo.xp_cmdshell @SQLStr, NO_OUTPUT

select @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'Unable to access file system backup folder ' + @BackupLocationPath + ' from ' + @@servername
  goto ErrorHandler
 end

-- still a possibility that the @logShippingUser can't access share from both servers
set @SQLStr = 'exec @hr = [' + @StandbyLinkedServer + '].master.dbo.xp_cmdshell ''' + @SQLStr + ''''
if @debug = 1
 begin
  print 'Verify access to the file system backup folder ' + @BackupLocationPath + ' from ' + @StandByServer
   + char(13) + char(10) + 'Statement executed: ' 
   + char(13) + char(10) + char(9) + @SQLStr
 end
exec @rc = sp_executesql @SQLStr, N'@hr int OUTPUT', @hr OUTPUT

select @ec = @@error
if @rc <> 0 or @ec <> 0 or @hr <> 0
 begin
  set @Err = 'Unable to access file system backup folder ' + @BackupLocationPath + ' from ' + @StandByServer
  goto ErrorHandler
 end

-- now remove any existing sls files
-- good test to make sure this user has change access to the folder
set @SQLstr = 'dir ' + @BackupLocationPath + @DatabaseName + '*.SLS'
if @debug = 1
 begin
  print 'Clear any previous log backups for database ' + @DatabaseName + ' from folder ' + @BackupLocationPath
   + char(13) + char(10) + 'Statement executed: ' 
   + char(13) + char(10) + char(9) + @SQLStr
  exec @rc = master.dbo.xp_cmdshell @SQLStr
 end
else
 exec @rc = master.dbo.xp_cmdshell @SQLStr, NO_OUTPUT

select @ec = @@error
if @rc = 0
 begin
  if @ec = 0
   begin   
    set @SQLStr = 'del ' + @BackupLocationPath + @DatabaseName + '*.SLS /Q'
    if @debug = 1
     begin
      print 'Deleting backup file'
       + char(13) + char(10) + 'Statement executed: ' 
       + char(13) + char(10) + char(9) + @SQLStr
      exec @rc = master.dbo.xp_cmdshell @SQLStr
     end
    else
     exec @rc = master.dbo.xp_cmdshell @SQLStr, NO_OUTPUT

    select @ec = @@error
   end
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'Unable to clear previous log backups from file system folder ' + @BackupLocationPath 
    goto ErrorHandler
          end
 end

set @SQLStr = 'Alter database ' + @DatabaseName + ' set RECOVERY FULL'
if @debug = 1
 begin
  print 'setting recovery model to FULL'
   + char(13) + char(10) + 'Statement executed: ' 
   + char(13) + char(10) + char(9) + @SQLStr
 end
exec @rc = sp_executesql @SQLStr
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'Unable to set recovery model to full for database ' + @DatabaseName + ' on ' + @@servername
  goto ErrorHandler
 end

if @debug = 1
 print 'Clear the SLSLogShipping and SLSLogShippingLog tables of rows for database ' + @DatabaseName 
-- delete from log first because of foreign key
delete admin.dbo.SLSLogShippingLog where DatabaseName = @DatabaseName
delete admin.dbo.SLSLogShipping where DatabaseName = @DatabaseName

if @debug = 1
 print 'Update the SLSLogShipping table with the information provided for database ' + @DatabaseName 
insert admin.dbo.SLSLogShipping 
 (DatabaseName
 , StandbyServer
 , StandbyLinkedServer
 , PrimaryLinkedServer
 , BackupLocationPath
 , JobName
 , NotifyOnFail
 , MinuteInterval
 , HoursToKeep
 , MinutesAcceptableLatency 
 , NotifyOnLatent
 , BackupThreads
 , BackupPriority)
values
 (@DatabaseName
 , @StandbyServer
 , @StandbyLinkedServer
 , @PrimaryLinkedServer
 , @BackupLocationPath
 , @JobName
 , @NotifyOnFail
 , @MinuteInterval
 , @HoursToKeep
 , @MinutesAcceptableLatency 
 , @NotifyOnLatent
 , @BackupThreads
 , @BackupPriority)

set @ec = @@error
If @ec <> 0
 begin
  set @Err = 'insert into table admin.dbo.SLSLogShipping failed on ' + @@servername
  goto ErrorHandler
 end

if @debug = 1
 select  * from admin.dbo.SLSLogShipping where DatabaseName = @DatabaseName

if @debug = 1
 print 'Remove any existing job: ' + @JobName
if exists (select 1 from  msdb.dbo.sysjobs where name = @JobName)
 begin
  exec @rc = msdb.dbo.sp_delete_job @job_name = @JobName
  If @ec <> 0
   begin
    set @Err = 'failed to remove the existing job ' + @JobName + ' on ' + @@servername
    goto ErrorHandler
   end    
 end 

-- could also be an (active) job on the Standby server
-- First make sure no SQLLiteSpeed task is already running on this db over there 
set @SQLStr = 'if exists (select 1 from master.dbo.sysprocesses with(nolock)
  where program_name = ''SQLLiteSpeed'' 
  and dbid = db_id(' + char(39) + @DatabaseName + char(39) + ')) set @hr = 1 else set @hr = 0'
if @debug = 1
 begin
  print 'Check for active SQL LiteSpeed Log Shipping Job on Standby Server'
   + char(13) + char(10) + 'Statement executed: ' 
   + char(13) + char(10) + char(9) + @SQLStr
 end
exec @rc = sp_executesql @SQLStr, N'@hr int OUTPUT', @hr OUTPUT
set @ec = @@error
if @rc <> 0 or @ec <> 0 or @hr <> 0
 begin
  set @Err = 'SQL LiteSpeed process may already be active in database ' + @DatabaseName 
   + ' on ' + @StandByServer + '. 
   Retry Log Shipping init when that process completes.'   
  goto ErrorHandler
 end

-- the coast is clear
set @SQLStr = 'if exists (select 1 from [' + @StandByLinkedServer + '].msdb.dbo.sysjobs where name = '+ char(39) + @JobName + char(39) +
  + ') exec [' + @StandByLinkedServer + '].msdb.dbo.sp_delete_job @job_name = ' + char(39) + @JobName + char(39) + '
  select @hr = @@error'
if @debug = 1
 begin
  print 'Delete SQL LiteSpeed Log Shipping Job on Standby Server'
   + char(13) + char(10) + 'Statement executed: ' 
   + char(13) + char(10) + char(9) + @SQLStr
 end
exec @rc = sp_executesql @SQLStr, N'@hr int OUTPUT', @hr OUTPUT
set @ec = @@error
if @rc <> 0 or @ec <> 0 or @hr <> 0
 begin
  set @Err = 'Removal of log shipping job ' + @JobName + ' on Standby server ' + @StandbyServer + ' failed'
  goto ErrorHandler
 end

-- make sure a backup or restore is not already running on this db
if @debug = 1
 print 'Check for active SQL LiteSpeed Log Shipping Job on Primary Server'
if exists (select 1 from master.dbo.sysprocesses with(nolock)
  where (cmd like 'BACKUP%'
         or cmd like 'RESTORE%')
  and dbid = db_id(@DatabaseName))
 begin
  set @Err = 'A backup or restore is already active in database ' + @DatabaseName 
   + ' on ' + @@servername + '. 
  Retry log Shipping init when that process completes.'   
  goto ErrorHandler
 end

if @debug = 1
 print 'Performing Full Database Backup of ' + @DatabaseName
exec @rc = master.dbo.xp_backup_database
  @database = @DatabaseName
  , @filename = @BackupFileName
  , @init = 1
  , @threads = @BackupThreads
  , @priority = @BackupPriority
  , @logging = 1
set @ec = @@error
if @ec <> 0 or @rc <> 0
 begin
  set @Err = 'Backup of database ' + @DatabaseName + ' failed on ' + @@servername
  goto ErrorHandler
 end

if @debug = 1
 select 'Adding row to the SLSLogShippingLog table for successful backup of ' 
  + @DatabaseName + ' to ' + @BackupFileName
insert admin.dbo.SLSLogShippingLog (DatabaseName, BackupFileName)
values (@DatabaseName, @BackupFileName)

set @SQLStr = N'Execute @hr = [' + 
  @StandbyLinkedServer + '].admin.dbo.ApplySLSLogSecondary ' + 
  char(39) + @DatabaseName + char(39) + ', ' + 
--  char(39) + @PrimaryLinkedServer + char(39) + ', ' +
  char(39) + @BackupFileName + char(39) + ', ' +
  char(39) + isnull(@UndoFileName,'null') + char(39) + ', ' +
  isnull(cast(@RecoveryFlag as varchar(4)),'null')  + ', ' +
  cast(@debug as char(1))
if @debug = 1
 begin
  print 'Call ApplySLSLogSecondary on Standby to restore backup.'
   + char(13) + char(10) + 'Statement executed: ' 
   + char(13) + char(10) + char(9) + @SQLStr
 end
exec @rc = sp_executesql @SQLStr,N'@hr int OUTPUT',@hr OUTPUT
set @ec = @@error
if @rc <> 0 or @ec <> 0 or @hr <> 0
 begin
  set @Err = 'The full restore of database ' + @DatabaseName + ' on server ' 
   + @StandbyServer + ' to sync for log shipping has failed.'
   goto ErrorHandler
 end -- restore failed

-- to do
-- linked server timeout doesn't seem to manifiest itself in @ec, @rc or @hc?
-- if the update was done from StandByServer after the restore is complete then
-- a select added here to verify that should solve the problem
-- would need to add a linked server from Standy back to this server to acomplish this

if @debug = 1
 print 'Creating SQL LiteSpeed Log Shipping Job on Local Server'

if not exists (select 1 from msdb.dbo.syscategories where name = @JobCategory)  
 begin
  if @debug = 1
   print 'Adding job category: ' + @JobCategory

  exec @rc = msdb.dbo.sp_add_category 
    @class = 'Job'
    , @type = 'local'
    , @name = @JobCategory
  set @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'sp_add_category failed'
    goto ErrorHandler
   end
 end

if @debug = 1
 print 'Adding job: ' + @JobName
exec @rc = msdb.dbo.sp_add_job 
  @job_name = @JobName
  , @owner_login_name = @JobOwner
  , @description = @JobDesc
  , @category_name = @JobCategory
  , @enabled = 1
  , @notify_level_email = 0
  , @notify_level_page = 0
  , @notify_level_netsend = 0
  , @notify_level_eventlog = 2
  , @delete_level= 0
set @ec = @@error

if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'sp_add_job failed'
  goto ErrorHandler
 end

if @debug = 1
 print 'Adding step 1 to job: ' + @JobName
exec @rc = msdb.dbo.sp_add_jobstep 
  @job_name = @JobName
  , @step_id = 1
  , @step_name = 'Ship a Log'
  , @command = @JobCmd
  , @database_name = 'admin'
  , @database_user_name = ''
  , @subsystem = 'TSQL'
  , @cmdexec_success_code = 0
  , @flags = 4
  , @retry_attempts = 0
  , @retry_interval = 1
  , @output_file_name = @OutputFile
  , @on_success_step_id = 0
  , @on_success_action = 1
  , @on_fail_step_id = 2
  , @on_fail_action = 4
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'sp_add_jobstep 1 failed'
  goto ErrorHandler
 end

if @debug = 1
 print 'Adding step 2 for job: ' + @JobName
exec @rc = msdb.dbo.sp_add_jobstep 
  @job_name = @JobName
  , @step_id = 2
  , @step_name = 'failure notification'
  , @command = @JobCmd2
  , @database_name = 'admin'
  , @database_user_name = ''
  , @subsystem = 'TSQL'
  , @cmdexec_success_code = 0
  , @flags = 4
  , @retry_attempts = 0
  , @retry_interval = 1
  , @output_file_name = @OutputFile
  , @on_success_step_id = 0
  , @on_success_action = 2
  , @on_fail_step_id = 0
  , @on_fail_action = 2
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'sp_add_jobstep 2 failed'
  goto ErrorHandler
 end

if @debug = 1
 print 'Adding jobserver for job: ' + @JobName
exec @rc = msdb.dbo.sp_add_jobserver @job_name = @JobName, @server_name = '(local)' 
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'sp_add_jobserver failed'
  goto ErrorHandler
 end

if @debug = 1
 print 'Adding schedule for job: ' + @JobName
exec @rc = msdb.dbo.sp_add_jobschedule 
  @job_name = @JobName
  , @name = 'Log shipping schedule'
      , @enabled = 1
      , @freq_type = 4 -- daily
      , @freq_interval = 1 -- default
      , @freq_subday_type = 0x4 -- minutes
  , @freq_subday_interval = @MinuteInterval
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'sp_add_jobschedule failed'
  goto ErrorHandler
 end

exec @rc = msdb.dbo.sp_update_job @job_name = @JobName, @start_step_id = 1 
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'sp_update_job failed'
  goto ErrorHandler
 end

if @debug = 1
 print 'Starting job: ' + @JobName
exec @rc = msdb.dbo.sp_start_job @job_name = @JobName

if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'sp_start_job failed'
  goto ErrorHandler
 end

return

ErrorHandler:

set @ErrDesc = 'admin.dbo.InitSLSLogShipping failed.
error codes: | rc %d | hc %d | error %d |
parameters: |%s | %s | %s | %d | %d | %d | %s | %d | %d |
msg:' + isnull(@err,'no additional message text avaiable')

 raiserror (@ErrDesc
   , 16
   , 1
   , @rc
   , @hr
   , @ec
   , @DatabaseName
   , @StandbyServer
   , @BackupLocationPath
   , @MinuteInterval
   , @HoursToKeep
   , @MinutesAcceptableLatency
   , @NotifyOnLatent
   , @BackupThreads
   , @BackupPriority)
 return -1




GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO