July 18, 2003 at 1:23 am
(sorry for posting in a new topic: I just could not add a reply to an old one. Was it locked or reached its size limit?)
Hi!
2Allen_Cui
quote:
--------------------------------------------------------------------------------
I am wondering you did not restore the last transaction log backup created at 13:45. Do you still have the complete error message with the LSN number?
--------------------------------------------------------------------------------
I did:
full backup at 12.13
diff backup at 12.30
log backup at 12.45
log backup at 13.00
log backup at 13.15
log backup at 13.30
diff backup at 13.34
log backup at 13.45
So I should restore:
full backup at 12.13
diff backup at 13.34
log backup at 13.45 /*restore failed */
Right? All log backups are appended to the same file.
Here is my restore process:
RESTORE DATABASE test_1c
FROM disk='D:\ONEC\ONEC.BAK'
WITH NORECOVERY
-- restore is OK
RESTORE DATABASE test_1c
FROM disk='D:\ONEC\ONEC_diff.BAK'
WITH NORECOVERY
-- restore is OK
RESTORE LOG test_1c
FROM disk='D:\ONEC\ONEC.TLF'
WITH RECOVERY
-- restore failed:
Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 1112000000499500001, which is too early to apply to the database. A more recent log backup that includes LSN 1113000000026900001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
July 18, 2003 at 6:45 am
quote:
RESTORE LOG test_1cFROM disk='D:\ONEC\ONEC.TLF'
WITH RECOVERY
-- restore failed:
The problem comes from last restore log statement. You have to add "FILE = file_number" option to indicate which backup set from 'D:\ONEC\ONEC.TLF' to be restored. You can use RESTORE HEADERONLY to find out. In your case, it should be 5.
The complete restore statement should be like
RESTORE LOG test_1c FROM disk='D:\ONEC\ONEC.TLF' WITH FILE = 5, RECOVERY
July 18, 2003 at 7:12 am
quote:
Right? All log backups are appended to the same file.
That is the reason why you need add FILE = filenumber option to the restore statement. SQL Server doesn't know which backup sets do you want to restore.
July 22, 2003 at 2:12 am
2Allen_Cui:
"The problem comes from last restore log statement. You have to add "FILE = file_number"... "
- This worked, but raised a few other questions:
since all log backups are appended to the same file, this file is constantly growing. Even after full or differential backups it remains the same. Are there any options for backup command to automatically remove from this file, log backups that are older than last full or diff backup?
Are there any way to automatically determine the number of file to begin restore with and to tell the program to continue the restore till the last log file?
/* the log backup is run every 15 minutes so there are about 100 log backup per day. */
Thanks.
July 22, 2003 at 6:35 am
You COULD create two log backup jobs. The first one (12:45) would use the command WITH INIT. Then a second job that would run all the other log backups and would NOT include WITH INIT.
-SQLBill
July 22, 2003 at 7:54 am
quote:
since all log backups are appended to the same file, this file is constantly growing. Even after full or differential backups it remains the same. Are there any options for backup command to automatically remove from this file, log backups that are older than last full or diff backup?
See SQLBill reply to this question.
quote:
Are there any way to automatically determine the number of file to begin restore with and to tell the program to continue the restore till the last log file?
RESTORE HEADERONLY retrieves all the backup header information for all backup sets. You can create script to use those information to automate the log restoration.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply