SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 EMERGENCY!!!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 10/05/2005 :  16:04:34  Show Profile  Reply with Quote
Guys!

I really please need your help here.

I did a backup of 3 sql7 db's on a nt4 server.
2 succesfully restored on a sql2000 server with windows 2003.
but one did not!
it says:
Msg 3270, Level 0, Sev 16
An internal consistency error occurred. Contact Technical Support for assistance.

I've looked here:
http://support.microsoft.com/?kbid=268481
http://www.tek-tips.com/viewthread.cfm?qid=1122183&page=1
http://www.tek-tips.com/viewthread.cfm?qid=876753

but I havent found the answer yet!

Could this have anything to do with the master db?
Why did two work and one didnt?
disk space is not an issue

I really have to get this working within the next hour. My balls are on the line!
please help

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 10/05/2005 :  16:07:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
Run DBCC CHECKDB on the SQL Server 7.0 database that did not restore successfully. Does it show any inconsistency errors? If so, then you need to get that fixed prior to upgrading it to 2000. For inconsistency errors, you can post the output of the DBCC command in the Data Corruption forum here, which MS looks at and responds to.

Tara
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 10/05/2005 :  16:29:21  Show Profile  Reply with Quote
Thanks for that Tara

But I cant do that. We trashed the live server and rebuilt it. Sql7 isnt on it anymore

Is there anything else that could work?
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 10/05/2005 :  16:31:10  Show Profile  Reply with Quote
I ran restore verify only and it said it's all good
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 10/05/2005 :  16:39:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
You don't have the SQL Server 7.0 server anymore! Oh my! Did you run DBCC CHECKDB prior to rebuilding it? Did you verify that your backup files were restorable to 7.0 prior to rebuilding it?

I believe your backup file is corrupt or that the original database was corrupt when it was backed up.

You'll probably need to contact MS directly for assistance with recovery.

Tara
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 10/05/2005 :  16:39:37  Show Profile  Reply with Quote
I am currently trying this advice on a test system:
"By any chance did you changed your service packs in between.
If yes, try to install the SQL server in a new machine with the exact
service pack level when you did the backup. After that copy the backup file
to that machine and try restoring the database."
Go to Top of Page

mramir74
Starting Member

USA
3 Posts

Posted - 10/05/2005 :  16:49:13  Show Profile  Reply with Quote
If you had a server backup, you can maybe recover off that. Restore the databases, then fix the corruption.
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 10/05/2005 :  20:47:56  Show Profile  Reply with Quote
After a 3hour call to Microsoft the result is:
The file that the backup was in became corrupt - probably thanks to "hardware".
Basically they said they cant be sure what caused the problem. But running "restore verify only" means nothing in this case because it does not check through every segment of the file. So my conclusion is that I cannot trust MS SQL Backup (to disk especially): When it says that the file is succesfully backed up and verfied, it's lying. The detach/attach approach would have run the same risk of file corruption. The best way in future will be:
1) use dts export wizard to make a copy on another server
2) check that I have a valid copy
3) Trash and rebuild the server I am doing the OS upgrade on
4) Dts back in

I can't believe it. I've had to restore from tape and lose a day's data. It's 1:45am. Come 7:30am, there's going to be 200 unhappy, angry users and managers who lost some money and work as a result of this.

I love this industry.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/05/2005 :  23:29:08  Show Profile  Reply with Quote
You can gripe about Microsoft all you want, but you have to look at your own plan as being a big part of the problem. When you make a major move like this without giving yourself a fallback, you may be successful and you may end up with what you have.

Hardware problems happen, and if you are responsible for a database, you have to have a plan for what you are going to do about it before the trouble starts.

You could have done things like restoring the backup file to test it before trashing the other server or going back to a prior days backup and rolling forward from transaction logs. Did you have backups on tape that you could recover from?



CODO ERGO SUM
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 10/05/2005 :  23:35:54  Show Profile  Reply with Quote
you totally blew it when you performed this operation.

you should have copied the offline datafiles as well as performed a backup. Not only that, you should have done a test run with your backups prior to smacking the old system. you only have yourself to blame.

This should be a lesson to every DBA that reads sqlteam. Always, always test your backups.


-ec
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 10/06/2005 :  00:24:37  Show Profile  Reply with Quote
point taken

allow to say though that i did test the backup before i trashed the server. it worked. but when i tried it later, it didn't
so the file must have got corrupt while it was sitting on the drive. so if i had a copy of the detached offline files as well (as you suggest), they would have been succeptable to the same problem - i presume.

i think your words are a little strong and presumptious eyechart. especially as microsoft agreed i did everything by the book (with the exception of making a backup to tape instead of disk) before trashing it
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 10/06/2005 :  01:15:06  Show Profile  Reply with Quote
quote:
Originally posted by coolerbob

point taken

allow to say though that i did test the backup before i trashed the server. it worked. but when i tried it later, it didn't
so the file must have got corrupt while it was sitting on the drive. so if i had a copy of the detached offline files as well (as you suggest), they would have been succeptable to the same problem - i presume.

i think your words are a little strong and presumptious eyechart. especially as microsoft agreed i did everything by the book (with the exception of making a backup to tape instead of disk) before trashing it



Sorry if the words are strong, but I feel it to be true. I am a DBA, an the first and foremost thing on my mind when we ever do any change to production is data recovery. I have had situations in the past where unforseen circumstances caused issues that we only got out of because of our preparation.

I guess my point here is that if you have several methods to back things up (backup from Sql server, backup by copying the data files when database is offline, etc.) then you do all of them. Becuase you just never know when something is going to go wrong.

Like I said, this should be a lesson to every DBA who reads sqlteam.



-ec
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 10/06/2005 :  12:56:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
RESTORE VERIFYONLY only "checks to see that the backup set is complete and that all volumes are readable". It does not mean that the file is restorable. That's why the only way to verify this is to actually run RESTORE DATABASE. This should have been done on another 7.0 server that way when the server was rebuilt, you'd still have the database elsewhere. I wouldn't have gone to tape with it. And I would have detached the database and immediately copied those files to another server. The chance of the file becoming corrupted within the amount of time between it being detached and then copied over would have been very small.

When doing a SQL Server upgrade, I prefer to use the two server method that way you can always fall back to the original server quickly. Yes it requires more hardware, but for mission critical applications it is a necessity.

Tara
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/06/2005 :  13:53:39  Show Profile  Reply with Quote
"I prefer to use the two server method"

Me too. Its the only way I've been prepared to do it in the past ...

Kristen
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/06/2005 :  14:16:57  Show Profile  Reply with Quote
Bob is no longer cooler.....



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 10/07/2005 :  04:00:02  Show Profile  Reply with Quote
Thanks for all that guys. I have come to the same conclusions on the two server method. That is certainly the way I will be insisting on it being done in future.
For six months I have been backing up and restoring from and to disk every week. I even when through a trial run from the live system the day before - and everything went fine. In my experience, I had no reason to doubt the backup. When MS-SQL said "Backup operation completed successfully" (or what ever it says), I have never had a reason to doubt it. But I will struggle to trust it again in a mission critical operation.

Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/07/2005 :  06:03:15  Show Profile  Reply with Quote
If there are corrupted data structures which are undetected in the database, or you have a hardware fault, its kinda hard for SQL to know!

AFAIK restoring backups to another server is the only way to know that your backups are "good"

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000