Information need on Logshipping Backups and Databases

Question

Need help with Script to know

• In the part of logshiping Configuration we need Validation script that mentions that a db is or is not Involved for log shipping.
• we need to know the size of the backups on Primary side and Duration of Restore on Secondary side

Please help on this situation.

Answer 1

1.-- Show Current Status of Log Shipping 

exec sp_executesql @stmt=N'exec sp_help_log_shipping_monitor',@params=N''

2. take a look at msdb.dbo.backupset

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Uri,

The script You provided showing only DB'S which Databases involved in Logshipping, But i need which Databases are not involved in Logshipping and in the same time we would like to know size of backups and Duration of restore on Replicate side? Can you please help

Answer 3

This article gives you the different ways to monitor logshipping and the transaction log shipping status report looks really good.

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 4

On your secondary site, run below statement to query backup/restore info under msdb. see [backup_size]and [Restore_Duration] columns

-- Restore Info

SELECT

       rh.restore_history_id

      ,rh.restore_date

      ,rh.destination_database_name

      ,rh.user_name

      ,rh.backup_set_id

      ,rh.restore_type

      ,rh.replace

      ,rh.recovery

      ,rh.restart

      ,rh.device_count

      ,MF.physical_device_name

      ,BS.name

      ,BS.description

      ,BS.type

      ,BS.backup_start_date

      ,BS.backup_finish_date

      ,datediff(mi,BS.backup_start_date,BS.backup_finish_date)as[Restore_Duration]

      ,BS.backup_size

      ,BS.is_damaged

      ,BS.user_name

      ,BS.recovery_model

      ,BS.compatibility_level

      ,BS.server_name

      ,BS.machine_name

      ,BS.database_creation_date

      ,BS.database_version

      ,BS.flags

      ,BS.is_snapshot

      ,BS.is_readonly

      ,BS.is_single_user

      ,BS.has_backup_checksums

      ,BS.begins_log_chain

      ,BS.has_incomplete_metadata

      ,BS.is_force_offline

      ,BS.is_copy_only

      ,rh.stop_at

      ,rh.stop_at_mark_name

      ,rh.stop_before

FROM

      msdb.dbo.restorehistory rh (NOLOCK)

      INNERJOINmsdb.dbo.backupset BS (NOLOCK)ONrh.backup_set_id=BS.backup_set_id

      INNERJOINmsdb.dbo.backupmediafamily MF(NOLOCK)ONBS.media_set_id =MF.media_set_id

ORDER BY

      rh.restore_date DESC

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 5

Villegas,

Your Script works but i am not getting Restore_Duration 0 for Databases, How can we fix this?

Answer 6

are you getting backup_start_date and backup_finish_date ?

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 7

Yes I am getting those dates. But i am not getting Restore Duration time, Just getting 0 for alll db's, I guess do we need to change to Micro seconds?

Answer 8

Right now is configured to return in Minutes. you can replace MI for SS in below line and you will get the results in Seconds

,datediff(SS,BS.backup_start_date,BS.backup_finish_date)as[Restore_Duration]

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you