/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
You will find each stored procedure script delimited by a comment 
block similar to this one (all the !!!!!) Each must be saved to a 
separate file with the name specified in the comment block, all in the 
same folder in order to us this first procedure 
DBAConfigSLSLogShipping, to easily deploy a log shipping 
installation 

save first script to a file named dbo.DBAConfigSLSLogShipping.PRC
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/

use [admin]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[DBAConfigSLSLogShipping] 
	@StandbyServer sysname -- network name of the target server.
	, @ConfigFilesPath varchar(255) -- UNC path to the scripts for the log shipping procedures
	, @LogShippingUser sysname = 'sa' -- sql server login only
	, @LogShippingShare varchar(255) = null -- location to stage backup files (dft is \\<StandbyServer>\SLS_Tlogs)
	, @DropExistingTables varchar(5) = 'false'
AS
/*******************************************************************************************************
*	admin.dbo.DBAConfigSLSLogShipping 
*	Creator:       Bill Wunder
*	Date:          3-28-2003
*
*	Project:       SQL LiteSpeed based Log Shipping
*
*	Description:   Configure SQL Server pair (primary and standby servers) for SQL LiteSpeed 
*                      based log shipping. 
*	Notes: ONLY A domain account that is a sysadmin on both the Primary and Standby servers can 
*       	execute this procedure because it relys on trusted osql calls.
*        
*	Prerequisites for this SQL LiteSpeed Log Shipping:
* 	A database named admin must exist on the primary and the standby server
*	SQL Litespeed must be installed and working on the primary and the standby server
*	The @logShippingUser must be an existing SQL login on the primary and the standby SQL Servers
*       	note that this login will be made a member of sysadmin on both servers by this procedure   
* 		The password for the login must be the same on both servers or the linked server must
* 		be configured to map the password between servers (not reccommended)
*	SQL Server Agent must be running on the primary server in the Log Shippping pair
*	Application Jobs that must begin running on the standby server at fail over should already be 
*		copied to the standby server. They will be started when GetSLSLogPrimary is executed
*               with @RecoveryFalg = 1 
*	The primary server SQL Agent service account must have sysadmin access to the standby server 
*		preferably use the same domain service logon account for both servers
*	A network share, shared as \\<StandbyServerName>\SLS_Tlogs must exist where the full 
*		backup and all log backup files will be stored
*	The primary server SQL Server service , SQL Agent service, and the command prompt proxy (if used) 
*		 accounts must have change access to this share
*       The primary and standby SQL Server service, SQL Agent service, and command prompt proxy (if used) 
*		accounts must be able to access [HKLM].[Software].[DBAssociates]
*	There must be a reliable network connection between the primary and the standby server
*       There shoud be no white space in the @ConfigFilesPath due to osql challenges
*	These procedures must already exist on the primary server
*       This procedure expects to find the following scripts at the @ConfigFilesPath location. 
*		dbo.InitSLSLogShipping.PRC       
*		dbo.GetSLSLogPrimary.PRC       
*		dbo.ApplySLSLogSecondary.PRC
*		dbo.ExpungeUsers.PRC		       
*		dbo.log_job_error.PRC
*       
*	Usage:
	     EXECUTE admin.dbo.DBAConfigSLSLogShipping 
			@StandByServer = 'Standby'
			, @ConfigFilesPath = '\\WorkStation\C$\log_shipping'
			, @logShippingUser = 'test'
			, @logShippingShare = '\\FileServer\SLS_Tlogs'
			, @DropExistingTables = 'true'
*
*	Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*                    
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
DECLARE 
	@CmdStr varchar(1024)
	, @BackupLocationPath varchar(255)
	, @rc int
	, @ec int
	, @SqlStr nvarchar(1024)
	, @StandByLinkedServer varchar(128)		-- WSOD conforming linked Server name
	, @ErrDesc varchar(8000)
	, @Err varchar(1024)
	, @PreviousLogShippingUser sysname
	, @PreviousLogShippingShare varchar(255)
---------------------------------------------
-- create temp tables
---------------------------------------------

---------------------------------------------
-- set session variables
---------------------------------------------
SET NOCOUNT ON
if substring(reverse(@ConfigFilesPath),1,1) <> '\'
	set @ConfigFilesPath = @ConfigFilesPath + '\'
if @LogShippingShare is null
	set @LogShippingShare = '\\' + @StandbyServer + '\SLS_Tlogs'
---------------------------------------------
-- body of stored procedure
---------------------------------------------
print 'Checking user ' + suser_sname() + '''s authority to configure log shipping.'
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 not a Windows login on ' + @@servername + '. SQL logins not permitted here.'
		goto ErrorHandler
	end

if @StandByServer = @@servername 
	begin
		set @Err = 'Cannot ship logs back to ' + @StandbyServer + '. Specify a different server as the standby server.'
		goto ErrorHandler
	end

print 'Assure the log shipping user exists and is sysadmin on ' + @@servername + ' and ' + @StandbyServer
--  required for backup/restore API (virtual device)  
--  the service account will also need access to the    
--  SQL Litespeed registry and the file system locations 
if @LogShippingUser != 'sa'
	begin
		print 'make ' + @LogShippingUser + ' sysadmin on ' + @@servername
		exec @rc = sp_addsrvrolemember @logShippingUser, sysadmin
		set @ec = @@error
		if @rc <> 0 or @ec <> 0
			begin
				set @Err = 'Failed to add ' + @logShippingUser + ' to sysadmin role on ' + @@servername
				goto ErrorHandler
			end

		print 'make ' + @LogShippingUser + ' sysadmin on ' + @StandbyServer
		set @CmdStr = 'osql -E -S' + @StandbyServer + ' -n -dadmin -Q"exec sp_addsrvrolemember ' + 
				+ @LogShippingUser + ', sysadmin" -b'
		print 'Osql command to execute : ' + @CmdStr
		exec @rc = master.dbo.xp_cmdshell @CmdStr
		set @ec = @@error
		if @rc <> 0 or @ec <> 0
			begin
				set @Err = 'Failed to add ' + @logShippingUser + ' to sysadmin role on ' + @StandbyServer
				goto ErrorHandler
			end

	end -- LogShippingUser not sa

print 'Validate configuration files file system location: ' + @ConfigFilesPath
set @Cmdstr = 'dir ' + @ConfigFilesPath
print 'Command line to execute : ' + @CmdStr
exec @rc = master.dbo.xp_cmdshell @CmdStr
select @ec = @@error
if @rc <> 0 or @ec <> 0
	begin
		set @Err = 'Unable to access file system location ' + @ConfigFilesPath + ' from ' + @@servername
		goto ErrorHandler
	end

if @DropExistingTables = 'true'
	begin
		select 'Drop any existing log shipping control tables on ' + @@servername
		if object_id('admin.dbo.SLSLogShippingLog','U') is not null 
			begin
				print char(9) + 'admin.dbo.SLSLogShippingLog'
				drop table admin.dbo.SLSLogShippingLog
			end
		if object_id('admin.dbo.SLSLogShipping','U') is not null
			begin 	
				print char(9) + 'admin.dbo.SLSLogShipping'
				drop table admin.dbo.SLSLogShipping
			end
		if object_id('admin.dbo.SLSLogShippingConfig','U') is not null 
			begin			
				print char(9) + 'admin.dbo.SLSLogShippingConfig'
				drop table admin.dbo.SLSLogShippingConfig
			end
	end

print 'Create log shipping control tables on ' + @@servername + ' (if not exists)'
if object_id('admin.dbo.SLSLogShippingConfig','U') is null 
	begin
		print char(9) + 'admin.dbo.SLSLogShippingConfig'
		create table admin.dbo.SLSLogShippingConfig
			(StandbyServer sysname not null
			, LogShippingUser sysname not null
			, LogShippingShare varchar(255) not null
			, ConfigFilesPath varchar(255) not null
			, ConfigDt datetime not null
			  constraint dft_SLSLogShippingConfig__ConfigDt 
				default getdate()
			, ConfigUser sysname not null
			  constraint dft_SLSLogShippingConfig__ConfigUser 
				default suser_sname()
			, constraint pk_SLSLogShippingConfig__StandbyServer
				primary key (StandbyServer))
		set @ec = @@error
		If @ec <> 0
			begin
				set @Err = 'create table admin.dbo.SLSLogShippingConfig failed on ' + @@servername
				goto ErrorHandler
			end
	end

if object_id('admin.dbo.SLSLogShipping','U') is null 
	begin
		print char(9) + 'admin.dbo.SLSLogShipping'
		create table admin.dbo.SLSLogShipping
			(DatabaseName sysname not null
			, StandbyServer sysname not null
			, StandbyLinkedServer sysname not null
			, PrimaryLinkedServer sysname not null
			, BackupLocationPath varchar(256) not null
			, JobName sysname not null
			, NotifyOnFail varchar(100) not null
			, MinuteInterval tinyint not null
			, HoursToKeep tinyint not null
			, MinutesAcceptableLatency tinyint not null
			, NotifyOnLatent varchar(100) not null
			, BackupThreads tinyint not null
			, BackupPriority tinyint not null
			, InitDt datetime not null
			  constraint dft_SLSLogShipping__InitDt 
				default getdate()
			, InitUser sysname not null
			  constraint dft_SLSLogShipping__InitUser 
				default suser_sname()
			, constraint pk_SLSLogShipping__DatabaseName
				primary key (DatabaseName)
			, constraint fk_SLSLogShipping__StandbyServer__to__SLSLogShippingConfig
			  foreign key (StandbyServer)
				references SLSLogShippingConfig (StandbyServer))
		set @ec = @@error
		If @ec <> 0
			begin
				set @Err = 'create table admin.dbo.SLSLogShipping failed on ' + @@servername
				goto ErrorHandler
			end
	end

if object_id('admin.dbo.SLSLogShippingLog','U') is null 
	begin
		print char(9) + 'admin.dbo.SLSLogShippingLog'
		create table admin.dbo.SLSLogShippingLog
			(Id int identity(1,1) not null
			, DatabaseName sysname not null
			, BackupFileName varchar(256) not null
			, UndoFileName varchar(256) null
			, RecoveryFlag bit null	-- if null then NORECOVERY (xp_restore_... default)
			, ShipCompleteDt datetime null -- if null the not restored
			, BackupUser sysname not null
			  constraint dft_SLSLogShippingLog__BackupUser
				default suser_sname()
			, BackupDt datetime not null
			  constraint dft_SLSLogShippingLog__BackupDt 
				default getdate()
			, constraint pkc_SLSLogShippingLog__Id
			  primary key (Id)
			, constraint ukn_SLSLogShippingLog__DatabaseName__BackupFileName
				unique (DatabaseName, BackupFileName)
			, constraint fk_SLSLogShippingLog__DatabaseName__to__SLSLogShipping
			  foreign key (DatabaseName)
				references SLSLogShipping (DatabaseName))
		set @ec = @@error
		If @ec <> 0
			begin
				set @Err = 'create table admin.dbo.SLSLogShippingLog failed on ' + @@servername
				goto ErrorHandler
			end
	end

-- exec the admin.dbo.SLSLogShippingConfig DML because this proc must support any schema changes
-- do this check before creating procs to possibly avoid proc compile failures if schema changes
print 'Check for existing values in admin.dbo.SLSLogShippingConfig table on ' + @@servername
set @SQLStr = 'select @PreviousLogShippingUser = LogShippingUser
		, @PreviousLogShippingShare = LogShippingShare
		from admin.dbo.SLSLogShippingConfig
		where StandbyServer = @StandbyServer'
exec @rc = sp_executesql @SQLStr, 
	N'@PreviousLogShippingUser sysname OUTPUT, @PreviousLogShippingShare varchar(255) OUTPUT, @StandByServer sysname'
	, @PreviousLogShippingUser OUTPUT, @PreviousLogShippingShare OUTPUT, @StandbyServer
set @ec = @@error
if @rc <> 0 or @ec <> 0
	begin
		set @Err = 'Specify @DropExistingTables = ''true'' to change existing schema.'
		goto ErrorHandler
	end

print 'Assure that anyone with access to admin db on ' + @@servername + ' can set ShipCompleteDt.'
if suser_sid('NT AUTHORITY\ANONYMOUS LOGON') is null
	exec sp_grantlogin [NT AUTHORITY\ANONYMOUS LOGON]
if user_id('NT AUTHORITY\ANONYMOUS LOGON') is null
	exec sp_grantdbaccess [NT AUTHORITY\ANONYMOUS LOGON]
grant select on dbo.SLSLogShippingLog to [NT AUTHORITY\ANONYMOUS LOGON]
grant update (ShipCompleteDt) on dbo.SLSLogShippingLog to public

print 'Creating procedures on ' + @@servername
print 'Creating procedure to add a linked server on ' + @@servername
set @CmdStr = 'osql -E -S' + @@servername + ' -n -dadmin -i"' 
		+ @ConfigFilesPath + 'dbo.AddLinkedServer.PRC" -b'
print 'Osql command to execute : ' + @CmdStr
exec @rc = master.dbo.xp_cmdshell @CmdStr
set @ec = @@error
if @rc <> 0 or @ec <> 0
	begin
		set @Err = 'Failed to add procedure admin.dbo.AddLinkedServer to ' + @@servername
		goto ErrorHandler
	end

print 'Creating Log Shipping Initialization procedure on ' + @@servername
set @CmdStr = 'osql -E -S' + @@servername + ' -n -dadmin -i"' 
		+ @ConfigFilesPath + 'dbo.InitSLSLogShipping.PRC" -b'
print 'Osql command to execute : ' + @CmdStr
exec @rc = master.dbo.xp_cmdshell @CmdStr
set @ec = @@error
if @rc <> 0 or @ec <> 0
	begin
		set @Err = 'Failed to add procedure admin.dbo.InitSLSLogShipping to ' + @@servername
		goto ErrorHandler
	end

print 'Creating Log Shipping Backup Procedure on ' + @@servername
set @CmdStr = 'osql -E -S' + @@servername + ' -n -dadmin -i"' 
		+ @ConfigFilesPath + 'dbo.GetSLSLogPrimary.PRC" -b'
print 'Osql command to execute : ' + @CmdStr
exec @rc = master.dbo.xp_cmdshell @CmdStr
set @ec = @@error
if @rc <> 0 or @ec <> 0
	begin
		set @Err = 'Failed to add procedure admin.dbo.GetSLSLogPrimary to ' + @@servername
		goto ErrorHandler
	end

print 'Create error notification procedure on ' + @@servername
set @CmdStr = 'osql -E -S' + @@servername + ' -n -dadmin -i"' 
		+ @ConfigFilesPath + 'dbo.NotifySLSLogShippingFailure.PRC" -b'
print 'Osql command to execute : ' + @CmdStr
exec @rc = master.dbo.xp_cmdshell @CmdStr
set @ec = @@error
if @rc <> 0 or @ec <> 0
	begin
		set @Err = 'Failed to add procedure admin.dbo.NotifySLSLogShippingFailure to ' + @@servername
		goto ErrorHandler
	end

print 'Creating procedures on ' + @StandbyServer
print 'Creating procedure to add a linked servers on ' + @StandbyServer
set @CmdStr = 'osql -E -S' + @StandbyServer + ' -n -dadmin -i"' 
		+ @ConfigFilesPath + 'dbo.AddLinkedServer.PRC" -b'
print 'Osql command to execute : ' + @CmdStr
exec @rc = master.dbo.xp_cmdshell @CmdStr
set @ec = @@error
if @rc <> 0 or @ec <> 0
	begin
		set @Err = 'Failed to add procedure admin.dbo.AddLinkedServer to ' + @StandbyServer
		goto ErrorHandler
	end

print 'Creating Log Shipping Restore Procedure on ' + @StandbyServer
set @CmdStr = 'osql -E -S' + @StandbyServer + ' -n -dadmin -i"' 
		+ @ConfigFilesPath + 'dbo.ApplySLSLogSecondary.PRC" -b'
print 'Osql command to execute : ' + @CmdStr
exec @rc = master.dbo.xp_cmdshell @CmdStr
set @ec = @@error
if @rc <> 0 or @ec <> 0
	begin
		set @Err = 'Failed to add procedure admin.dbo.ApplySLSLogSecondary to ' + @StandbyServer 
		goto ErrorHandler
	end

print 'Creating SPID killing procedure on ' + @StandbyServer
set @CmdStr = 'osql -E -S' + @StandbyServer + ' -n -dadmin -i"' 
		+ @ConfigFilesPath + 'dbo.ExpungeUsers.PRC" -b'
print 'Osql command to execute : ' + @CmdStr
exec @rc = master.dbo.xp_cmdshell @CmdStr
set @ec = @@error
if @rc <> 0 or @ec <> 0
	begin
		set @Err = 'Failed to add procedure admin.dbo.ExpungeUsers to ' + @StandbyServer
		goto ErrorHandler
	end

print 'Verify log shipping file share from ' + @@servername
set @CmdStr = 'dir ' + @LogShippingShare
print 'Command line to execute : ' + @CmdStr
exec @rc = master.dbo.xp_cmdshell @CmdStr
set @ec = @@error
if @rc <> 0 or @ec <> 0
	begin
		set @Err = 'Share \\' + @StandbyServer + '\SLS_Tlogs not found. Create before proceeding.'
		goto ErrorHandler
	end

-- dynamic exec the admin.dbo.SLSLogShippingConfig DML because this proc must support any schema changes
print 'Maintain admin.dbo.SLSLogShippingConfig table on ' + @@servername
if @PreviousLogShippingUser is not null	
	begin
		print 'Updating admin.dbo.SLSLogShippingConfig on ' + @@servername
		set @SQLStr = 'update admin.dbo.SLSLogShippingConfig
			set 	LogShippingUser = ''' + @LogShippingUser + '''
			, LogShippingShare = ''' + @LogShippingShare + '''
			, ConfigFilesPath = ''' + @ConfigFilesPath + '''
			where StandbyServer = ''' + @StandbyServer + ''''
		exec @rc = sp_executesql @SQLStr	
		set @ec = @@error
		if @rc <> 0 or @ec <> 0
			begin
				set @Err = 'Update of admin.dbo.SLSLogShippingConfig failed.'
				goto ErrorHandler
			end

		print 'Log shipping had already been configured from ' + @@servername + ' to ' + @StandbyServer 
			+ char(13) + char(10) + 'Existing configuration has been updated with files from '
			+ char(13) + char(10) + char(9) + @ConfigFilesPath				

		if @PreviousLogShippingUser <> 'sa' and @PreviousLogShippingUser <> @LogShippingUser
			print 'You may want to remove sysadmin access for previous log shipping user "' 
				+ @PreviousLogShippingUser + '" on ' + @@servername + ' and ' + @StandbyServer + '.'
		if @PreviousLogShippingShare is not null and @PreviousLogShippingShare <> @LogShippingShare
			print 'You may want to remove the previous log shipping share ' + @PreviousLogShippingShare 

		if @DropExistingTables = 'true'
			print 'Because option to drop existing log shipping tables was specified it is necessary ' 
				+ ' to execute admin.dbo.InitSLSLogShipping before log shipping will resume.'
			
	end
else
	begin
		print 'Adding admin.dbo.SLSLogShippingConfig row on ' + @@servername
		set @SQLStr = 'insert admin.dbo.SLSLogShippingConfig
			(StandbyServer
			, LogShippingUser
			, LogShippingShare
			, ConfigFilesPath)
		values (''' + @StandbyServer + '''
			, ''' + @LogShippingUser + '''
			, ''' + @LogShippingShare + '''
			, ''' + @ConfigFilesPath + ''')'

		exec @rc = sp_executesql @SQLStr	
		set @ec = @@error
		if @ec <> 0 
			begin
				set @Err = 'Insert to table admin.dbo.SLSLogShippingConfig failed.'
				goto ErrorHandler
			end
	end

print 'Log shipping configuration complete.'
return

ErrorHandler:

	set @ErrDesc = 'admin.dbo.DBAConfigSLSLogShipping error:  
	Return Code ' + convert(varchar(10),isnull(@RC,'-0')) + ' 
	Error Code ' +  + convert(varchar(10),isnull(@EC,'-0')) + ' 
	occurred on server %s
		@StandbyServer = %s
		@LogShippingUser = %s
		@ConfigFilesPath = %s
		@DropExistingTables = %s

	' + isnull(@err,'no additional text available') 
	raiserror (@ErrDesc,16,1,@@servername,@StandbyServer,@LogShippingUser,@ConfigFilesPath,@DropExistingTables)
	return -1


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON 
GO
---------------------------------------------
-- permissions (please use a 'GO' after each permissions grant)
---------------------------------------------
--GRANT EXECUTE on [dbo].[DBAConfigSLSLogShipping] to [role group or user]
GO

/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
save next script to a file named dbo.InitSLSLogShipping.PRC
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/

use [admin]
GO
SET QUOTED_IDENTIFIER OFF
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) = 'DBOps@wallst.com;DataCenter@wallst.com' -- 'DMSQLNotify@wallst.com'     
	, @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) = 'DBOps@wallst.com' 
	, @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:       SQL LiteSpeed based Log Shipping
*
*	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 = 'StandBy'
			, @NotifyOnLatent = 'bill.wunder@wallst.com'
			, @NotifyOnFail = 'bill.wunder@wallst.com'

			, @BackupLocationPath = '\\Standby\SLS_TLogs'
			, @MinuteInterval = 5
			, @HoursToKeep = 1
			, @MinutesAcceptableLatency = 180
			, @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 = 'sa'
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 + ''', NO_OUTPUT'
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) + @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
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 'Updating step 1 of job: ' + @JobName
exec @rc = msdb.dbo.sp_update_jobstep 
		@job_name = @JobName
		, @step_id = 1
		, @on_fail_step_id = 2
		, @on_fail_action = 4
set @ec = @@error
if @rc <> 0 or @ec <> 0
	begin
		set @Err = 'sp_update_jobstep 1 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 OFF
GO
SET ANSI_NULLS ON 
GO
---------------------------------------------
-- permissions (please use a 'GO' after each permissions grant)
---------------------------------------------
--GRANT EXECUTE on [dbo].[InitSLSLogShipping] to [role group or user]
GO

/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
save next script to a file named dbo.GetSLSLogPrimary.PRC
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/

use [admin]
GO
SET QUOTED_IDENTIFIER OFF
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:       SQL LiteSpeed 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
*          ------------------ -------- ------------------------------------------------------------
*      Scott Nelson           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'@rc int OUTPUT', @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'[' + 
				@StandbyLinkedServer + '].admin.dbo.ApplySLSLogSecondary ' + 
				char(39) + @DatabaseName + 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 OFF
GO
SET ANSI_NULLS ON 
GO
---------------------------------------------
-- permissions (please use a 'GO' after each permissions grant)
---------------------------------------------
--GRANT EXECUTE on [dbo].[GetSLSLogPrimary] to [user or role]
GO

/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
save next script to a file named dbo.ApplySLSLogSecondary.PRC
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/

use [admin]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[ApplySLSLogSecondary] 
	@DatabaseName sysname,
	@BackupFileName nvarchar(256) = null, -- if recovery only, no file needed
	@UndoFile nvarchar(256) = null, -- only required if recovery mode is STANDBY
	@RecoveryFlag bit = 0,	 -- 0 for STANDBY - 1 for RECOVERY - null for NORECOVERY
	@debug bit = 0
AS
/*******************************************************************************************************
*	admin.dbo.ApplySLSLogSecondary 
*	Creator:       
*	Date:          3-28-2003
*
*	Project:       SQL LiteSpeed Log Shipping
*
*	Description:   Apply log to Standby Server
*	Notes:         called by admin.dbo.GetSLSLogPrimary
*		       made for web page requiring no ouput on success	
*	Usage:
	     EXECUTE admin.dbo.ApplySLSLogSecondary 
			@DatabaseName = 'pubs'
			, @PrimaryLinkedServer = 'lnkSLSLogShipping__From__SOURCE__pubs'
			, @BackupFileName = '\\StandBy\SLS_TLogs\pubs_200303211357_Log.sls'
			, @UndoFile = '\\StandBy\SLS_TLogs\pubs_200303211357.tuf'
			, @recoveryflag = 1
			, @debug = 1	
*
*
*	Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*                    
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
declare @withstr nvarchar(255),
	@ErrDesc nvarchar(510),
	@Err nvarchar(255),
	@rc int,
	@ec int,
	@hr int,
	@SQLStr nvarchar(1024)

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

---------------------------------------------
-- set session variables
---------------------------------------------
SET NOCOUNT ON

---------------------------------------------
-- body of stored procedure
---------------------------------------------
if @debug = 1
	print 'Verify that undo file name provided for restore with STANDBY'
If @UndoFile is null and @RecoveryFlag = 0
	begin
		set @Err = 'Restore with STANDBY specified but no undo file provided for restore of ' + @DatabaseName 
			+ ' on ' + @@servername + '. Specify a different recovery mode or provide an undo file name.'			
		goto ErrorHandler
	end

if @debug = 1
	print 'Verify that a backup/restore operation is not already active in the target database.'
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 = 'A backup or restore operation is already active in database ' + @DatabaseName 
			+ ' on ' + @@servername + '. Retry log restore when that process completes.'			
		goto ErrorHandler
	end

if @debug = 1
	print 'Removing all spids connected to database ' + @DatabaseName 
exec @rc = admin.dbo.ExpungeUsers @DatabaseName	
set @ec = @@error 	
if @rc <> 0 or @ec <> 0
	begin
		set @Err = 'Unable to remove user connection from ' + @DatabaseName + ' on ' + @@servername
		goto ErrorHandler
	end

if @RecoveryFlag is null
	select @withstr = 'NORECOVERY'
if @RecoveryFlag = 0
	select @withstr = 'STANDBY="' + @UndoFile + '"'
if @RecoveryFlag = 1
	select @withstr = 'RECOVERY'
		
if @BackupFileName is not null
	begin
		if @debug = 1
			print 'Restoring SQL LiteSpeed Backup of ' + @DatabaseName + ' from backup file ' + @BackupFileName
		if charindex(reverse('_Log.sls'),reverse(@BackupFileName)) = 1
			exec @rc = master.dbo.xp_restore_log
					@database = @DatabaseName 
					, @filename = @BackupFileName 
					, @with = @withstr
					, @logging = 1
		else
			exec @rc = master.dbo.xp_restore_database
					@database = @DatabaseName 
					, @filename = @BackupFileName 
					, @with = @withstr
					, @logging = 1
		set @ec = @@error 	
		if @rc <> 0 or @ec <> 0
			begin
				set @Err = 'Unable to restore log backup file ' + @BackupFileName 
					+ ' to database ' + @DatabaseName + ' on ' + @@servername
				goto ErrorHandler		
		 	end

	end	

else
	begin
		set @SQLStr = 'restore database ' + @DatabaseName + ' with recovery'
		if @debug = 1
			print 'Recovering database ' + @DatabaseName + ' only, no restore file provided.'
				+ char(13) + char(10) + 'SQL Command executed:'
				+ char(13) + char(10) + char(9) + @SQLStr
		exec @rc = sp_executesql @SQLStr

		set @ec = @@error 	
		if @rc <> 0
			begin
				set @Err = 'Failed to recover database ' + @DatabaseName + ' on ' + @@servername
				goto ErrorHandler		
		 	end
	end

return

ErrorHandler:
set @ErrDesc = 'admin.dbo.ApplySLSLogSecondary Failed |	return code %d | error code %d | @DatabaseName %s | @BackupFileName %s | Err$ %s'
raiserror (@ErrDesc,16,1,@rc, @ec, @DatabaseName, @BackupFileName, @Err)
return -1   
  
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON 
GO
---------------------------------------------
-- permissions (please use a 'GO' after each permissions grant)
---------------------------------------------
--GRANT EXECUTE on [dbo].[ApplySLSLogSecondary] to [user or group]
GO

/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
save next script to a file named dbo.NotifySLSLogShippingFailure.PRC
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/


use [admin]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[NotifySLSLogShippingFailure] 
	@DatabaseName sysname	  
AS
/*******************************************************************************************************
*	admin.dbo.NotifySLSLogShippingFailure 
*	Creator:       Bill Wunder
*	Date:          5-2-2003
*
*	Project:       SQL LiteSpeed based Log Shipping
*
*	Description:   If job fails send to correct recipient based on preconfigured latency interval
*	Notes:         Purpose of two level notification is to avoid so called "false positive"
*                      failure reporting to data center and developers yet still provide full visibility
*                      to transient and/or self correcting interruptions to the log shipping process
*		       Works with safe_sendmail or xp_sendmail	
*	Usage:
	     EXECUTE admin.dbo.NotifySLSLogShippingFailure 'pubs'
*
*
*	Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*                    
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
declare @JobName sysname
	, @MinutesAcceptableLatency int
	, @Latency int
	, @NotifyOnFail varchar(100) 
	, @NotifyOnLatent varchar(100)
	, @Recipients varchar(100)
	, @Subject varchar(100)
	, @Message varchar(8000)
	, @rc int
	, @ec int
---------------------------------------------
-- create tables
---------------------------------------------

---------------------------------------------
-- set session variables
---------------------------------------------
SET NOCOUNT ON

---------------------------------------------
-- body of stored procedure
---------------------------------------------
select @MinutesAcceptableLatency = MinutesAcceptableLatency
	, @NotifyOnFail = NotifyOnFail
	, @NotifyOnLatent = NotifyOnLatent
	, @JobName = JobName
from admin.dbo.SLSLogShipping
where DatabaseName = @DatabaseName

select @Latency = case when max(lastbackup.BackupDt) > max(lastrestore.BackupDt) 
				or max(lastrestore.BackupDt) is null
			then datediff(mi,max(lastbackup.BackupDt),getdate()) 
			else datediff(mi,max(lastrestore.ShipCompleteDt),getdate()) 	
			end
from admin.dbo.SLSLogShippingLog lastbackup
left join admin.dbo.SLSLogShippingLog lastrestore
on lastbackup.DatabaseName = lastrestore.DatabaseName
where lastbackup.DatabaseName = @DatabaseName
and lastrestore.ShipCompleteDt is not null

if @Latency < @MinutesAcceptableLatency
	begin
		set @Recipients = @NotifyOnLatent
		set @Subject = @JobName + ': latency warning'

		select top 1 @Message = h.message
		from msdb.dbo.sysjobhistory h
		inner join msdb.dbo.sysjobs j
		on h.job_id = j.job_id
		where j.name = @JobName
		and message not like ('% succeeded%')
		order by h.run_date desc, h.run_time desc

		select @Message = 'It has been ' + cast(@Latency as varchar(4)) 
			+ ' minutes since the last successful log shipping activity. There are now ' 
			+ cast(count(*) as varchar(10))	+ ' log backups awaiting restore on the Standby Server. '
			+ ' Job will enter failed state in ' + cast(@MinutesAcceptableLatency-@Latency as varchar(4)) 
			+ ' minutes. ' +  @Message	 
		from admin.dbo.SLSLogShippingLog		
		where DatabaseName = @DatabaseName
		and ShipCompleteDt is null

		-- use the xp_smtp_sendmail hook if available otherwise call xp_sendmail
		if object_id('sysmon.dbo.safe_sendmail') is not null
			exec @rc = sysmon.dbo.safe_sendmail @recipients = @Recipients, 
							@subject = @Subject, 
							@message = @Message
		else
			exec @rc = master.dbo.xp_sendmail @recipients = @recipients, 
							@subject = @subject, 
							@message = @message

	end
else
		exec @rc = admin.dbo.log_job_error @NotifyOnFail

set @ec = @@error

-- raise a level 16 (or 19 WITH LOG?) error if send fails
if @rc <> 0 or @ec <> 0
	Raiserror ('%s Failed! Notification failed during send. Review job history for details.',16,1, @JobName)	

RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON 
GO
---------------------------------------------
-- permissions (please use a 'GO' after each permissions grant)
---------------------------------------------
--GRANT EXECUTE on [dbo].[NotifySLSLogShippingFailure] to [role group or user]
GO

/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
save next script to a file named dbo.ExpungeUsers.PRC
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/

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

CREATE PROCEDURE [dbo].[ExpungeUsers] 
	@database nvarchar(128),
	@maxretry int = 3            -- number of attempts to remove users from database before failing
AS
/*******************************************************************************************************
*	admin.dbo.ExpungeUsers 
*	Creator:	     Bill Wunder
*	Date: 		12-06-2002
*
*	Project:	     ForwardRecovery
*	Project Mgr:	
*	Dev Contact:	
*
*	Description:	get rid of connections in a database
*	       	
*	Usage:
*	     EXECUTE ExpungeUsers BillWTest
*
*
*	Modifications:   
*         Developer Name     Date     Brief Description
*         ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
declare @spid int,
	@sqlstr nvarchar(20),
	@retry int

---------------------------------------------
-- create temp tables
---------------------------------------------
declare @spidsInDB table (spid int)

---------------------------------------------
-- set session
---------------------------------------------
SET NOCOUNT ON

set nocount on
set @maxretry = 3
set @retry = 0

---------------------------------------------
-- body of stored procedure
---------------------------------------------

-- kill spids
insert @spidsInDB
select spid
from master.dbo.sysprocesses
where spid > 50
and dbid = db_id(@database)
and spid <> @@spid

while @@rowcount > 0 and @retry <= @maxretry
	begin
		select @spid = min(spid) from @spidsInDB
		while @spid is not null
			begin
				set @sqlstr = 'kill ' + cast(@spid as varchar(10))

				exec master.dbo.sp_executesql @sqlstr

				select @spid = min(spid) from @spidsInDB
				where spid > @spid
				and spid <> @@spid
			end

		delete @spidsInDB

		set @retry = @retry + 1		

		insert @spidsInDB
		select spid
		from master.dbo.sysprocesses
		where spid > 50
		and dbid = db_id(@database)
		and spid <> @@spid
	end

-- fail if exhausted retrys
if @retry > @maxretry
	begin
		raiserror ('Unable to get users out of database. Call in the reinforcements!',10,1)
		return 1
	end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--GRANT  EXECUTE  ON [dbo].[ExpungeUsers]  TO [role or user]
GO

/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
save next script to a file named dbo.AddLinkedServer.PRC
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/

use admin
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[AddLinkedServer] 
	@LinkedServerName varchar(50) ,
	@ServerName varchar(50), 
	@DataAccess varchar(3) = 'ON',
	@CollationCompatible varchar(3) = 'ON',
	@RPC varchar(3) = 'ON',
	@RPCOut varchar(3) = 'ON',
	@ConnectionTimeout int = 15,
	@QueryTimeout int = 3600,
	@Catalog sysname = ''
AS
/*******************************************************************************************************
*	admin.dbo.AddLinkedServer 
*	Creator:	     Bill Wunder
*	Date: 		
*
*	Project:	     utility
*	Project Mgr:	
*	Dev Contact:	
*
*	Description:	Add linked server with standard attributes
*	Notes:	        moved to WSOD template 2-19-2003
*
*	Usage:
	     EXECUTE AddLinkedServer 'lnkDEV_ZEUS', 'DEV_ZEUS'
*
*
*	Modifications:   
*         Developer Name     Date     Brief Description
*         ------------------ -------- ------------------------------------------------------------
*	  bw		     4-22-03  move all setting values to parameter list
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------

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

---------------------------------------------
-- set session
---------------------------------------------
SET NOCOUNT ON


---------------------------------------------
-- body of stored procedure
---------------------------------------------
if IS_SRVROLEMEMBER ('sysadmin') <> 1
	begin
		raiserror ('You must be a member of the sysadmin role to execute admin.dbo.addLinkedServer',16,1)
		return -1
	end

if exists (select 1 from master.dbo.sysservers where srvname = @LinkedServerName)
	EXEC master.dbo.sp_dropserver @LinkedServerName

-- don't use a default db
EXEC master.dbo.sp_addlinkedserver
	@server = @LinkedServerName
	, @srvproduct = ''
        , @provider = 'SQLOLEDB'
	, @datasrc = @ServerName
	, @catalog = @catalog

exec master.dbo.sp_serveroption 
	@server = @LinkedServerName
	, @optname = 'data access'
	, @optvalue = @DataAccess

exec master.dbo.sp_serveroption 
	@server = @LinkedServerName
	, @optname = 'collation compatible'
	, @optvalue = @CollationCompatible

exec master.dbo.sp_serveroption 
	@server = @LinkedServerName
	, @optname = 'rpc'
	, @optvalue = @RPC

exec master.dbo.sp_serveroption 
	@server = @LinkedServerName
	, @optname = 'rpc out'
	, @optvalue = @RPCOut

exec master.dbo.sp_serveroption 
	@server = @LinkedServerName
	, @optname = 'connect timeout'
	, @optvalue = @ConnectionTimeout

exec master.dbo.sp_serveroption 
	@server = @LinkedServerName
	, @optname = 'query timeout'
	, @optvalue = @QueryTimeout

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO