April 28, 2010 at 9:13 am
Really long post!
Using SQL 2000 Enterprise Edition
I am have a very strange problem when I try to restore a SQL 2000 database.
Server A is a production Server.
Server B is a production Server.
Server C is a backup Server
Our normal backup plan is to use SQL 2000 Enterprise Manager Maintenance Plans to backup the databases from Server A to Server B and the databases on Server B to Server A.
Each month I take a backup file and copy it to server C and perform a restore using Enterprise Manager or using a restore script.
I then run some queries to confirm that all the data is as it should be.
I have been doing it this was for a few years on these machines.
Yesterday, I encountered an alarming condition:
I received this error when I tried to restore a database.
"An internal consistency error occurred.
Contact Technical Support for assistance.
Restore Database is terminating abnormally."
To try to track down what was happening, I ran DBCC CHECKDB on the database in question and the message was no allocation errors and no consistency errors. So to me, that means the database is ok.
I modified the maintenance plan and backed up to Server C and tried to restore again, but received the same error.
I modified the maintenance plan again and backed up locally on Server A, so a database on Server A is being backed up to Server A, not something you want as a solution.
I copied the backup file from server A to server C, the backup server, and tried the restore again.
This time it was successful.
I then ran DBCC CHECKDB ('dbname',REPAIR_ALLOW_DATA_LOSS) on the restored databases
and again it found no errors. I am thinking my database is still in good shape, it is the backup file that is having the problem.
So to summarize,
using enterprise manager to backup across the network seems to be causing the file to become corrupt. Using enterprise manager to backup locally and then copying the backup file is not having a problem.
I have to backup across the network and not locally, other wise what's the point of a back up.
I really need a solution for this and I would appreciate any help in solving the problem.
My company will not buy 3rd party backup software so that is not an option.(no money)
Thanks for taking the time to read through this.
I will continue to try different approaches.
Gary
April 28, 2010 at 9:57 am
It sounds like you have a problem with the network between the servers. When backing up across the network the backup file is being corrupted.
Unless you can guarantee your network connectivity, the best approach is to backup locally and copy the backup file to the other location. Robocopy works well for this.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 13, 2010 at 4:38 am
Hi,
Check the disk space. and restore the database thru wizard wise.
July 13, 2010 at 4:43 am
balaji.ganga (7/13/2010)
Hi,Check the disk space. and restore the database thru wizard wise.
This wont make a difference if the backup set is corrupted.
Agree with Jeffery, your best option is to back up locally and then copy the file across the network
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply