SQL database files

Question

SQL database files

tobypaul509

Grasshopper

Points: 23
More actions
August 23, 2016 at 8:38 am

#329580

What is the best tool in terms of efficiency, cost and success rate for repairing and recovering corrupt SQL 2005 database files?
I need to purchase a tool for my Database Admins and I am searching for the best bet in the industry.
August 23, 2016 at 9:55 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

tobypaul509 (8/23/2016)
What is the best tool in terms of efficiency, cost and success rate for repairing and recovering corrupt SQL 2005 database files?
I need to purchase a tool for my Database Admins and I am searching for the best bet in the industry.

ouch.

corrupt dbs for SQl are almost always related to hardware not writing correctly.

the best tools is the free ability to create a backup of a database, and restore it.

if you don't have a backup, and you have a corrupt database, it depends on how corrupted it is.

have you run DBCC CHECKDB('db name') WITH NO_INFOMSGS, ALL_ERRORMSGS?

what were the errors?

if you can query it, you want to start migrating queries into a new database.

i've never, ever heard of a tool that auto-magically fixes a corrupt database;

i've always seen that you save what data you can, and understand there may be data loss.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

I'm right there with Lowell. Severely corrupt databases are fixed by restoring a good backup (which also means validation of consistency is a part of the backup process). It does depend on what gets corrupted. So far, knock wood, most corruption issues I've hit have been with nonclustered indexes. Recreating them fixes the problem.

If you're hitting lots of corruption issues, you may have severe problems that need to be addressed.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

prettsons (8/23/2016)
Microsoft offers DBCC CHECKDB with three options to fix database corruption issues: repair_rebuilt, repair_fast & repair_allow_data_loss. All these are able to fix database issues.

https://msdn.microsoft.com/en-us/library/ms176064.aspx

REPAIR_FAST
Maintains syntax for backward compatibility only. No repair actions are performed.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tobypaul509 Grasshopper Points: 23 More actions · Answer 4

Thanks for the reply! now, life has taught me not to forget to make a backup!

Error Msg 7929, Level 16, State 1, Line 1

Check statement aborted. Database contains deferred transactions.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

What statement did you run to get that?

What state is the database in? (Online, suspect, recovery pending)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GabrielPoulsen SSC Enthusiast Points: 107 More actions · Answer 6

Be really sure that your db files are not compressed first.

Right click on both the db files and the logs for all DBS (especially those that are system db's but basically any you want to use) -> go to properties then advanced and -> un tick the box that says compressed.

Using certain OS cleaners will compress the files on your hard drive including db and log files. This causes them to behave as though they were corrupted.

If you have db's that you haven't backed up that are corrupted then try my suggestions as compressed db's will give you all sorts of errors. I don’t want to sound pedantic but I would make sure I had good backup procedures and verbose logging rather than having a tool to recover corrupted databases. This sort of thing is last resort.

If you really need the program lot of tools available in Market. If you don't have the time and desire to search for, you can try a suitable tool SQL Server Recovery Toolbox which I usually use for database recovery. This is my recommendation at your discretion. http://www.oemailrecovery.com/sql_recovery.html

tobypaul509 Grasshopper Points: 23 More actions · Answer 7

tobypaul509

Grasshopper

Points: 23

August 29, 2016 at 10:11 am

#1898130

Thank you all for the help!