| Author |
 Topic  |
|
|
coolerbob
Aged Yak Warrior
United Kingdom
841 Posts |
|
|
tkizer
Almighty SQL Goddess
USA
35953 Posts |
 Posted - 10/05/2005 : 16:07:08
|
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 |
 |
|
|
coolerbob
Aged Yak Warrior
United Kingdom
841 Posts |
Posted - 10/05/2005 : 16:29:21
|
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? |
|
|
|
coolerbob
Aged Yak Warrior
United Kingdom
841 Posts |
Posted - 10/05/2005 : 16:31:10
|
| I ran restore verify only and it said it's all good |
|
|
|
tkizer
Almighty SQL Goddess
USA
35953 Posts |
Posted - 10/05/2005 : 16:39:37
|
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 |
|
|
|
coolerbob
Aged Yak Warrior
United Kingdom
841 Posts |
Posted - 10/05/2005 : 16:39:37
|
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."
|
|
|
|
mramir74
Starting Member
USA
3 Posts |
Posted - 10/05/2005 : 16:49:13
|
| If you had a server backup, you can maybe recover off that. Restore the databases, then fix the corruption. |
|
|
|
coolerbob
Aged Yak Warrior
United Kingdom
841 Posts |
Posted - 10/05/2005 : 20:47:56
|
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. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 10/05/2005 : 23:29:08
|
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 |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 10/05/2005 : 23:35:54
|
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 |
|
|
|
coolerbob
Aged Yak Warrior
United Kingdom
841 Posts |
Posted - 10/06/2005 : 00:24:37
|
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 |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 10/06/2005 : 01:15:06
|
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 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35953 Posts |
Posted - 10/06/2005 : 12:56:48
|
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 |
|
|
|
Kristen
Test
United Kingdom
22403 Posts |
Posted - 10/06/2005 : 13:53:39
|
"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 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
coolerbob
Aged Yak Warrior
United Kingdom
841 Posts |
Posted - 10/07/2005 : 04:00:02
|
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.
|
|
|
|
Kristen
Test
United Kingdom
22403 Posts |
Posted - 10/07/2005 : 06:03:15
|
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 |
|
|
| |
Topic |
|
EMERGENCY!!!
