Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-07-14
7
Medium Priority
?
96 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 27

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 27

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 27

Accepted Solution

by:
Zberteoc earned 2000 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 27

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

916 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