Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding an LSN - Blind log shipping


Finding an LSN - Blind log shipping

Author
Message
cphite
cphite
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 650
I've been tasked with trying to set up log shipping in a blind scenario... that is, the source server and the target server cannot communicate with one another directly, or even see one another.

So we have log shipping enabled on the source side, and we're creating logs, and then getting them over to our DR site via some snap-mirroring magic.

My idea is to restore the databases on the target side in STANDBY mode, and then create a job that basically does the following:

1. Look for new .trn files in a folder.
2. Determine what .trn file is next in the chain based on the LSN number recorded in the .trn file.
3. Restore that log.
4. Look for the next one.
5. Repeat until there are no more logs.
6. Run again later to look for new logs, etc.

My problem is this... I can find the current LSN for a database by using dbcc log ('<Database>') but it's coming across as a hex value; for example 00000bde:000003cc:0001

My question is, how do I convert 00000bde:000003cc:0001 into something like 3035000000068900057? Or vice-versa? CAST and CONVERT don't seem to be working - I keep getting overflow errors.

Barring that; is there a better way to be doing log shipping in this scenario?
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
cphite (8/17/2012)
I've been tasked with trying to set up log shipping in a blind scenario... that is, the source server and the target server cannot communicate with one another directly, or even see one another.

So we have log shipping enabled on the source side, and we're creating logs, and then getting them over to our DR site via some snap-mirroring magic.

My idea is to restore the databases on the target side in STANDBY mode, and then create a job that basically does the following:

1. Look for new .trn files in a folder.
2. Determine what .trn file is next in the chain based on the LSN number recorded in the .trn file.
3. Restore that log.
4. Look for the next one.
5. Repeat until there are no more logs.
6. Run again later to look for new logs, etc.

My problem is this... I can find the current LSN for a database by using dbcc log ('<Database>') but it's coming across as a hex value; for example 00000bde:000003cc:0001

My question is, how do I convert 00000bde:000003cc:0001 into something like 3035000000068900057? Or vice-versa? CAST and CONVERT don't seem to be working - I keep getting overflow errors.

Barring that; is there a better way to be doing log shipping in this scenario?


First, if your t-log files are date/time stamped in the filename, wouldn't it make sense to process them in that order?

Second, if you need to look at LSN's, check out this: http://msdn.microsoft.com/en-us/library/ms178536(v=sql.100).aspx.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
cphite
cphite
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 650
Lynn Pettis (8/17/2012)
cphite (8/17/2012)
I've been tasked with trying to set up log shipping in a blind scenario... that is, the source server and the target server cannot communicate with one another directly, or even see one another.

So we have log shipping enabled on the source side, and we're creating logs, and then getting them over to our DR site via some snap-mirroring magic.

My idea is to restore the databases on the target side in STANDBY mode, and then create a job that basically does the following:

1. Look for new .trn files in a folder.
2. Determine what .trn file is next in the chain based on the LSN number recorded in the .trn file.
3. Restore that log.
4. Look for the next one.
5. Repeat until there are no more logs.
6. Run again later to look for new logs, etc.

My problem is this... I can find the current LSN for a database by using dbcc log ('<Database>') but it's coming across as a hex value; for example 00000bde:000003cc:0001

My question is, how do I convert 00000bde:000003cc:0001 into something like 3035000000068900057? Or vice-versa? CAST and CONVERT don't seem to be working - I keep getting overflow errors.

Barring that; is there a better way to be doing log shipping in this scenario?


First, if your t-log files are date/time stamped in the filename, wouldn't it make sense to process them in that order?

Second, if you need to look at LSN's, check out this: http://msdn.microsoft.com/en-us/library/ms178536(v=sql.100).aspx.


I know how to get the LSN's from the files; my question is how to get the LSN from the database that is currently sitting in standby mode, and convert it into a format that I can use. I can't just process all of the files in order because it's possible that a file may have been created prior to the backup that I'm restoring from. I need to know which file is first in the chain since the restore point.

Also, while it's true that the file names are date/time stamped, it just seems like an LSN would be a much more specific and reliable way to go with an automated process.
ekkis
ekkis
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 19
found a function that will do the conversion. from M$'s knowledge base: http://support.microsoft.com/kb/886839


CREATE FUNCTION dbo.fn_convertnumericlsntobinary(
@numericlsn numeric(25,0)
) returns binary(10)
AS
BEGIN
-- Declare components to be one step larger than the intended type
-- to avoid sign overflow problems. For example, convert(smallint, convert(numeric(25,0),65535)) will fail but convert(binary(2),
-- convert(int,convert(numeric(25,0),65535))) will give the
-- intended result of 0xffff.
declare @high4bytelsncomponent bigint,
@mid4bytelsncomponent bigint,
@low2bytelsncomponent int
select @high4bytelsncomponent = convert(bigint, floor(@numericlsn / 1000000000000000))
select @numericlsn = @numericlsn - convert(numeric(25,0), @high4bytelsncomponent) * 1000000000000000
select @mid4bytelsncomponent = convert(bigint,floor(@numericlsn / 100000))
select @numericlsn = @numericlsn - convert(numeric(25,0), @mid4bytelsncomponent) * 100000
select @low2bytelsncomponent = convert(int, @numericlsn)
return convert(binary(4), @high4bytelsncomponent) +
convert(binary(4), @mid4bytelsncomponent) +
convert(binary(2), @low2bytelsncomponent)
END



however... I still can't correlate the LSNs in my backup files to anything I get out of dbcc log or loginfo... I need to know that so I can know whether any particular file can be loaded (because the LastLSN of the last transaction log dump that was loaded is the same as the FirstLSN of the next I need to load)
ekkis
ekkis
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 19
got it. actually, you can correlate the LSNs in the backup files directly with what's in the msdb..restorehistory. The query at the bottom of this article:

http://www.sqlbackuprestore.com/logsequencenumbers.htm

shows what you need:


SELECT TOP 1 b.type, b.first_lsn, b.last_lsn, b.checkpoint_lsn, b.database_backup_lsn
FROM msdb..restorehistory a
INNER JOIN msdb..backupset b ON a.backup_set_id = b.backup_set_id
WHERE a.destination_database_name = 'AdventureWorks'
ORDER BY restore_date DESC


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search