Solved

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

Posted on 2016-07-14
7
43 Views
Last Modified: 2016-07-20
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?
0
Comment
Question by:25112
  • 4
  • 3
7 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41711067
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.
0
 
LVL 5

Author Comment

by:25112
ID: 41711256
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
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41711280
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,
...
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 5

Author Comment

by:25112
ID: 41712808
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?
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 41713515
The relation is not that simple. Here it is a link were explained how it works:

https://www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/

You can modify the code yourself. Right now I don't have time for it but next week. I solved the sequence problem by using the dates in the where clause and I return rows only beginning with the last FULL backup. We don't keep here backups before that. And it works because I use this view in a log shipping process that I built myself and works for some time now with no issue. In that table only the successful backups are saved and the date is also accurate enough to get the right order.

The is_Copy_only = 0 would probably be a good idea to be included but I never tested with those backups.
0
 
LVL 5

Author Comment

by:25112
ID: 41721370
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?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41721405
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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now