SQLTeam.com | Weblogs | Forums

Order of restore: why doesn;t it like it?


#1

Trying to apply a .Bak, a .Dif, then .trn for a restore a prod Database to a TEST server & database
The .BAK goes fine
The .dif is giving me fits, with the attached message.
This is the 1st time I've tried a SQL restore with queries.
I issued these commands:
RESTORE DATABASE P21TEST
FROM DISK = '\sql1\Backup\P21\P21_backup_2016_02_04_222133_6470682.bak'
WITH Replace,
MOVE 'seed16_data' TO 'G:\SQL\DATA\P21TEST.mdf',
MOVE 'seed16_log' TO 'G:\SQL\DATA\P21TEST_Log.ldf',
NORECOVERY

Then, I tried:

RESTORE LOG P21TEST
FROM DISK = '\\sql1\Backup\P21\P21_backup_2016_02_05_120004_8203734.dif'
WITH NORECOVERY

I keep getting:
Msg 4305, Level 16, State 1, Line 16
The log in this backup set begins at LSN 1044747000000001600137, which is too recent to apply to the database. An earlier log backup that includes LSN 1044211000000028500001 can be restored.
Msg 3013, Level 16, State 1, Line 16
RESTORE LOG is terminating abnormally.


#2

For the differential, you specify RESTORE DATABASE, not RESTORE LOG.

Plus you'll need the entire log chain after the diff. So looks like 5 files possibly.


#3

excellent.
thanks much!
great forum.
Rich


#4

Curious, when I tried to apply the transaction logs, after the .dif, it balked & said that I need an eariler
logfile..and it referenced the sequence #...that was out
Do you know what causes the sequencing to get thrown out of whack?
It wouldn't accept ANY of the .trn logs..

I finished the restore, and am about 178 records short for the day..

hmmm


#5

Do you have log backups being taken by a different job or anything else? Did you try the one at noon?


#6

Well, I thought the same thing & checked jobs etc Not seeing anything.
Have to regroup next week..thanks!!
R


#7

hmm, so I just re-ran my restore & using the attached fileset,
upon doing the tail, it balked & said there are more recent files...blah blah
Bumed, because I was hoping I'd get it this time, with a simpler set of files
;(
Rich


#8

I can re-run the whole thing again, if we need to to try & see what's wrong..
if you had any further ideas
R


#9

So, just re-ran :slightly_smiling:restored .bak, then 4 .trn filers
then, the tail log trn.. and it tells me:
Msg 4326, Level 16, State 1, Line 34
The log in this backup set terminates at LSN 1044609000000564100001, which is too early to apply to the database. A more recent log backup that includes LSN 1045862000000001600001 can be restored.

I created the tail log after the last of the four .trn 's were restored...
So, I figured the tail would grab the last transactions since the last .trn

I'm doing something wrong...
;(
Rich


#10

I've never applied a tail log to another server. Try a regular log backup as your final backup.


#11

yeah, if I do a final WITH RECOVERY, ignoring the tail log backup, I'm missing a number of records, compared with the production database.
Out of curiosity, how does SQL know where a .trn stops, and a TAIL LOG backup starts?


#12

Ok, I think I might be shooting myself in the foot...maybe
I think by running these 'TAIL LOG' backups, while testing, I'm creating a break in the chain
of transaction files.
I just tried to do a gui-based restore of the bak & 5 transaction logs:
it was fine up till the 3rd trn
It balked about :"The log in this backup set begins at LSN 1045968000000214000001, which is too recent to apply to the database"
I'm now thinking that by taking a TAIL LOG backup(and then DELETING it/Re-creating), I'm creating breaks in my log chain...?
Am I on the right track?
oy..
Rich


#13

It knows where the logs stop/start by the LSN numbers.

Oh yes if you are deleting a tail backup, then you don't have the entire chain. You have to restore ALL files in the chain. If you have a break, you have to instead use a full or differential backup after that break.


#14

Ok, so can I check if the following is a correct understanding:

  1. We have nightly full backups @ 2200
  2. We have transaction log backups running every hour starting @ 0600 till 2000
  3. If I do a restore @ 0900 (.BAK, .TRN, then run a tail log backup), do I now have an "extra" .trn as part of the transaction log backup chain?

Many thanks for sticking with me here!
Rich


#15

Yes the tail log backup is part of the chain. But just run a regular transaction log backup, not a tail backup. Tail backups are for when the data files aren't accessible, and you need to backup the log to not lose that data. You are not in this situation, so a regular log backup should be used. Be sure not to delete that file if the source database is production as it's needed for the log chain. If you've deleted files and don't have copies anywhere, you'll want to backup the database or run a differential to start that chain again, knowing that your recovery points are limited since that file is gone and all the log backups after it and until the full/diff are run are useless.


#16

ahh ok. that all makes sense.
I shall proceed accordingly.
You're an angel..or at least, play one well
:slightly_smiling:Rich