February 26, 2011 at 2:04 am
Following a HDD glitch I've discovered that one of my databases wasn't included in my routine backups, so I don't have a recent copy. The version on the affected disk is evidently damaged and I can't attach it normally. I tried renaming the logfile and using sp_attach_single_file_db, but that results in a "log cannot be rebuilt" error (details below).
Is there any way of recovering anything from this database, or is it completely lost?
Suggestions appreciated.
==========
[Attempting ordinary attach]
Attach database failed for Server '[my_server]\SQLEXPRESS'. (Microsoft.SqlServer.Smo)
[...]
The log scan number (1944:59:71) passed to log scan in database 'myDB' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
Could not open new database 'myDB'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 9003)
====
[Attempting sp_attach_single_file_db (ldf renamed)]:
File activation failure. The physical file name "[path_to]\myDB.LDF" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'myDB'. CREATE DATABASE is aborted.
====
February 26, 2011 at 2:42 pm
if SS2K5 then take a look at "alter database for attach_rebuild_log"
if SS2K then take a look at "dbcc rebuild_log"
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 26, 2011 at 4:39 pm
See the process here for hack-attaching the database and repairing it. http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
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
February 26, 2011 at 4:39 pm
PaulB-TheOneAndOnly (2/26/2011)
if SS2K5 then take a look at "alter database for attach_rebuild_log"if SS2K then take a look at "dbcc rebuild_log"
attach_rebuild_log only works if the DB was cleanly shut down. Otherwise it will fail.
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
February 26, 2011 at 5:49 pm
GilaMonster (2/26/2011)
PaulB-TheOneAndOnly (2/26/2011)
if SS2K5 then take a look at "alter database for attach_rebuild_log"if SS2K then take a look at "dbcc rebuild_log"
attach_rebuild_log only works if the DB was cleanly shut down. Otherwise it will fail.
Interesting. I was under the impression it was the SS2K5 implementation of "dbcc rebuild_log"
Thank you for pointing it out Gail.
It is "attach_force_rebuild_log", isn't it? 🙂 http://blogs.msdn.com/b/john_daskalakis/archive/2008/11/20/9128367.aspx
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 27, 2011 at 1:15 am
Thanks for the replies, folks, and apologies for failing to report the SS version (it's SQL Server 2008 Express). I'll pore over the 'hack-attach' article and see what I can do - much appreciated.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply