Log Shipping – Status Tracking

Background

Reviewing Log Shipping on one of our setups and wanted a generic script that will allow quick review of where things are.

Code

Here is where things are:

Primary

SQL


use [master]
go

if schema_id('logShipping') is null
begin

    exec('create schema [logShipping] authorization [dbo]')

end
go

if object_id('[logShipping].[if_StatusPrimary]') is null
begin

    exec('CREATE FUNCTION [logShipping].[if_StatusPrimary] 
            ()
            RETURNS TABLE   
            AS
            RETURN 
            (
                -- Add the SELECT statement with parameter references here
                SELECT [shell] = 0
            )
        ')
end
go


ALTER FUNCTION [logShipping].[if_StatusPrimary] 
()
RETURNS TABLE   
AS
RETURN 
(

    select 
             [sqlInstancePrimary]
              = tblLSMP.[primary_server]
    
            , [databasePrimary]
                = tblLSMP.[primary_database]

            /*
                Process - Backup - Begin
            */
            , [backupDate]
                = tblLSMP.[last_backup_date]

            , [backupDateUTC]
                = tblLSMP.[last_backup_date_utc]

            , [timeSinceLastbackupInMinutes]
                =
                    datediff
                    (
                          minute
                        , tblLSMP.[last_backup_date]
                        , getdate()
                    )


            , [lastbackupFile]
                = tblLSMP.[last_backup_file]

            , [lastbackupFileTS]
                = msdb.[dbo].[agent_datetime]
                    (
                        substring
                        (

                            substring
                            (
                              tblLSMP.[last_backup_file]
                            , charindex( '_2', tblLSMP.[last_backup_file]) + 1
                            , charindex( '.', tblLSMP.[last_backup_file])
                               - charindex( '_2', tblLSMP.[last_backup_file])
                               - 1
                            )
                            , 1
                            , 8
                        )

                        , substring
                        (

                            substring
                            (
                              tblLSMP.[last_backup_file]
                            , charindex( '_2', tblLSMP.[last_backup_file]) + 1
                            , charindex( '.', tblLSMP.[last_backup_file])
                               - charindex( '_2', tblLSMP.[last_backup_file])
                               - 1
                            )
                            , 9
                            , 6
                        )

                    )



            , [lastbackupFileTSLocale]
                = dateadd
                    (
                          minute
                        , datediff
                            (
                                  minute
                                , tblLSMP.[last_backup_date]
                                , tblLSMP.[last_backup_date_utc]
                            ) * -1
                        , msdb.[dbo].[agent_datetime]
                        (
                            substring
                            (

                                substring
                                (
                                  tblLSMP.[last_backup_file]
                                , charindex( '_2', tblLSMP.[last_backup_file]) + 1
                                , charindex( '.', tblLSMP.[last_backup_file])
                                   - charindex( '_2', tblLSMP.[last_backup_file])
                                   - 1
                                )
                                , 1
                                , 8
                            )

                            , substring
                            (

                                substring
                                (
                                  tblLSMP.[last_backup_file]
                                , charindex( '_2', tblLSMP.[last_backup_file]) + 1
                                , charindex( '.', tblLSMP.[last_backup_file])
                                   - charindex( '_2', tblLSMP.[last_backup_file])
                                   - 1
                                )
                                , 9
                                , 6
                            )

                        )
        
                )

            /*
                Process - Backup - End
            */



    from   [msdb]..[log_shipping_monitor_primary] tblLSMP

)
go

Invoke


select *
from   [master].[logShipping].[if_StatusPrimary]()

Output

Secondary

SQL


use [master]
go

if schema_id('logShipping') is null
begin

    exec('create schema [logShipping] authorization [dbo]')

end
go

if object_id('[logShipping].[IF_LogShippingMetric]') is not null
begin

    drop function [logShipping].[IF_LogShippingMetric]

end
go


if object_id('[logShipping].[if_StatusSecondary]') is null
begin

    exec('CREATE FUNCTION [logShipping].[if_StatusSecondary] 
            ()
            RETURNS TABLE   
            AS
            RETURN 
            (
                -- Add the SELECT statement with parameter references here
                SELECT [shell] = 0
            )
        ')
end
go


ALTER FUNCTION [logShipping].[if_StatusSecondary] 
()
RETURNS TABLE   
AS
RETURN 
(

    select 
             [sqlInstancePrimary]
              = tblLSMS.[primary_server]
        
            , [sqlInstanceSecondary]
                = tblLSMS.[secondary_server]
        
            , [databasePrimary]
                = tblLSMS.[primary_database]

            /*
                Process - Copy - Begin
            */
            , [copyDate]
                = tblLSMS.[last_copied_date]

            , [copyDateUTC]
                = tblLSMS.[last_copied_date_utc]

            , [timeSinceLastCopiedInMinutes]
                =
                    datediff
                    (
                          minute
                        , tblLSMS.last_copied_date
                        , getdate()
                    )


            , [lastCopiedFile]
                = tblLSMS.[last_copied_file]

            , [lastCopiedFileTS]
                = msdb.[dbo].[agent_datetime]
                    (
                        substring
                        (

                            substring
                            (
                              tblLSMS.[last_copied_file]
                            , charindex( '_2', tblLSMS.[last_copied_file]) + 1
                            , charindex( '.', tblLSMS.[last_copied_file])
                               - charindex( '_2', tblLSMS.[last_copied_file])
                               - 1
                            )
                            , 1
                            , 8
                        )

                        , substring
                        (

                            substring
                            (
                              tblLSMS.[last_copied_file]
                            , charindex( '_2', tblLSMS.[last_copied_file]) + 1
                            , charindex( '.', tblLSMS.[last_copied_file])
                               - charindex( '_2', tblLSMS.[last_copied_file])
                               - 1
                            )
                            , 9
                            , 6
                        )

                    )

           , [lastCopiedFileTSLocale]
                = dateadd
                    (
                          minute
                        , datediff
                            (
                                  minute
                                , getdate()
                                , getutcdate()
                            ) * -1
                        , msdb.[dbo].[agent_datetime]
                        (
                            substring
                            (

                                substring
                                (
                                  tblLSMS.[last_copied_file]
                                , charindex( '_2', tblLSMS.[last_copied_file]) + 1
                                , charindex( '.', tblLSMS.[last_copied_file])
                                   - charindex( '_2', tblLSMS.[last_copied_file])
                                   - 1
                                )
                                , 1
                                , 8
                            )

                            , substring
                            (

                                substring
                                (
                                  tblLSMS.[last_copied_file]
                                , charindex( '_2', tblLSMS.[last_copied_file]) + 1
                                , charindex( '.', tblLSMS.[last_copied_file])
                                   - charindex( '_2', tblLSMS.[last_copied_file])
                                   - 1
                                )
                                , 9
                                , 6
                            )

                        )
        
                )

            /*
                Process - Copy - End
            */


            /*
                Process - Restoration - Begin
            */

            , [restoreDate]
                = tblLSMS.last_restored_date 

            , [timeSinceLastRestoreInMinutes]
                =
                    datediff
                    (
                          minute
                        , tblLSMS.last_restored_date
                        , getdate()
                    )

            , [lastRestoredFile]
                = tblLSMS.[last_restored_file]

            , [lastRestoredFileTS]
                = msdb.[dbo].[agent_datetime]
                    (
                        substring
                        (

                            substring
                            (
                              tblLSMS.[last_restored_file]
                            , charindex( '_2', tblLSMS.[last_restored_file]) + 1
                            , charindex( '.', tblLSMS.[last_restored_file])
                               - charindex( '_2', tblLSMS.[last_restored_file])
                               - 1
                            )
                            , 1
                            , 8
                        )

                        , substring
                        (

                            substring
                            (
                              tblLSMS.[last_restored_file]
                            , charindex( '_2', tblLSMS.[last_restored_file]) + 1
                            , charindex( '.', tblLSMS.[last_restored_file])
                               - charindex( '_2', tblLSMS.[last_restored_file])
                               - 1
                            )
                            , 9
                            , 6
                        )

                    )

            , [lastRestoredFileTSLocale]
                = dateadd
                    (
                          minute
                        , datediff
                            (
                                  minute
                                , getdate()
                                , getutcdate()
                            ) * -1
                        , msdb.[dbo].[agent_datetime]
                        (
                            substring
                            (

                                substring
                                (
                                  tblLSMS.[last_restored_file]
                                , charindex( '_2', tblLSMS.[last_restored_file]) + 1
                                , charindex( '.', tblLSMS.[last_restored_file])
                                   - charindex( '_2', tblLSMS.[last_restored_file])
                                   - 1
                                )
                                , 1
                                , 8
                            )

                            , substring
                            (

                                substring
                                (
                                  tblLSMS.[last_restored_file]
                                , charindex( '_2', tblLSMS.[last_restored_file]) + 1
                                , charindex( '.', tblLSMS.[last_restored_file])
                                   - charindex( '_2', tblLSMS.[last_restored_file])
                                   - 1
                                )
                                , 9
                                , 6
                            )

                        )
        
                )

            /*
                Process - Restoration - End
            */

    from   [msdb]..log_shipping_monitor_secondary tblLSMS

)
go

Invoke


select *
from   [master].[logShipping].[if_StatusSecondary]()

Output

 

Version Control

GitHub

  1. DanielAdeniji/SQLServerLogShippingStatus
    Link

 

References

  1. Docs / SQL / Relational databases / System tables
    • log_shipping_monitor_primary
      • log_shipping_monitor_primary (Transact-SQL)
        Link
    • log_shipping_monitor_secondary (Transact-SQL)
      • log_shipping_monitor_secondary (Transact-SQL)
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s