February 22, 2008 at 5:26 am
Guys,
during recent DR testing we performed a restore of 4 of our primary databases from our management sql server.
3 of the databases restored correctly, however the final database failed to restore at the 99% mark due to a read error.
so we created a new backup and kept the old one in place and again the new backup failed to restore.
so we tried to copy the file to a new server and restore again - failed on the file copy to the new server at 99% due to a read error.
SO we then looked at hard disks - no faults on the raid array - scandisks report no issues.....
performed a checkdb on the database - no issues....
the fact that both backup files were corrupt leads me to think this is not a hardware fault. - so now i'm wondering what else can cause this corruption.
It's a windows 2000 server and the backup has just recently crept over the 50GB mark, previous tests on a 45gb database restored ok. - is there an issue with windows?
sql 2000 is on service pack 4 with 2187 hotfix (enterprise edition)
or could it be some corruption within the backup mechanism (sqlmaint) or database itself?
any thanks in advance
MVDBA
February 22, 2008 at 6:35 am
Hey,
I would suggest you try to restore using the verifyonly option to make sure the backup set is valid.
RESTORE VERIFYONLY
FROM DISK = ???????????????
Let me know results.
Thanks,
Phillip Cox
February 26, 2008 at 11:43 am
You should verify the backup, as Phillip stated.
Is the "management sql server" instance running on SQL 2000 sp4, or is it SQL 2005? I am asking because this is posted in the SQL Server 2005 forum.
Keep us posted.
Thanks,
Adam
February 27, 2008 at 11:47 am
Verifying database backup file is one. Other is that just run a DBCC CHECKDB on the server database where the backup was taken. to see if there are any torn pages in the server. backup of databases with torn pages will fail at restoration.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 29, 2008 at 3:24 am
guys,
apologies for placing this in the sql 2005 section was meant for sql 2000
but have tio flame most of you here....(regret).....
if you read the original post
this is sql 2k sp4 with 2187 therefore comments about sp4 are not relevant
if you read the original post i've done a checkdb... don't even go there
as a matter of course (and having used sql 2000 since it was born) backups are verified. - this issue i am having (and have never experienced with any other system) is that even the file copy won't work... there is a problem with the .bak file even though a new one is created each time.
as a n experienced and time served DBA i'm checked all of the obvious. any help on the out of the ordinary/maybe windows issues??
Thanks in advance
MVDBA
February 29, 2008 at 9:17 am
Did you check the file properties. any compression or encryption in the backup file as that may lead to the file not being restored.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 29, 2008 at 9:33 am
Hi Mike,
Ok, you're not gonna like this one, but seems you have exhausted all avenues for a resolution. So, if possible, let's eliminate the native SQL Server for restore and try a 3rd pary application such as Quest LiteSpeed, as it is able to read .bak files. You can download a trial copy for the test. If this works, then further investigation of why the native restore fails will be required. As you'll know, a solution must be found, as if you have a real DR scenario in future, you don't want an issue such as this to surface.
In addition, does your server have SAN storage or local? If so, has caching been enabled, if exists?
Thank you,
Phillip Cox
MCITP - DBAdmin
March 3, 2008 at 4:59 am
already ahead of you - attempting to use redgate sqlbackup.
we suspect this may be a an o/s specific issue in files larger than 50GB.
Cheers
MV
MVDBA
March 3, 2008 at 8:51 am
Mike
Interesting issue, have never heard of this happening. I have restored much larger databases from both tape and disk backups. Never had an OS issue at all. I don't think it would be a bad spot on the physical disk, as you have stated that you accomplished a new backup and it would strange for the new backup to be placed in the exact same spot, (unless you deleted the bad file first).
Now the question is how to solve, try to copy the file over to a different drive and see if there are any errors thrown by the copy process. If so, then you know there is an OS error with the file, if not, then it's time to look deeper at SQL Server to determine the problem.
If no error is thrown by the OS during the copy process, make a smaller backup of the database (such as file group or even down to the table level), and determine if the files restore correctly. Keep increasing what is actually backed up until you reach the full backup and hopefully something will raise it's ugly head as to the real issue.
Hope this helps but keep us informed as I think this is a very interesting issue.
Marvin Dillard
Senior Consultant
Claraview Inc
March 3, 2008 at 10:02 am
Hi Mike,
Please let us know the outcome of Redgate restore, but in the mean-time, still thinking.
Cheers,
Phillip Cox
MCITP - DBAdmin
March 3, 2008 at 12:13 pm
Mike,
Since you are already looking at Red-Gate SQLBackup, try creating a new backup using SQLBackup and compression to create a smaller file. Try restoring that file - copying that file, etc...
If you do not get any errors, I would think you are probably right about there being something going with the O/S.
Do you backup across the network? Or, do you backup locally? If locally, what happens when you run a RESTORE VERIFYONLY on the source system?
Could be a problem with copying the file across the network instead of an O/S issue. Also could be a problem with the SAN storage (are you using a SAN?).
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 3, 2008 at 9:59 pm
I am not sure this might work, but just check the disk that you are restoring the database is not compressed.
Regards,
Supriy S. Fale
March 4, 2008 at 2:13 am
hi guys
will be performing the red gate backup and test restore today, but in the mean time
file copy to another disk fails (as per original post) - which is why we suspect os/patching issues.
ps - anyone using redgate sql backup - small note - don't import all of your servers from enterprise manager/sql management studio if you have any more than 50 servers registered.
I have over 200 servers registered and now sql backup won't respond and uses all my ram and CPU....
:sick:
MVDBA
March 4, 2008 at 6:16 am
Mike
Sorry, missed the file copy part in the original post. I would say you have either a disk error (hotspot) or the OS is not handling the file as it should. Of course SQL Server could have created a bad file, however you have repeated the backup and the same thing happens which would lead me to put that lower on the possible causes.
I suppose you already ran the old dos chkdsk which might let you know if it is a disk issue.
Remember, if all else fails, there is the windows standard fix, if you can, reboot the system so that it can remember its main purpose in life(small joke but not kidding, had to do it many times).
Good luck with the redgate backup
Marvin Dillard
Senior Consultant
Claraview Inc
March 4, 2008 at 9:20 am
hi
maybe you have license problem .
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply