Restore Problem
-
Hi,
I am trying to restore a point in time restore of backup. My bkp plan includes full bkp-weekly, daily diff bkp and every 2 hr TLog bkp. I would like to restore the latest bkp as on ''2007-10-18 ' with minimul data loss. Hence i follow the following seqence of restores;
--Restore Full backup
RESTORE DATABASE [ABC] FROM ABC_BackUp WITH FILE = 1, NORECOVERY
GO
--Restore Diff backup
RESTORE DATABASE ABC FROM ABC_BackUp WITH FILE = 3, NORECOVERY
--Restore TLog backup
RESTORE LOG ABC FROM ABC_BackUp WITH FILE=4, NORECOVERY, STOPAT = '2007-10-18 15:11:00'
GO
The system works well with Full bkp and Diff bkp restore but throws following error while TLog restore;
ERROR:
Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 86001000000103200001, which is too late to apply to the database. An earlier log backup that includes LSN 86001000000081300001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
The following is the backup information of my database:-
Position Bkp Type Bkp Size First LSN Last LSN 1 1 1923229696 86001000000001600002 86001000000005900001 2 2 7284736 85999000000278900001 86001000000081100001 3 5 534528 86001000000081100001 86001000000081300001 4 2 598016 86001000000103200001 86001000000176700001 5 2 729088 86001000000176700001 86001000000276800001 6 2 598016 86001000000276800001 86001000000353100001 7 2 663552 86001000000353100001 86001000000432900001 Pls. guide why i get this error. I don't do any shrinking between Full bkp and TLog bkps. Thanks in advance.
-
If the scripts above are accurate, I don't see you using the NORECOVERY option while restoring the differential backup.
Also, what happens if you restore the full backup (file 1) and the transaction logs (files 2 and 4) (this means not restoring the differential backup)?
-
Ya. I use NORECOVERY with diff bkp. (corrected above). Further, i can restore file 2 but not 4. While restoring file 4 it gives the same error (as above). Further, i would like to add that the backup is of production server and i am testing restore on test server. All the settings of production and test server are same. Pl.guide.
-
It appears that there my have been a truncate of the log file done between the differential backup (file 3) and the next transaction log backup (file 4).
I am guessing that you have an automated process running that completes the Full/Differential/Transaction Log backups. Take a look at that process and see if there is a backup log with truncate_only being run after the differential backup is completed.
Either that, or someone ran a manual transaction log backup aafter the automated differential and before the automated transaction log backup. Check you SQL and NT Event logs. It should be recorded in one of them.
-
Also, you can query msdb.dbo.backupset to see what backups were completed if you don't have access to the logs for the time in question.
-
I even tried recreating the whole bkp and this time again i could not restore file 4 (TLog bkp file). I checked all truncate log is never runned. Why i am not able to restore tlog file??
-
Have you checked the SQL Server Logs for the day that the full backup, differential backup, and transaction log backups you are attempting to restore were taken? If no backup log with truncate_only was run, the only other possibility is that there is a missing transaction log backup.
You can also query msdb.dbo.backupset for the same information. Read BOL for more info on this and related tables.
-
Hi,
Check ur restore database code once again,
you are using different database name in Restore Diff backup. ( i.e TelehopBilling insteadof ABC)
Before restoring the database you confirm the file names by executing Restore filelistonly against the backup device.
-
I have corrected. It was typo.
-
If you are still have a problem with the restore, have you checked the SQL Logs or msdb.dbo.backupset table to see if there is a missing transaction log backup?
-
I checked the log but could not find any info. Also the table backupset doesnot give much help.
-
What information is available in the msdb.dbo.backupset table for the day in question?
-
Further, one thing i need to mention that during that day the TLog backup failed once but on its next run it succeeded. See the error below when it failed;
Executed as user: sa. Cannot open backup device 'ABC_BackUp'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). NOTE: The step was retried the requested number of times (3) without succeeding. The step failed.
Since it succeeded in next run, does this matter? Also, i am not good at looking msdb.dbo.backupset table, so kindly pls. guide what should is see when doing such checks of restore failure? Also looking at the error what could be the reason of above error.
Thanks
-
The simplest query is:
select * from msdb.dbo.backupset where backup_start_date between <earliest datetime> and <latest datetime>
-
I mean to say what we need to look for. It simply gives the sequence of backups created as similar to ;RESTORE HEADERONLY FROM ABC_BackUP? Also what could be the reason for LSN mismatches and why the device failed? Knowing this may lead to know exact cause of restore failure.
Thanks
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply