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?
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?
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
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,
...
SELECT
--first_lsn,
--last_lsn,
--checkpoint_lsn,
--database_backup_lsn,
...
change to:
SELECT
first_lsn,
last_lsn,
checkpoint_lsn,
database_backup_lsn,
...
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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.
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.
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.