Link to home
Start Free TrialLog in
Avatar of 25112
25112

asked on

sql server-t-sql to generate all Ttransaction Logs under latest full backup, from MSDN.dbo.backupset

in
https://www.experts-exchange.com/questions/28951464/SQL-Server-script-to-report-the-SQL-database-backup-method-status-and-history.html
there is  a query, but it does not check LSNs..

Can you modify it to check only the relevant Transaction log backup List/Sequence,
for the latest full backup for a specific database?
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Can you give an example? If you refer to the view then you have to order by database and start date:

 select * from dbo.vwDatabaseBackupInfo order by database_name, backup_start_date

Open in new window

Everything after the last particular database FULL (including) will be your relevant backup files and in that order for a restore.
Avatar of 25112
25112

ASKER

thank you Zberteoc-
that report was very good-

but would be greatly help to also track by LSN.. to determine the validity of the chain...
let me give an example...

let's say the first_lsn of the latest FULL backup is 722684000015161100149
then the last_lsn of the FULL points to the first TLB and the first TLB's firstLSN is the FULL's LASTLSN
then the second TLB's firstLSN is the FirstTLBs LASTLSN and so forth..

and if that LSN chain is broken, then it can be determined that the transaction logs are not useful-

thank u again
In the view code you can uncomment the columns in that come right after SELECT in each query from the UNION:

SELECT
--first_lsn,
--last_lsn,
--checkpoint_lsn,
--database_backup_lsn,
...

change to:

SELECT
  first_lsn,
  last_lsn,
  checkpoint_lsn,
  database_backup_lsn,
...
Avatar of 25112

ASKER

Zberteoc, perhaps not explaining myself clearly to you..

thank you- that report is amazing/helpful- have the over all information on the backups- well compiled and report format....
but when i try to chain the LSNs, hitting a roadblock.. could you incorporate the LSN chain in the report.. how the LSN points from FULL DB to 1st TLB to 2nd TLB etc.
 
 see below query..
 
  select * from
 msdb.dbo.backupset a --full
 join
 msdb.dbo.backupset b
/*TransactionLog#1 (there could be more Transaction Logs available in the chain which this query is not able to fine*/
 on
 a.last_lsn = b.first_lsn
 where
 a.type = 'd' and
 a.is_Copy_only = 0 and
 b.type = 'l' and
 b.is_Copy_only = 0  
 order by a.backup_start_date desc
 
 i should see this in your report, but i don't , ( LSN is not checked in the query?)
 
 is it possible to make the chain visible through code (LSN) to prove/establish there is a valid backup chain..
 
 for example, for the FULL backup that your report generated, if the above query does not bring back a record, then that chain is invalid and no use?
ASKER CERTIFIED SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 25112

ASKER

select COUNT(*) from
  msdb.dbo.backupset a --full
  join
  msdb.dbo.backupset b
 /*TransactionLog#1 (there could be more Transaction Logs available in the chain which this query is not able to fine*/
  on
  a.last_lsn = b.first_lsn
  where
  a.type = 'd' and
  a.is_Copy_only = 0 and
  b.type = 'l' and
  b.is_Copy_only = 0  
 
  gives me 518 records..
 
    select COUNT(*) from   msdb.dbo.backupset gives me 6 digit number.. is that concern, you think?
I would use the relations as explained in that article. I am simply not sure that the one you use

a.last_lsn = b.first_lsn

is enough and will actually give you the correct result. I am not very familiar myself with the LSN sequences but now I understand them better after the article. I am not ready to implement them yet but I am pretty sure that the date relation that I use is good as I use it for some time now with no issues. I agree that the LAS is the way to go and also the copy_only issue you raised it is valid, though.