-- Stored Procedure: dbo.GetSLSLogPrimary
-- 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].[GetSLSLogPrimary]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSLSLogPrimary]
GO





CREATE PROCEDURE [dbo].[GetSLSLogPrimary] 
 @DatabaseName sysname, -- Name of the database to ship logs
 @RecoveryFlag bit = 0, -- 0 for STANDBY, 1 for RECOVERY (null for NORECOVERY)
 @debug bit = 0 -- 0 for false, 1 for true
AS
/*******************************************************************************************************
* admin.dbo.GetSLSLogPrimary 
* Creator:       Bill Wunder
* Date:          3-21-2003
*
* Project:       SQLLiteSpeed based Log Shipping
*
* Description:   Generate a log backup on the Primary and invoke restore on StandBy
*                      steps:  verify that failover has not already occurred
*    get working values
*    if recovery specified, failover associated jobs
*    clean up expired backups for this log shipping instance    
*    backup the database log
*    apply log 
*
* Notes:         Procedure assumes execution under an account that is a local sysadmin 
*         and can access StandBy server via a preconfigured linked server as sysadmin. 
*         No output on success 
*         Associated jobs are proccessed at failover. Associted if a job by the same name 
*         exists on the primary server and the standby server and is enabled on primary
*         but disabled on standby
*                      If previous backups were not marked as restored in the SLSLogShippingLog table
*                      they are restored by this execution in datestamp order.
*                      Procedure InitSLSLogShipping must have sucessfully executed before this procedure 
*         
* Usage:
      EXECUTE admin.dbo.GetSLSLogPrimary 
   @DatabaseName ='pubs'
*
* Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*      bw                 5-7-03   never delete the most recent file through filestokeep processing                     
*      sn                 8-22-03  Changed the job status update (failover) process (when @RecoveryFlag = 1) to use system sp's
*******************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
declare @SQLStr nvarchar(510) -- dynamic sql statement
 , @Cmd varchar(510) -- dynamic command line 
 , @DateTimeString varchar(20) -- datetime literal used to build file names
 , @ErrDesc varchar(1024) -- message for raiserror
 , @Err varchar(510) -- failed point specific portion of @ErrDesc
 , @rc int -- return code of called procedures
 , @hr int -- return code from inside of dynamic sql
 , @ec int -- place holder for execution error not fatal to the batch
 , @UndoFileName varchar(256) -- Undo file on the standby server
 , @StandbyServer sysname -- linked server to Standby server to receive/apply the logs 
 , @StandbyLinkedServer sysname -- linked server to Standby server to receive/apply the logs 
 , @PrimaryLinkedServer sysname -- linked server from Standby server to notify of ship complete 
 , @BackupLocationPath varchar(256) -- UNC path to location of the log files
 , @HoursToKeep tinyint -- how long to keep files in backupshare after successfull restore in hours
 , @MinutesAcceptableLatency tinyint -- how long log shipping can have a problem before failing
 , @NotifyOnLatent varchar(100) -- who to notify if latent but not yet aged to point of failure
 , @BackupThreads tinyint -- Number of Threads to use for SQL LiteSpeed Backup, Use 1 if Unsure
 , @BackupPriority tinyint -- Base Priority of SQL LiteSpeed Backup, Use 0 if Unsure
 , @BackupFileName varchar(256) -- name of backup produced by this execution 
 , @RecoveredBackupFileName varchar(256) -- previous backups that recovered the standby database
 , @ExpiredBackupFileName varchar(256) -- previous backups to delete
 , @OldestUnrestoredBackupId int -- previous backups awaiting restore
 , @OldestUnrestoredBackupFileName varchar(256) -- previous backups awaiting restore
 , @OldestUnrestoredRecoveryFlag bit -- recoveryFlag stored with that previous backup
 , @OldestUnrestoredBackupDt datetime -- datetime of that previous backup
 , @OldestUnrestoredUndoFileName varchar(256) -- Undo file of that previous backup
 , @JobName varchar(2048) -- working variable from SLSLogShipping table
 , @Recovery varchar(10) -- literal mapped to RecoveryFlag
 , @Latency int -- minutes since last good backup or restore if this one failed
 , @Subject varchar(100) -- email subject for latency email based alerts
 , @Message varchar(1024) -- email messsage for latency email based alerts
 , @name sysname

---------------------------------------------
-- create temp tables
---------------------------------------------
create table #jobs (name sysname)
---------------------------------------------
-- set session variables
---------------------------------------------
-- set xact abort on to allow the linked server restore to fail without failing this proc.
-- will get messy if this proc is called inside a transaction
-- still makes it hard to see what the error was as it only goes to stdout
set xact_abort on
set nocount on
set @hr = 0
---------------------------------------------
-- body of stored procedure
---------------------------------------------

if @debug = 1
 print 'Get working values from log shipping control table'
select @StandbyServer = StandbyServer
 , @StandbyLinkedServer = StandbyLinkedServer
 , @PrimaryLinkedServer = PrimaryLinkedServer
 , @BackupLocationPath = BackupLocationPath
 , @JobName = JobName
 , @HoursToKeep = HoursToKeep
 , @MinutesAcceptableLatency = MinutesAcceptableLatency
 , @NotifyOnLatent = NotifyOnLatent
 , @BackupThreads = BackupThreads
 , @BackupPriority = BackupPriority
from admin.dbo.SLSLogShipping
where DatabaseName = @DatabaseName

if @@rowcount = 0
 begin
  set @Err = 'Database ' + @DatabaseName 
   + ' is not currently configured for SLS log shipping on ' + @@servername   
  goto ErrorHandler
 end

if @debug = 1
 print 'Check that Standby database has not already been recovered'
-- things are really messed up if there is more than one row here
select @RecoveredBackupFileName = BackupFileName
from admin.dbo.SLSLogShippingLog 
where DatabaseName = @DatabaseName
and ShipCompleteDt is not null
and RecoveryFlag = 1

If @RecoveredBackupFileName is not null
 begin
  if @debug = 1
   print 'Previous recoverey detected so disable log shipping job.'
  exec msdb.dbo.sp_update_job @job_name = @JobName, @enabled = 0

  set @Err = 'Database ' + @DatabaseName + ' on Standby Server ' + @StandbyServer 
   + ' has already been recovered by previous backup/restore of file ' 
   + @RecoveredBackupFileName 
   + ' Log shipping job has been disabled.'   
  goto ErrorHandler
 end

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

if @debug = 1
 print 'Generate the date/time stamp file name.'
set @DateTimeString = convert(varchar(20),current_timestamp,120)
set @BackupFileName = 
 @BackupLocationPath + rtrim(@DatabaseName) + 
 '_' +
 substring(@DateTimeString,1,4) + 
 substring(@DateTimeString,6,2) +
 substring(@DateTimeString,9,2) + 
 '_' + 
 substring(@DateTimeString,12,2) +
 substring(@DateTimeString,15,2) + 
 substring(@DateTimeString,18,2) +
 '_LOG.SLS'

set @UnDoFileName = replace(@BackupFileName,'.SLS','.tuf')

-- if controlled failover, failover jobs associated with this database
-- assumption is that such jobs will be enabled on the primary 
-- disabled on standby and have the same name

if @RecoveryFlag = 1
 begin
  if @debug = 1
   Print 'This is a failover so disable the log shipping job'
  exec @rc = msdb.dbo.sp_update_job @job_name = @JobName, @enabled = 0
  set @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'Unable to disablng job ' + @JobName + ' on ' + @@servername 
     + ' after recovering the log shipping StandBy database on ' + @StandByServer   
    goto ErrorHandler
   end

  -- any filtering of which jobs to failover can be added here
  -- disable this side first to stop changes before log backup 
  -- if more than one db is replicated jobs will get started with the first one failed over
  -- is this what you want?
  set @SQLStr = 'select src.name
  from msdb.dbo.sysjobs src
  join [' + @StandbyLinkedServer + '].msdb.dbo.sysjobs tgt
  on src.name = tgt.name
  where src.enabled = 1
  and tgt.enabled = 0 

  select @hr = @@error'

  if @debug = 1
   begin
    print 'Identify jobs on this server enabled with disabled mate on Standby'
     + char(13) + char(10) + 'Statement Executed: ' 
     + char(13) + char(10) + char(9) + @SQLStr
   end
  insert #jobs
  exec @rc = sp_executesql @SQLStr,N'@hr int OUTPUT',@hr OUTPUT

  set @ec = @@error

  if @ec = 0 and @rc = 0 and @hr = 0
   begin
    if @debug = 1
     print 'Disable jobs with disabled mate on Standby server.'
    
    select @name = min(name)
    from   #jobs

    while @name is not null
     begin
      exec @rc = msdb.dbo.sp_update_job @job_name = @name, @enabled = 0
 
      if @ec <> 0 or @rc <> 0 or @hr <> 0
       begin
        set @Err = 'Unable to fail over SQL Agent Jobs at Standby database recovery. Adjust all job status before proceeding'   
        goto ErrorHandler
       end
 
      select @name = min(name)
      from   #jobs
      where  name > @name
     end
   end

  if @ec = 0 and @rc = 0 and @hr = 0
   begin
    if @debug = 1
     print 'Enable jobs on Standby server that were just disabled on this server.'  

    select @name = min(name)
    from   #jobs

    while @name is not null
     begin
      set @SQLStr = 'exec @rc = ['+@StandbyLinkedServer+'].msdb.dbo.sp_update_job @job_name = @name, @enabled = 1'
      exec @hr = sp_executesql @SQLStr, N'@name sysname, @rc int OUTPUT', @name, @rc OUTPUT
 
      if @ec <> 0 or @rc <> 0 or @hr <> 0
       begin
        set @Err = 'Unable to fail over SQL Agent Jobs at Standby database recovery. Adjust all job status before proceeding'   
        goto ErrorHandler
       end
 
      select @name = min(name)
      from   #jobs
      where  name > @name
     end
   end

  if @ec <> 0 or @rc <> 0 or @hr <> 0
   begin
    set @Err = 'Unable to fail over SQL Agent Jobs at Standby database recovery. Adjust all job status before proceeding'   
    goto ErrorHandler
   end
 end

if @debug = 1
 print 'Check for active job: ' + @JobName
if exists(select 1
  from master.dbo.sysprocesses p with(nolock)
  join msdb.dbo.sysjobs j with(nolock)
  on substring(p.program_name,charindex('0x', p.program_name) + 18, 16)  = substring(replace(j.job_id, '-',''),17,16) 
  where  p.program_name like 'SQLAgent - TSQL JobStep%'
  and p.spid <> @@SPID
  and j.name = @JobName)
 begin
  set @Err = 'Job ' + @JobName + ' is already running. Cannot continue.'   
  goto ErrorHandler
 end

if @debug = 1
 print 'Remove any expired backups from the file system'
select @ExpiredBackupFileName = min(BackupFileName)
from admin.dbo.SLSLogShippingLog 
where ShipCompleteDt < dateadd(hh, -1*@HoursToKeep, getdate())

-- always leave most recent file, used for latency check and in general to see what last ran
while @ExpiredBackupFileName is not null and (select count(*) from admin.dbo.SLSLogShippingLog) > 1
 begin
  set @Cmd = 'dir ' +  @ExpiredBackupFileName
  if @debug = 1
   begin
    print 'Removing SQL LiteSpeed Backup file ' + @ExpiredBackupFileName
     + char(13) + char(10) + 'Command line executed: ' 
     + char(13) + char(10) + char(9) + @Cmd
    exec @rc = master.dbo.xp_cmdshell @Cmd
   end
  else
   exec @rc = master.dbo.xp_cmdshell @Cmd, NO_OUTPUT
  set @ec = @@error  
  if @ec <> 0
   begin
    set @Err = 'Error while checking for file ' + @ExpiredBackupFileName    
    goto ErrorHandler
   end
  if @rc = 0 -- found
   begin
    set @Cmd = 'del ' +  @ExpiredBackupFileName
    if @debug = 1
     begin
      print 'Removing SQL LiteSpeed Backup file ' + @ExpiredBackupFileName
       + char(13) + char(10) + 'Command line executed: ' 
       + char(13) + char(10) + char(9) + @Cmd
      exec @rc = master.dbo.xp_cmdshell @Cmd
     end
    else
     exec @rc = master.dbo.xp_cmdshell @Cmd, NO_OUTPUT
    set @ec = @@error  
    if @rc <> 0 or @ec <> 0
     begin
      set @Err = 'Unable to delete file ' + @ExpiredBackupFileName   
      goto ErrorHandler
     end
   end

  delete admin.dbo.SLSLogShippingLog
  where BackupFileName = @ExpiredBackupFileName

  select @ExpiredBackupFileName = min(BackupFileName)
  from admin.dbo.SLSLogShippingLog 
  where ShipCompleteDt < dateadd(hh, -1*@HoursToKeep, getdate())
 end


if @debug = 1
 print 'Check for active backup or restore on ' + @@servername
-- if a backup or restore is already running give it a minute to finish and retry
-- would rather exit before deleting file, but better to exit now to avoid timeout on .dll
if exists (select * from master.dbo.sysprocesses with(nolock)
  where (cmd like 'BACKUP%'
         or cmd like 'RESTORE%')
  and dbid = db_id(@DatabaseName))
 begin
  waitfor delay '00:01:00'
  if exists (select * from master.dbo.sysprocesses with(nolock)
    where (cmd like 'BACKUP%'
           or cmd like 'RESTORE%')
    and dbid = db_id(@DatabaseName))
   begin
    set @Err = 'Another backup or restore operation is preventing log backup of database ' 
     + @DatabaseName + ' on server ' + @@servername 
    goto ErrorHandler
   end
 end

-- do backup before checking for additional logs to restore
-- this sequence provide the longest interval between when the last restore succeeded
-- and when the primary side runs out of log space.
if @debug = 1
 print 'Backup database ' + @DatabaseName + ' log to ' + @BackupFileName
exec @rc = master.dbo.xp_backup_log 
 @database = @DatabaseName
 , @filename = @BackupFileName
 , @threads = @BackupThreads
 , @priority = @BackupPriority
 , @logging = 1
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'The log backup of database ' + @DatabaseName 
   + ' on server ' + @@servername + ' has failed.'
  goto ErrorHandler
 end

if @debug = 1
 print 'Add a row to the SLSLogShippingLog table for the successful backup.'
/* adding a check to make sure the header can be read here after repeated problems 
   with a backup failing because of non-fatal untrappable error from SQL LiteSpeed dll:
 Operation could not continue due to insufficient system resources (x80770003). 
 You may be running two many simultaneous Backup/Restore operations or a 
 system intensive application. BACKUP LOG is terminating abnormally. 
   probably technically more correct to check this with xp_restore_verifyonly, but verify
   can run as long as a restore, bad if file is good and usually file is good, headeronly
   is very fast and after testing always returns 10400 if above error caused backup to fail
   this is the same return value returned by verify.
*/
exec @rc = master.dbo.xp_restore_headeronly @filename = @BackupFileName
if @rc <> 10400
 insert admin.dbo.SLSLogShippingLog (DatabaseName, BackupFileName, UndoFileName, RecoveryFlag)
 values (@DatabaseName, @BackupFileName, @UndoFileName, @RecoveryFlag)

if @debug = 1
 print 'Apply the log, first process any previous backups that didn''t get restored'
-- min controls the order of the logs
select @OldestUnrestoredBackupId = min(Id)
from admin.dbo.SLSLogShippingLog
where DatabaseName = @DatabaseName
and ShipCompleteDt is null

-- restore any existing before trying a new log backup to help prevent a broken restore chain
while @OldestUnrestoredBackupId is not null
 begin   
  select @OldestUnrestoredRecoveryFlag = RecoveryFlag,
   @OldestUnrestoredBackupDt = BackupDt, 
   @OldestUnrestoredBackupFileName = BackupFileName,
   @OldestUnrestoredUndoFileName = UndoFileName
  from admin.dbo.SLSLogShippingLog
  where DatabaseName = @DatabaseName
  and Id = @OldestUnrestoredBackupId
  if @debug = 1
   print 'Restoring backup file: ' + @OldestUnrestoredBackupFileName
  -- For this to work, the standby server must be setup as a linked server
  -- and must have the DBAApplySLSLogSecondary stored procedure in the admin database 
--????  set @SQLStr = N'Execute @hr = [' + 
  set @SQLStr = N'[' + 
    @StandbyLinkedServer + '].admin.dbo.ApplySLSLogSecondary ' + 
    char(39) + @DatabaseName + char(39) + ', ' + 
