April 6, 2010 at 9:53 am
Hello - hope an expert out there can get me on track. We have a similar setup of jobs for all of our databases. Thought these were all correct. We have restored to development and test environments using the full and differential backups successfully many times.
Yesterday I needed to restore the full and differential and wanted to apply several hours of the log backups but could not get the logs to apply. It kept asking for a very old log that we no longer have. My fear is that I needed to restart the log backups with an init statement after the full backup and then again daily after the differential backups. Months ago I started the series of regular backups with the three different jobs --- starting the log backups with an init statement the first time after the first full backup. Since then the full runs on Saturday night, differential nightly and logs from its own job every 15 minutes.
Any good advice?
Thank you,
Ellen
Partial job statements below
I set up a weekly job that does an integrity check then takes a full backup to a file --
.....
BACKUP DATABASE [MAINDB] TO DISK = @filename01, DISK = @filename02, DISK = @filename03, DISK = @filename04
with NAME = 'MAINDB Full Backup', MEDIANAME = @medianame, MEDIADESCRIPTION = 'Weekly Full Backup'
then nightly have a job that takes an integrity check then takes a differential backup
....
SET @filename01 = 'F:\Production\MAINDB\WK' + @weekofyear + '-' + @dayofweek + '_MAINDB_DIFF.BAK'
checkpoint
BACKUP DATABASE [MAINDB] TO DISK = @filename01
with DIFFERENTIAL , NAME = 'MAINDB Differential Backup', MEDIANAME = @medianame, MEDIADESCRIPTION = 'Daily MAINDB Differential Backup'
Throughout the day all week there is a SQL Agent job that runs every 15 minutes to backup the log
....
set @medianame = 'Week ' + @weekofyear + ' TransLog'
SET @logdaytime = @weekofyear + @dayofweek + '_' + @hourofday + @minofhour
SET @filename = 'F:\Production\MAINDB\WK' + @logdaytime + '_JGUTT_log.trn'
BACKUP LOG [MNAINDB] TO DISK = @filename with NAME = 'MAINDB Trans Log', MEDIANAME = @medianame, MEDIADESCRIPTION = 'Tlog backup every 15 mins work days', RETAINDAYS = 14
April 6, 2010 at 10:00 am
Ellen-477471 (4/6/2010)
Yesterday I needed to restore the full and differential and wanted to apply several hours of the log backups but could not get the logs to apply. It kept asking for a very old log that we no longer have. My fear is that I needed to restart the log backups with an init statement after the full backup and then again daily after the differential backups. Months ago I started the series of regular backups with the three different jobs --- starting the log backups with an init statement the first time after the first full backup. Since then the full runs on Saturday night, differential nightly and logs from its own job every 15 minutes.Any good advice?
WIth INIT clause allows you to overwrite the contents of the file and nothing more. In ur case, i can see that log backups are having unique file names to this wont be of any help.
After restoring ur full and differential backup, u need to apply the log backup taken immediately after the differential backup. In ur case may be u hv lost a log backup file causing mismatch in LSN...
April 6, 2010 at 10:34 am
Thank you for your reply. So you think that the backup jobs look to be setup & scheduled correctly but that for this restore I am just missing some logs?
I thought I had all of them starting with the one after the differential -- I will reexamine the available logs it is possible we missed restoring 4 of them from tape. The number of the log that was requested made it appear to be way far apart from what we have.
Thank you.
April 6, 2010 at 10:51 am
I think there is some missing log backup file.
use this statement....
restore filelistonly from <your backup file>
Run this statement for ur differential backup and ur oldest log backup(just after diff backup)... and check the LSN.. The LSN returned from the log backup should be less than or equal to the one returned by diff backup file.
April 6, 2010 at 11:32 am
Found the files and the restore is complete.
We forgot to restore the log file from midnight -- '0000'
Now at least I know the backups are working correctly!
Thank you.
April 6, 2010 at 11:35 am
Ellen-477471 (4/6/2010)
Found the files and the restore is complete.We forgot to restore the log file from midnight -- '0000'
Now at least I know the backups are working correctly!
Thank you.
Good to hear that. Congo. 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply