Help Restoring TransLog after full backup
-
I am practicing restoring my database to my backup server in the event of a
failure. I cannot seem to get TransLog backup to restore after my full
backup.. I get the error:
>restore log vtioe from bul with FILE=1, NORECOVERY +
>The log in this backup set begins at LSN 1164000000012900001, which is too
late to apply to the database. An earlier log backup that includes LSN
1162000000098000001 can be restored.
>RESTORE LOG is terminating abnormally.
I am Running SQL2K no service packs on WinNT4 SP6a server. I do full
backups at 3:00am and 1:00pm to tape and then hourly translog backups to
disk. I do this using the SQLserver Agent. Data recovery is set to FULL.
I can restore the database fine off the tape.. but when I restore allowing
additional translogs to be added I get the error. As far as can tell I am
using the first translog of the hourly backup set. SO... I cannot figure
out what earlier log it is looking for. Perhaps the way I am backing up the
trans log hourly is the problem. Can someone look over my code. Below is
the code I use.
*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 NOINIT ,
NOUNLOAD , NAME = N'VTIOE backup 1:00pm', NOSKIP , STATS = 10, NOFORMAT
Step 2-> BACKUP LOG [VTIOE] TO DISK =
N'E:\SQLData\Backup_logs\VTI\VTI_LOG_BU2' 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 -
I think this is ok, but I am wary of the appending. I always write a new t-Log backup and overwrite so that I have multiple files to restore, but no issues with finding the correct one in a set.
I'd be guessing, but you might be having issues with the multiple tlogs in one file. If you use the GUI in EM, does it show all the backups?
Steve Jones
-
Hi Steve.. Thanks for helping
.First. A 3:00AM Full backup to tape erases the tape and starts fresh... that is why the 1:00PM has "NOINIT" in the code.
Yes I can see all my different TransLogs backups in EM. The first one has a date of 1:06PM as is run right after the full backup (this log backup uses "INIT" to refresh the backup set the 6 min difference is due to time it take to run step1 -the full backup).... then you see all the "on-the-hour" backups.
I use EM to do the restore. After restoring from device:tape with option 2 (no recovery) I then restore from device:disk (with correct path)and again choose option 2. That is when I get the error saying I need the most recent log... but I am using the most recent log. (backup number 1)
What am I doing wrong?
Glenn
Glenn Holton
Edited by - gholton on 01/30/2002 11:07:57 AM
Glenn Holton
gholton@vtiboston.com -
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.
-
Just guessing -- You're doing NOINIT on the tape device when you do the full database backup. That means the DB backup will be appended to the tape device. Are you sure that you're actually restoring the latest DB backup, and not some earlier one? If that was your error, your log backup would obviously be way ahead of the older DB backup, and you'd get that type of message when trying to apply the T-log.
.Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply