RECOVER DATABASE WITH CORRUPT LOG FILE

My database was marked 'SUSPECT' as a result of harddisk error and I don't
have any backup since my backup harddisk was broken
I tried using
Sp_resetstatus mydata
DBCC CHECKDB ('mydata', NO INDEX)
GO
It returned:
Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
staturs = 1077936397
(status suspect bit = 256)
For row in sysdatabase for datase ' mydata', the status bit 256 was forced
off and mode was force to 0
warning : you must recover this database prior to access
Server: Msg 926, Level 14, State 1, Line -1074284091
Database 'mydata' cannot be opened. It has been marked SUSPECT by recovery.
See the SQL Server Error log for more information.
When I checked the error log, part of the log was:
Opening file C:\mssql7\data\mydata_Data.MDF
Opening file C:\mssql7\data\mydata_Log.LDF
The log for database 'mydata' is corrupt
Error 9004, severity 23, state:2
Database 'mydata' (database ID 11) could not recover. Contact Technical
Support
Error 3414, Severity 21 state 1
I tried using the following command
ALTER DATABASE mydata
ADD LOG FILE
(NAME = test1log2,
FILENAME= 'C:\mssql7\data\test2log.ldf',
SIZE = 5MB,
MASXIZE = 100MB,
FILEFROWTH = 5MB)
GO
It returned
Database 'mydata' cannot be opened
When I tried detach/attach_single_file database, it returned:
EXEC sp_detach_db @.dbname = 'mydata'
EXEC sp_attach_single_file_db @.dbname = 'mydata',
@.physname = 'c:\mssql7\data\mydata_data.mdf'
Successfully detached database 'mydata'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
server: Msg 9004, Level 23, State 2, Line 1
The log for database 'mydata' is corrupt.
Connection Brokens
Please anyone could help me recover my database
Regards
FHDid you read:
http://www.karaszi.com/sqlserver/info_corrupt_suspect_db.asp
HTH, jens Suessmeyer,|||Hi
Try with sp_add_log_file_recover_suspect_db.
Else you're with problems if don't have recently backup.
Good luck.
FJCG
"FH" <FH@.discussions.microsoft.com> escribió en el mensaje
news:488835F7-3B5D-4278-9A51-121E7EFD2B8C@.microsoft.com...
> My database was marked 'SUSPECT' as a result of harddisk error and I
> don't
> have any backup since my backup harddisk was broken
>
> I tried using
> Sp_resetstatus mydata
> DBCC CHECKDB ('mydata', NO INDEX)
> GO
> It returned:
> Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
> staturs = 1077936397
> (status suspect bit = 256)
> For row in sysdatabase for datase ' mydata', the status bit 256 was forced
> off and mode was force to 0
> warning : you must recover this database prior to access
> Server: Msg 926, Level 14, State 1, Line -1074284091
> Database 'mydata' cannot be opened. It has been marked SUSPECT by
> recovery.
> See the SQL Server Error log for more information.
>
> When I checked the error log, part of the log was:
> Opening file C:\mssql7\data\mydata_Data.MDF
> Opening file C:\mssql7\data\mydata_Log.LDF
> The log for database 'mydata' is corrupt
> Error 9004, severity 23, state:2
> Database 'mydata' (database ID 11) could not recover. Contact Technical
> Support
> Error 3414, Severity 21 state 1
>
> I tried using the following command
> ALTER DATABASE mydata
> ADD LOG FILE
> (NAME = test1log2,
> FILENAME= 'C:\mssql7\data\test2log.ldf',
> SIZE = 5MB,
> MASXIZE = 100MB,
> FILEFROWTH = 5MB)
> GO
> It returned
> Database 'mydata' cannot be opened
>
> When I tried detach/attach_single_file database, it returned:
>
> EXEC sp_detach_db @.dbname = 'mydata'
> EXEC sp_attach_single_file_db @.dbname = 'mydata',
> @.physname = 'c:\mssql7\data\mydata_data.mdf'
> Successfully detached database 'mydata'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> server: Msg 9004, Level 23, State 2, Line 1
> The log for database 'mydata' is corrupt.
> Connection Brokens
> Please anyone could help me recover my database
> Regards
> FH
>|||Not sure if you fixed this yet but I just had a db marked to this statu
and it simply means that the Log file is knackered. Unfortunately
without backups you will lose some uncommitted data, but at least th
written data will be about. Do the following:
1) Stop the SQL server
2) Copy the database.MDF file to anywhere (just a safety check)
3) Run SP_DETACH_DB 'DBNAME', 'TRUE'
4) Now Use explorer and manually delete the LDF file (LOG)
5) Now run sp_attach_single_file_db @.dbname = 'dbname',
@.physname = 'fullpath\dbname.MDF'
It will create a brand new LDF file in the Same folder as the MDF
this can then be moved to your folder structure using SP_RENAMEDB.
Word of advice though is to run a maintenance plan with a backup daily
I guess the problem with the log file is that you never clear it dow
as a result of a backup. It makes me sad to see you treating a databa
in that way! :-)
Good luck matey.
Mik
-
mikeroberts2
----
Posted via http://www.webservertalk.co
----
View this thread: http://www.webservertalk.com/message1201952.htm