--    char(39) + @PrimaryLinkedServer + char(39) + ', ' +
    char(39) + @OldestUnrestoredBackupFileName + char(39) + ', ' +
    char(39) + @OldestUnrestoredUndoFileName + char(39) + ', ' +
    cast(@OldestUnrestoredRecoveryFlag as char(1)) + ', ' +
    cast(@debug as char(1))

  if @debug = 1
   begin
    print 'Call ApplySLSLogSecondary on Standby'
     + 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
    -- make sure ShipCompleteDt for current row is null if the apply failed 
    -- added because of SQLLiteSpeed bug (SQLLiteSpeed JobId 2242)
    update admin.dbo.SLSLogShippingLog   
    set ShipCompleteDt = null
    where Id = @OldestUnrestoredBackupId 

    set @Err = 'The log restore of database ' + @DatabaseName 
     + ' on server ' + @StandbyServer + ' has failed.'
      goto ErrorHandler
   end -- restore failed
  else
   begin
    update admin.dbo.SLSLogShippingLog   
    set ShipCompleteDt = getdate()
    where Id = @OldestUnrestoredBackupId 

    -- this should never get executed here, but CYA
    if @OldestUnrestoredRecoveryFlag = 1 and @OldestUnrestoredBackupFileName <> @BackupFileName
     begin
      if @debug = 1
       print 'Disable the log shipping because StandBy database has been recovered'
      exec msdb.dbo.sp_update_job @job_name = @JobName, @enabled = 0
  
      set @Err = 'Database ' + @DatabaseName + ' on Standby Server ' + @StandbyServer 
       + ' has now been recovered by backup ' + @OldestUnrestoredBackupFileName 
       + ' Log shipping job has been disabled.'   
      goto ErrorHandler
     end

    -- If current restore succeeded see if there is another one      
    if not exists (select 1 from admin.dbo.SLSLogShippingLog
      where  Id = @OldestUnrestoredBackupId
      and ShipCompleteDt is null)
     select @OldestUnrestoredBackupId = min(Id)
     from admin.dbo.SLSLogShippingLog
     where DatabaseName = @DatabaseName
     and Id > @OldestUnrestoredBackupId
     and ShipCompleteDt is null
    else
     select @OldestUnrestoredBackupId = null
  end -- restore is ok
 end

Finish:

if @debug = 1
 print 'Log shipping cycle complete'
return

ErrorHandler:

 set @ErrDesc = 'admin.dbo.GetSLSLogPrimary %s failed. %s | @rc = %d | @hc = %d | @ec = %d |'
 raiserror (@ErrDesc,16,1, @DatabaseName, @Err, @rc, @hr, @ec)
 return -1
  




GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO