May 20, 2011 at 7:17 am
Greetings all;
OK, so I've set up logshipping before. It is really quite a simple concept. In an ideal world I'd be able to utilize all of the features of Log Shipping right out of the box. But, due to security reasons I can't.
So here is the deal. I've gone ahead and configured my primary database (production) for log shipping. For now it is easy to deal with because it is not really loaded with data. Due to security reasons the receiving database is unaccessible from the production box. However I can FTP to it from the receiving database server (pull). OK, so no big deal. I simply write a simple process to ftp the transaction logs to the receiving database. Works just fine. This is where things start to fall apart.
I FTP over the first transaction log to receiving database server. No problem
I load the first transaction log. No problem
I FTP over the next transaction log to receiving database server. No problem
I go to load the second transaction log and kaput! Doesn't load. In essence it states that I'm out of sequence. Driving me nuts! :crazy:
So, what am I missing?
FYI, I've done this before with success. This one is about to make me rip my hair out.
Specs:
Production Box (source)
Intel Xeon E7520 dual 8 core processors
16gb memory
Data drive 500gb
Log drive 500gb
Receiving Box(destination)
Xeon 2.8 gh dual core processor
Memory ??
Disk 100 gb (data & log)
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
May 20, 2011 at 7:44 am
hmm .. tricky. The boxes shouldn't make any difference - that's the joy of log shipping, I've used since sql 6.5 but my own implementation.
I usually check the headers as a first step - you should be able to get the lsns to make sure you're in sequence. An operation on source will mess the restores, can't remember what offhand but some operations can mess the lsn sequence.
Are you doing this in standby or read only ( not that it should make any difference )
I'd check your log restores onto another database on your source machine just to verify they all restore ok.That way you eliminate log shipping and ftp, when it works there then go back to your ftp. I've only used ftp for log shipping once but it worked fine.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 20, 2011 at 7:59 am
Thanks... I'll keep poking at it.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
May 20, 2011 at 9:08 am
You have another log backup job running?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2011 at 9:17 am
Nope... that was my first thought too. Thanks.
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
May 20, 2011 at 9:27 am
GilaMonster (5/20/2011)
You have another log backup job running?
or someone\something is taking a log backup
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 20, 2011 at 10:11 am
Query the backup tables in MSDB (or check the error log). If it says the logs are out of sequence, they are.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2011 at 12:37 pm
OK, well here is something interesting... It appears that I can load ...20110520170000.trn and nothing beyond it. Odd. Any suggestions?
--==================
Processing E:\dbtransfer\CONX_Prod_20110520163000.trn
RESTORE LOG [CONX_Prod] FROM DISK = N'E:\dbtransfer\CONX_Prod_20110520163000.trn' WITH FILE = 1 , NORECOVERY , NOUNLOAD , STATS = 10
Msg 4326, Level 16, State 1, Server SP5DB, Line 1
The log in this backup set terminates at LSN 1526000000683700001, which is too early to apply to the database. A more recent log backup that includes LSN 1526000000791400001 can be restored.
Msg 3013, Level 16, State 1, Server SP5DB, Line 1
RESTORE LOG is terminating abnormally.
--==================
--==================
Processing E:\dbtransfer\CONX_Prod_20110520170000.trn
RESTORE LOG [CONX_Prod] FROM DISK = N'E:\dbtransfer\CONX_Prod_20110520170000.trn' WITH FILE = 1 , NORECOVERY , NOUNLOAD , STATS = 10
100 percent processed.
Processed 0 pages for database 'CONX_Prod', file 'CONX_Prod' on file 1.
Processed 68 pages for database 'CONX_Prod', file 'CONX_Prod_log' on file 1.
RESTORE LOG successfully processed 68 pages in 0.256 seconds (2.054 MB/sec).
--==================
--==================
Processing E:\dbtransfer\CONX_Prod_20110520173000.trn
RESTORE LOG [CONX_Prod] FROM DISK = N'E:\dbtransfer\CONX_Prod_20110520173000.trn' WITH FILE = 1 , NORECOVERY , NOUNLOAD , STATS = 10
Msg 4305, Level 16, State 1, Server SP5DB, Line 1
The log in this backup set begins at LSN 1526000000793200001, which is too recent to apply to the database. An earlier log backup that includes LSN 1526000000791400001 can be restored.
Msg 3013, Level 16, State 1, Server SP5DB, Line 1
RESTORE LOG is terminating abnormally.
--==================
--==================
Processing E:\dbtransfer\CONX_Prod_20110520180001.trn
RESTORE LOG [CONX_Prod] FROM DISK = N'E:\dbtransfer\CONX_Prod_20110520180001.trn' WITH FILE = 1 , NORECOVERY , NOUNLOAD , STATS = 10
Msg 4305, Level 16, State 1, Server SP5DB, Line 1
The log in this backup set begins at LSN 1526000000900100001, which is too recent to apply to the database. An earlier log backup that includes LSN 1526000000791400001 can be restored.
Msg 3013, Level 16, State 1, Server SP5DB, Line 1
RESTORE LOG is terminating abnormally.
--==================
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
May 20, 2011 at 12:47 pm
There is a log backup missing between CONX_Prod_20110520170000.trn and CONX_Prod_20110520173000.trn. Either you have a log backup job/maint plan, someone took an ad-hoc log backup or there's a third party tool backing up the log.
The error is a clear one, there is a log backup missing, nothing else causes that message.
Did you check the MSDB system tables or error log like I suggested?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2011 at 1:14 pm
Gail et el;
To my discovery there IS another backup process taking place. This time by the the host provider. I was unaware of this, but it was just mentioned to me by the Ops Mgr. This causing the logs to be out of sequence. Turns out to be something that was never disclosed to me... go figure. I know I'm not crazy ... :crazy:
Thanks all for your help.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply