?
Solved

Microsoft SQL 7 42000 consistency error

Posted on 2011-10-04
8
Medium Priority
?
465 Views
Last Modified: 2012-05-12
I have read other responses that say make sure no one is in the database and make sure you have a good backup but my case is different. This server runs a legacy application and it caught a virus but the virus never got to the data files. A backup was made and tested on another server and it worked fine. The original server was rebuilt and an attempt was made to restore the backup and the error 42000 consistency appeared. The same backup was put on another server and it worked however on a fourth server the same consistency error appeared. Each server tested was a new sql server install. A new backup from one of the test servers that worked was made and put on the server that is needed and the error still occurs,. I have even uninstalled and installed sql again and the error still occurs...

Any suggestions because the original server has been rebuilt and  this is my only backup...

Thanks
rf
0
Comment
Question by:ivron001
  • 5
  • 3
8 Comments
 
LVL 19

Expert Comment

by:elimesika
ID: 36913429
hi

run DBCC CHECKDB
see what the problem is, if it is in index,PK or FK simply drop and re-create it
0
 

Author Comment

by:ivron001
ID: 36914040
dbcc checkdb reported the following...

CHECKDB found 0 allocation errors and 0 consistency errors in database

What is strange about this is that all sql installations came from the same install disk yet two work and two does not work. The same backup file was used by all installs. I am pulling my hair out over this one. Any chance you have another suggestion.

rf
0
 
LVL 19

Expert Comment

by:elimesika
ID: 36916705
Is there any change in machine OS Service pack ?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:ivron001
ID: 36921117
That was a good suggestion but it did not work. To be safe I uninstalled SQL then loaded the updates including the service pack to match the systems that work. I then loaded SQL again and tried to install and got the same error message. I also tried backing up the database again and restoring that backup to the new server and still get the same message. I have been doing this for years and this is the first time I have gotten this error.
0
 
LVL 19

Expert Comment

by:elimesika
ID: 36921405
Seems like the virus may spread to other computers
Do you mind downloading the latest version of Sbybot and configure it to run once on reboot.
Try again when the machine is up after Spybot completes

http://www.safer-networking.org/en/download/index.html
0
 

Author Comment

by:ivron001
ID: 36921605
I used Enterprise to gain access to both servers at once. I tried to restore to the SQL server that did not work using Enterprise on the server that does work and got the same message. I then used the same Enterprise and the same backup on the server that did work and it restored properly. I got tired of screwing with it and used the export to move all tables to the problem SQL server and it worked. I am now going to try it on the live server and hopefully I will get the same results. I will also create a new backup and test restore on all servers. Will let you know the results.

Thanks for your suggestions
0
 

Accepted Solution

by:
ivron001 earned 0 total points
ID: 36952006
Just in case anyone comes across this issue the export and import feature worked in all scenarios. Once the data was imported into the destination server I was able to make a valid backup that restored for all servers. I was never able to determine what the exact problem was for the original backup file.
0
 

Author Closing Comment

by:ivron001
ID: 36975469
the solutions given were great suggestions however they did not solve my problem. I solved it myself but I was grateful for the time and effort given by those that responded.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question