August 17, 2012 at 9:43 am
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?
August 17, 2012 at 10:30 am
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.
August 17, 2012 at 11:15 am
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.
March 26, 2013 at 1:06 pm
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)
March 26, 2013 at 1:28 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply