June 24, 2002 at 10:54 pm
(Using SQL Server 2000 Enterprise Edition)
I am testing out recovery with a new database.
I have restored the full database backup and choose to leave the database "non-operational but able to restore additional transaction log backups". I am getting the below error from the first transaction log backup file I am restoring:
"The log in this backup set begins at LSN5000000042500001, which is too late to apply to the database. An earlier log backup that includes LSN5000000042200001 can be restored. RESTORE LOG is terminating abnormally".
--> I am using the first t-log backup file. There is no "earlier" t-log backup.
Any ideas?????
June 25, 2002 at 4:02 am
Sounds like you have a gap in your transaction log. A lot of times it is how you perform your full backup and if you do a shrink database and empty the transation log at that time. If you do then do the truncate transaction log first then the full backup and you should not have an issue.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 25, 2002 at 9:15 am
This is exactly what I did:
(All done by a maintenance plan.)
10:00pm Full Backup
10:15pm Transaction Log Backup
10:18pm Did some inserts
10:30pm Transaction Log backup
Then I restored the full and tried to restore the first Transaction log and got the above error.
Any ideas?
June 25, 2002 at 9:49 am
Double check you restored the right log. Do it in EM and then "view contents"
This is usually what Andy posted, a gap.
Also, do you append or overwrite when you do logs? Could you have overwritten the 10:15 log with the 10:30 log?
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 25, 2002 at 10:30 am
I am restoring the first log backup created after the full backup. There is no overwriting. The maintenance plan creates a different(timestamp) log backup every 15 minutes.
I restore the full, but the first log backup will not restore.
June 25, 2002 at 7:37 pm
Can someone PLEASE try this and let me know their results.
(1) Create a new database in SQL Server 2000 Enterprise edition. Set to Full recovery model.
(2) Create a maintenance plan with a Full backup and T-Log backups every 15 minutes.
Next:
(1) Insert some records in a table with INSERT INTO
(2) Wait for full backup to complete
(3) Insert some more records
(4) Wait for 3 T-log backups to complete.
Then, try and restore the full backup with the option "Non-operational but able to restore additional transaction log backups: Then, attempt to restore the first transaction log backup.
I will give a ONE COLD BEER to the first person who successfully gets the first Transaction Log restored. I suspect you will get the below error:
"The log in this backup set begins at LSN5000000042500001, which is too late to apply to the database. An earlier log backup that includes LSN5000000042200001 can be restored. RESTORE LOG is terminating abnormally".
Any takers?
June 25, 2002 at 7:41 pm
To Clarify,
Step (2)
Create a maintenance plan with a Full backup "once" daily and T-Log backups every 15 minutes.
June 26, 2002 at 7:55 pm
can anyone try my above example and see if this is a bug or not?
June 27, 2002 at 3:39 am
Hi bryan,
I tried this with sql developer 2000 without any problem. I am afraid that the beer is no longer cold when it arives.
best regards,
Klaas-Jan
June 27, 2002 at 4:38 am
This also worked fine for me and restored correctly. My transaction log backups for database 'a' were given filenames containing a date and time stamp e.g. a_tlog_200206271125.TRN this gives you a simple visual check to see the order in which they should be restored. How did you get the filenames in the format LSN5000000042500001? I couldn't see a method to configure the filenames using the maintenance plan wizard.
Regards,
Andy Jones
.
June 27, 2002 at 5:26 am
Andy,
The LSN is not the TLog file name. LSN is the Log Sequence Number which is an internal sequential number within the TLog. When a transaction is written to the TLog, it is given a LSN number as a pointer in the Log. I believe the only customisation you can do to file names is change their extension. It gives you the option to change TRN to whatever you choose.
Clive Strong
September 4, 2002 at 3:24 am
I know this isn't going to help, but it may give some relief that I am experiencing the same problem.
I have a number of databases, all of which are backed up regularly, and the backups are NOT created using the maintenance plan. All backups have been running perfectly until this morning.
I back up the Full backup, Differentiual and Transaction log each to a different file per datasbase (i.e. 3 backup files per db).
My schedule is...
1) Run a full backup Sunday morning at 6am. Immediately AFTER this is completed, run a differenctial and a transaction log - this probably won't backup any data, but serves to clear the files down (INIT).
2) 7am, 1pm and 7pm run a differential backup, appended to the file. A T-Log backup is run on completion, again more to clear down the file (INIT) than backup data.
3) Every 15 minutes, timed between the differentials so that they don't clash, a T-Log backup is taken, again appended to the file.
Each morning, I restore these files onto a backup server for testing.
This morning, all databases restored except one. The full restores, the latest differential restored, and 7 of the 10 transaction logs. The 8th transaction log fails with the same error listed here. I've checked the differentials and no later one has been run, and the times of the transaction logs are 15 minutes apart, but it will not let me restore any log after the 7th.
Wierd that it has just happened now, and hasn't happened to any of the other databases.
I've double checked the jobs, they haven't been altered and have been working perfectly so far.
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
September 5, 2002 at 9:07 am
Check Q308267 in the Microsoft Knowledgebase.
If you've done anything to the database with SQLDMO, this may be causing the problem.
Cheers,
Ken
September 5, 2002 at 9:18 am
After having read the article, I'm inclined to think that you mave have hit the nail on the head, but I'm not too sure.
Between the two T-Logs two things would have happened.
I run red.gate's SQL Compare to give me a comparison report between the production and development database structures and code for the developers.
The other task that is run is Bill Wunder's ArchUtil to source safe both the development and production database structures in case a roll back is required, and also for tracking daily changes.
The article says that applying the latest service pack, which is already installed on both servers.
Any suggestions as to whether this is the case would be helpful.
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
September 5, 2002 at 5:24 pm
This was a common problem in my case. I backup the logs every 15 minutes and restore them on a stand-by server. If I do import or export data in SQL EM that need to drop the current table or if I modify a table (using SQL EM) to insert a new column, there will be a gap in LSN b/w the log backups. I didn't know what was wrong. I posted this question in MS SQL news group. A SQL guru comfirmed this problem. She (or he) setup Profiler and saw the database recovery option was changed to Simple when she did the import data in SQL EM. She had SQL2K SP2, so did I. A MS support guy admitted the problem since he could duplicate the problem on his box. He said it would be corrected in SP3!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply