-- Stored Procedure: dbo.ApplySLSLogSecondary -- 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].[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: SQLLiteSpeed Log Shipping * Project Mgr: * Dev Contact: * * 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' , @BackupFileName = '\\BILL2K\SLS_TLogs\pubs_200303211357_Log.sls' , @UndoFile = '\\BILL2K\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 /* Let it mark as ship complete if "There are no backup files in the set" been getting 10400 rc in this case, documentations says should be 10408 rc. Can't handle on cycle when backup fails because the error is not handled by LiteSpeed yet causes the job step to end in failure. This causes a bogus row in slslogshippinglog and a file of 33kb to be left behind for the next cycle Backup is getting cancelled during DBCC INDEXDEFRAG due to: "insufficient system resources (x80770003)" */ if @rc in (10400,10408) and charindex(reverse('_Log.sls'),reverse(@BackupFileName)) = 1 set @rc = 0 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 ON GO SET ANSI_NULLS ON GO