April 7, 2008 at 8:08 pm
Hello
I am trying to generate SQL statements to apply the backlogged Transaction logs. To do this , i am looking at the following System tables
log_shipping_monitor_alert (Transact-SQL) => Stores alert job IDs for log shipping.
log_shipping_monitor_error_detail (Transact-SQL) => Stores error detail for log shipping jobs.
log_shipping_monitor_history_detail (Transact-SQL) => Stores history details for log shipping jobs.
log_shipping_monitor_primary (Transact-SQL) => Stores one monitor record per primary database in each log shipping configuration.
log_shipping_monitor_secondary (Transact-SQL) => Stores one monitor record per secondary database in a log shipping configuration.
log_shipping_primary_databases (Transact-SQL) => Stores one record for the primary database in a log shipping configuration.
log_shipping_primary_secondaries (Transact-SQL) => Maps each primary database to its secondary databases. This
log_shipping_secondary (Transact-SQL) => Stores one record per secondary ID.
log_shipping_secondary_databases (Transact-SQL) => Stores one record per secondary database in a log shipping configuration.
In the light of the above i have two questions:
Q1. The log_shipping_monitor_primary has the last_backup_file column and does not give the names of the earlier backed up files. log_shipping_monitor_history_detail has the timestamp but not the physical file name. Is there any other view which give both?
Q2. Also, how do i find which log has been applied or not applied?
Would appreciate any useful feedback on this.
Thanks
April 8, 2008 at 12:39 am
gk (4/7/2008)
HelloI am trying to generate SQL statements to apply the backlogged Transaction logs. To do this , i am looking at the following System tables
log_shipping_monitor_alert (Transact-SQL) => Stores alert job IDs for log shipping.
log_shipping_monitor_error_detail (Transact-SQL) => Stores error detail for log shipping jobs.
log_shipping_monitor_history_detail (Transact-SQL) => Stores history details for log shipping jobs.
log_shipping_monitor_primary (Transact-SQL) => Stores one monitor record per primary database in each log shipping configuration.
log_shipping_monitor_secondary (Transact-SQL) => Stores one monitor record per secondary database in a log shipping configuration.
log_shipping_primary_databases (Transact-SQL) => Stores one record for the primary database in a log shipping configuration.
log_shipping_primary_secondaries (Transact-SQL) => Maps each primary database to its secondary databases. This
log_shipping_secondary (Transact-SQL) => Stores one record per secondary ID.
log_shipping_secondary_databases (Transact-SQL) => Stores one record per secondary database in a log shipping configuration.
In the light of the above i have two questions:
Q1. The log_shipping_monitor_primary has the last_backup_file column and does not give the names of the earlier backed up files. log_shipping_monitor_history_detail has the timestamp but not the physical file name. Is there any other view which give both?
Q2. Also, how do i find which log has been applied or not applied?
Would appreciate any useful feedback on this.
Thanks
I managed to scrap something .. but this still does not give the exact file name
With NumberRows As
( Select a.database_name,a.backup_start_date
,getdate(), DateDiff(second,getdate(), a.backup_start_date) as TotalSeconds
,Row_Number() Over (Order By a.backup_start_date DESC) as RowNumber
,a.last_lsn , a.database_backup_lsn, a.checkpoint_lsn
,a.backup_size,a.backup_set_id , b.physical_name ,b.logical_name,a.server_name
From backupset a , backupfile b
Where a.database_name = 'ARSystem' and a.type = 'L'
and b.backup_set_id = a.backup_set_id
and b.file_type = 'L'
)
Select * From NumberRows
Where RowNumber Between 1 And 9
Order By backup_start_date DESC
The physical file name is the LDF/log file but does not seem to be showing up the TRN log file.
I am continuing my search. Any ideas?
TIA
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply