Solved

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

Posted on 2016-07-14
7
39 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

707 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

12 Experts available now in Live!

Get 1:1 Help Now