April 19, 2010 at 7:59 am
hi,
When SQL server was down, someone has accidentally deleted the LDF file and now DB is in suspect mode.
sp_resetstatus does not work. How can I open database again?
Regards,
MShenel
April 19, 2010 at 8:26 am
This should help.
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
It's for SQL 2005+, but the steps are pretty much the same for SQL 2000, except for the emergency mode repair. You should be able to find info on the web about emergency mode in SQL 2000. It requires hacking of the system tables, then you'll probably need to run DBCC REBUILD_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
April 19, 2010 at 8:46 am
Thanks Gail,
CREATE DATABASE db_name ON (FILENAME = 'D:\xxxx.MDF')
FOR attach ; worked fine.
Regards,
MShenel
April 19, 2010 at 8:59 am
You're lucky.
Now, take measures to ensure that it can't happen again - tighten file system security, remove unnecessary permissions/logins, and make sure that administrators understand that the log file is not an optional file.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply