MICROSOFT SQL SERVER ERROR

John Gilbert Alcazar 0

SQL cannot attach a specific database and it show the logs has been corrupted, how could I resolve this w/out restoring the backup

see the attach photo for your reference

Olaf Helper 44,421 Reputation points

2024-07-17T06:36:23.68+00:00

There is no attachement and when SQL Server says, the database file(s) is may corrupt, then it is ... or it's a database from a newer SQL Server version, which can't be attached to an older one.

Which version are you using and from which version is the database?
LiHongMSFT-4306 27,016 Reputation points

2024-07-17T07:37:22.26+00:00

see the attach photo for your reference

Not found the photo, please double check the post. And also check the error log for more information.
John Gilbert Alcazar 0 Reputation points

2024-07-17T07:39:35.08+00:00

Sorry forgot to add
Sassan Karai 81 Reputation points

2024-07-17T19:59:40.6066667+00:00

You can attach the broken database (Log file is corrupt) by using the .mdf file only -assuming there are no secondary database files (.ndf)

Here is more information https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-attach-single-file-db-transact-sql?view=sql-server-ver16&redirectedfrom=MSDN

The database will most likely not be logically consistent (as transactions might be missing due to the broken log file) on the other hand, you may not need everything in the log file and be happy having the majority of your databack.
Erland Sommarskog 110.8K Reputation points MVP

2024-07-17T20:28:20.21+00:00

That procedure only works if the database was shut down cleanly. That is, there are no transactions to redo or undo. But it seems in John's case that there is.

1 answer

Erland Sommarskog 110.8K Reputation points MVP

2024-07-17T09:18:36.48+00:00

If this can be resolved without restoring a backup? Maybe, but it will take much longer time than restoring a backup. It will require very good knowledge about the internal format of the data and log files, and probably also quite a bit of trial and error.

There are some repair tools on the market, a few ones are listed on this page: https://nira.com/how-to-repair-a-corrupted-sql-database/. I have not tried any of them, and since each corruption is essentially unique, my faith in them is limited.

So, yes, the fastest and cheapest way to get out of this situation is to restore a backup. If you don't have a good backup, you need to consider what the cost of losing the database entirely and weigh that against the cost of trying to repair it (and which may fail in the end).
Please sign in to rate this answer.
John Gilbert Alcazar 0 Reputation points

2024-07-18T01:24:48.3466667+00:00

thank you for the suggestion, Data from SQL has been recovered and already attached. This is Helpful.

Sassan Karai 81 Reputation points

2024-07-18T10:27:52.4266667+00:00

Glad to hear you're back up and running.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

MICROSOFT SQL SERVER ERROR

1 answer

Your answer