February 4, 2002 at 12:50 pm
SQL 2000 (no Sp) running on WinNT4 SP6a
I am having trouble restoring by translogs to my test server. I can restore
fine when I do the backups both (full and translog) manually. But the jobs
that I have setup to run automatically do not allow me to restore as I get
the error-> The log in this backup set begins at LSN [LSNnumber], which is
too late to apply to the database. An earlier log backup that includes LSN
[LSNnumber] can be restored. >RESTORE LOG is terminating abnormally.
Looking at the header of my Full and Translog backup sets shows that there
is definately LSN's missing. 90000 of them I'm guessing. I am obviously
doing some wrong 🙁
Full backup 1238000000039900001 1238000000045300001
--------------------------------------------
First log BU 1238000000046200001 1238000000050000001
2nd Log BU 1238000000050000001 1239000000008000001
3rd Log BU 1239000000008000001 1239000000063800001
4th Log BU 1239000000063800001 1240000000029900001
Below is how I am automatically backing up using EM SQLAgent Jobs. I do NOT
have a problem restoring the full backup (with or without NO RECOVERY) so
the way I am backing up the trans log hourly is the problem. Throughout
the course of my problem... someone mentioned that the way I am
overwriting/appending the log might be the problem. Am I using INIT
correctly? I think so.. that is why am so perplexed. Can someone look over
my code?
*Full Backups to tape & first Translog backup to disk (translog uses "INIT"
to reset the backup set)
Step1-> BACKUP DATABASE [VTIOE] TO TAPE = N'\\.\Tape0' WITH INIT ,
NOUNLOAD , NAME = N'VTIOE backup 3:00pm', NOSKIP , STATS = 10, NOFORMAT
Step 2-> BACKUP LOG [VTIOE] TO DISK =
N'E:\SQLData\Backup_logs\VTI\VTI_LOG_BU' WITH INIT , NOUNLOAD , NAME =
N'Hourly Trans log BU', SKIP , STATS = 10, DESCRIPTION = N'Backs up trans
log every hour to disk', NOFORMAT
*Hourly translog backup to disk (appending to backup set on disk)
Step 1->BACKUP LOG [VTIOE] TO DISK =
N'E:\SQLData\Backup_logs\VTI\VTI_LOG_BU2' WITH NOINIT , NOUNLOAD , NAME =
N'Hourly Trans log BU', SKIP , STATS = 10, DESCRIPTION = N'Backs up trans
log every hour to disk', NOFORMAT
Thanks in advance.... Glenn
Glenn Holton
Glenn Holton
gholton@vtiboston.com
February 6, 2002 at 1:51 pm
One thing I notice is Step 2 of the full backup has a log backup to disk to a file VTI_LOG_BU but then in the hourly you state append but the files is VTI_LOG_BU2, when you restore are you including BU and BU2 or just BU2. If so you will need both.
February 6, 2002 at 5:41 pm
nice catch... but unfortunately this was a typo on my part. I check the code make sure. Boy I wish that was it.
I am starting to lose hope here... Glenn
Glenn Holton
Glenn Holton
gholton@vtiboston.com
February 7, 2002 at 4:46 am
Ok let me know if I got this right, when you say jobs that run automatically you are referring to the backup itself, not a job to restore. If so exactly how are you performing the restore and I have went ahead here and setup a backup of a database I have test data in with jobs that do the same yours do to see if I can get the same results and will let you know.
February 7, 2002 at 9:21 am
How much time occurs between the full backup and the first transaction log backup? Also, unless the backups were one right after the other, could any nonlogged operations such as the following have occurred:
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 7, 2002 at 10:08 am
Oh almost forgot, make sure the database properties on the options under recovery section does not have truncate log on checkpoint marked on. If it does and a checkpoint occurrs it will upset you log backups in this fashion.
February 7, 2002 at 6:37 pm
Hi
I wrote a script a while back to assist you in recovering backups logged in the msdb databases. This may assist you in quickly viewing all backups and the associated restore command (disk only) to determine where you are going wrong.
Check in your scripts you are not moving between simply and full recovery mode for example as I have had issues with this when trying to recover. Any alter database statement I typically force a full to ensure maximum saftey.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply