March 29, 2012 at 3:28 am
Hi all,
A restore job has errored out as follows....
The backup data in '\\XXXXXXXXX\\XXXXXtlog_201203270745.TRN' is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable.
The Shipping Monitor confirms a copy step failed 2 days ago with the following message;
10.15am - "Failure / 121 The semaphore timeout period has expired."
The subsequent load(restore) step failed with the following message:
10.30am [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL Server Driver][SQL Server]The file on device '\\XXXXXXXXXXXXXX\XXXXXXXX_tlog_201203271015.TRN' is not a valid Microsoft Tape Format backup set.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
[Microsoft][ODBC SQL Server Driver][SQL Server]Deleting database file '\\XXXXXXXXXXXXXXX201203271000.tuf'.
Subsequent copy jobs since have succeeded, but the load steps contain the following:
10.45am [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4323: [Microsoft][ODBC SQL Server Driver][SQL Server]The database is marked suspect. Transaction logs cannot be restored. Use RESTORE DATABASE to recover the database.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
It would seem the official option as above is to restore the DB. However, given the initial message, would it be worth simply reapplying the initial failed log to try to clear the error, before doing a full restore?
March 29, 2012 at 3:40 am
Drop the database and restore a full backup. The database has been marked suspect and no logs restores will work
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2012 at 4:17 am
Thank you, doing so now.
March 29, 2012 at 4:51 am
Oh, and do some root cause analysis on why the DB went suspect. That's not a normal or expected occurrence, likely means some IO problems somewhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2012 at 9:01 am
Cheers Gail, it appears this is a known issue with the LAN and may recur until this is addressed.
March 29, 2012 at 9:04 am
Huh?
Suspect databases aren't a LAN (local area network) issue.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply