asked
Help with Backup and Restore in SQL Server 2008
HI All, I did many jobs to do a backup for Full : every week on Friday , Diff:daily at 4 pm , T-log:daily every 2 hour. When they tried to restore the backup they got an error and the database in Restoring. **Msg 9004, Level 16, State 3, Line 1 An error occurred while processing the log for database 'DB'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally.** The backup file have : Full,T-Log at 12PM , T-log at 2PM ![alt text][1] [1]: /storage/temp/573-backup.jpg If I tried to restore the Full and T-log the process is done without any error but If I include the third one (T-log at 2PM) with the tow, it does n’t work. I run the TSQL to verify the backup file **restore verifyonly from disk='D:\Backup12062012.bak'** I got this info **Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details. The path specified by "E:\\MSSQL_Data\\DB.mdf" is not in a valid directory. Directory lookup for the file "F:\\MSSQL_Log\\DB_log.ldf" failed with the operating system error 2(failed to retrieve text for this error. Reason: 15105). The backup set on file 1 is valid.** Any help with this issue ?
I must confess to not having seen those specific error messages, so definitely take someone else's answer if they have personal experience. However, from the face of those two messages I would say one of two things have happened: Either the second message is literally right and you should check that those paths exist (and have ample space for the operation). Or that specific log backup is corrupted. In that case, there may be limited options for restoring back to the state in that specific time frame or one that depends on the unbroken chain that includes that time frame. But you can get around it easily enough if you have a later full or differential backup.
The backup file you're running `RESTORE VERIFYONLY` on - that's being run on a separate server, yes? Some observations from your messages: * That appears to be a full backup you're testing, rather than a T-Log backup * the file `E:\MSSQL_Data\DB.mdf` doesn't exist, or the directory `E:\MSSQL_Data\` doesn't exist, or the E: drive doesn't exist * Ditto for `F:\MSSQL_Log\DB_Log.ldf`, `F:\MSSQL_Log\`, F: * The last line is the most important here. "The backup set on file 1 is valid." However, it's no good having what appears to be a valid backup if you can't actually restore it... Try restoring with the `MOVE` option to move the files to locations that do exist (or make sure that the `E:\MSSQL_Data` & `F:\MSSQL_Log` directories exist. Right. Now, as for the T-Log backup... Are you sure you're restoring the right sequence / order? If you're trying to restore a 2pm T-Log dump, then, by your description, you'll need to do the following: * Restore the most recent FULL backup that pre-dates the 2pm T-Log * Restore the most recent DIFF backup that pre-dates the 2pm T-Log and that post-dates the FULL backup * Restore *all* the T-Log backups that post-date the most recent DIFF (or FULL if no DIFF) backup that you just restored, in the order in which they were made. Now, as to what seems to be happening... * Are you sure that your backup process is the only one that's running? I don't like the jump in LSNs that's shown. * Are you sure that your backup contains backups for the same database? from the same server? (This sort of thing is the reason I like separate files for each backup - easier to see if there's been a problem somewhere...)
I'm very sure I'm restore with the same sequence. But I'm agree with you the LSNs jump in 2pm something wrong here. because as I mentioned If I restore only the Full and T-log at 12 pm this process will work fine. today I did a Diff so the file have: -Full -T-log at 12pm -T-log at 2pm -Diff at 11am Today when I restore it it worked ...so the problem in T-log at 2pm but why this happen ?
12 People are following this question.
