transaction logs saying to late too apply, newer one available
-
HI everyone, Im in the process of trying to restore a database using a full backup, differential and transaction logs.
The full backup was taken at 5am Sunday morning, differential today at 5am, then the transactions logs were done at 6am and 10am.
When doing the restore, I created a new database. Then applied the full and differential backups. They worked fine but I receive the error message below when trying to restore the transaction logs:
oft SQL-DMO (ODBC SQLState: 42000)
---------------------------
The log in this backup set begins at LSN 14872000007449300001, which is too late to apply to the database.
An earlier log backup that includes LSN 14869000000770300001 can be restored.
RESTORE LOG is terminating abnormally.
The database has full as recovery mode and we are not doing any log shipping. The only other thing is maintenance plans on Sunday at 1am.
Also, I was able to do the restore by clicking on the database and on the general tab all the backups (full backup, differential, transaction logs) where already checked off for me. I just changed the name in the restore as database box and it worked fine. My dilema is...I need this to happen on another server where I will not be able to right click this database and hit restore. Why am I receiving the error message about the t-logs when i try to do the restore manual?
Now I can't do anything with the database because it says it's loading because I left it open to restore the transaction logs.
I read the other posts on this subject but we aren't log shipping. WHat else can the problem be?
Any help here would be greatly appreciated. Thanks a bunch
-
Check the order of transaction backup and compare with the full backup
Kindest Regards,Amit Lohia
-
The full backup was done on Wednesday (mistake in earlier post) at 5am, differential today at 5am, and the transaction logs are every 4 hours from 6am until 10pm. This is how the backups are:
Full backup Wednesday 5am
Differential Friday 5am
Logs:
Thursday 2pm
Thursday 6pm
Thursday 10pm
Friday 6am
Friday 10am
Logs overide if older than 24 hours.
I restored the full backup from Wednesday (mistake in my earlier post) at 5am, then the differential today at 5am and then then when I tried to do the transactional log from today at 6am..It bombed.
Is this the wrong order? But when it was done automatically by SQL (using the same sequence it works)..which leads me to beleive it is me. What is different when i do it manually...creating a new database and then applying the backups in sequental order and leaving the database non operational?
-
what is the syntax of the entire restore process you are using, for the full, the diff, and the logs?
-
What do you mean by syntax? Please explain.
-
You are using some scripts like:
RESTORE DATABASE YourDB
FROM DISK = N'F:\MSSQL\BACKUPS\YourDB.BAK'
WITH REPLACE,
MOVE N'YourDB_Data' TO N'H:\data\YourDB_Data.MDF',
MOVE N'YourDB_Log' TO N'G:\Log\YourDB_Log.LDF'
-
4 questions: Was there a differential backup on Thursday 5 am as well? Was it to the same file as the Friday differential? Did you use WITH INIT on the backup? Did you use WITH FILE = on the restore? If I were psychic, i'd say your answers are Yes, Yes, No, No.
Use RESTORE HEADERONLY to explore the contents of the differential and log backups. I'm guessing that you restored the differential from Thursday when you thought you were using Friday's.
-
That's why it's important to make sure on restores you know what you're dealing with.
The restore HEADERONLY comment above is a great tool.
and syntax is what is mentioned above:
how are you restoring the databases?
I typically do so through query analyzer
restore database
FROM DISK = 'c:\backup path\full_file.bak'
WITH NORECOVERY, stats = 10
RESTORE DATABASE
FROM DISK = '...\diff_file.bak'
WITH NORECOVERY, stats = 10
--note log below! and change in recovery
RESTORE LOG <dbname
FROM DISK = '...\tran_file.bak'
WITH RECOVERY
again, these are off the top of my head, syntax may be off.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply