| Author |
Topic |
|
dewacorp.alliances
452 Posts |
 Posted - 2006-11-11 : 15:47:39
|
| Hi thereOne of the forum user suggests that I need to post the following issue to this area relating on data corruption.Here's where I am up to in detail: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74599[/url]Summary: Basically, I made a daily automatic testing restore to TARGETSERVER and every database are ok except the master database. I do DBCC CHECKDB on master database in the SOURCESERVER .. and no error what so ever. But the result of restoring of the master backup does. This happens everday.I am appreciated your comment/feedback. |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-11-11 : 20:58:23
|
| Hi valdyv,I'd just like to make sure I understand your situation correctly.You take backups of various databases every day and restore to a secondary system. DBCC CHECKDB of the restored copy of master on the secondary system shows errors. DBCC CHECKDB of master on the source system is clean (this is very important).Is that correct?Paul RandalLead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-11-11 : 23:20:46
|
quote:
You take backups of various databases every day and restore to a secondary system.
Correct and changing the name to SERVERNAME_MASTER_DRT.quote:
DBCC CHECKDB of the restored copy of master on the secondary system shows errors.
Correctquote:
DBCC CHECKDB of master on the source system is clean (this is very important).
Yes ... the DBCC CHECKDB of master on the sourcce system is no error.What makes me surprise is actually I had 2 source servers (let say SOURCESERVER1 and SOURCESERVER2), when I run DBCC CHECKDB on both master databases on the secondary system, they shows error. |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-11-12 : 01:28:23
|
| ok - there's something wrong with the hardware on the secondary system. I take it you can restore the databases on other servers without finding any consistency errors?Paul RandalLead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-11-12 : 03:26:25
|
| are you restoring the master database from the source system on your secondary system?-ec |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-11-12 : 05:54:17
|
quote:
are you restoring the master database from the source system on your secondary system?
No ... I am restoring the master of source system on a secondary system by changing database name into SERVERNAME_MASTER_DRT (the file name for data and TLog still keeps as original - 'master' and 'masterlog'). |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-11-12 : 05:56:48
|
quote:
ok - there's something wrong with the hardware on the secondary system. I take it you can restore the databases on other servers without finding any consistency errors?
But if it's the hardware issue, that doesn't explain why only happening for master database only. Cause I have 15 users database restored to this secondary system including msdb and model ... and they are all fine .. no error when do DBCC CHECKDB. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-11-12 : 14:14:44
|
| the master database does not get restored in a standard way. You first have to put the database in single-user mode. Are you doing that?-ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-12 : 14:56:48
|
| I think you've misunderstood ec (or I have!)valdyv is restoring a backup of master, from a different computer, to a database named XXX_master_YYY (on a different server) so that he can run a DBCC CHECKDB on it. He's not trying to reinstate it as the "master" database on the target server, just trying to prove that the backup file is recoverable.Kristen |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-11-12 : 19:11:54
|
| Hi all.I had another master database from other source and restore successfully but then run DBCC CHECKDB returns an error as before. So it seems that this thing happens only for master database. I am thinking to raise this to Microsoft support. But waiting from you .. just in case if you can point me to the right direction. Or have you guys any issue on restoring and checkdb on master? |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-11-15 : 19:50:07
|
| Anyone can assist me on this? |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-11-15 : 23:47:19
|
quote:
Originally posted by valdyv
quote:
ok - there's something wrong with the hardware on the secondary system. I take it you can restore the databases on other servers without finding any consistency errors?
But if it's the hardware issue, that doesn't explain why only happening for master database only. Cause I have 15 users database restored to this secondary system including msdb and model ... and they are all fine .. no error when do DBCC CHECKDB.
If the restore is overwriting the existing files then its using the same area of disks over and over again - hence the recurring error.Have you run any hardware diagnostics? Checked the SQL error log and Windows event logs for disk errors?Paul RandalLead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-16 : 00:56:05
|
"If the restore is overwriting the existing files then its using the same area of disks over and over again - hence the recurring error."I wouldn't have thought of that.So renaming that database (to lock the potentially bad sectors) and restoring to a newly created database should solve the problem - assuming no more new bad sectors - at least as a quick test.Odd that valdyv is restoring MASTER databases from two different (source) servers, and both masters have problems, but none of the user databases also being restored have. It would be cruel luck if both happen to be sitting on some bad sectors  as I would easily have jumped to the same conclusion that there must be something special about the Master databases. I'd be blaming restoration of my encrypted Sprocs and all sorts  Kristen |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-11-19 : 06:51:36
|
| Mmm ... I will look more carefully one Event Log and SQL Log. But I don't think so it's a bad sector on the disk cause we even have 2 sets of environments (Data Center Servers and National Office Servers .. each environment has it's own separate backup and test restore) and they both returns an error when I run DBCC CHECKDB on the restored 'master' database. They must have some sort of special about this 'master' database when you restore on the different server ... doesn't it? Anyone? |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-11-19 : 15:11:47
|
| You need to call Product Support to help you with this - we're not getting anywhere trying to help you through the forums - and this has been going on for at least 8 days - I'm sure you want to get to the bottom of this before too long.ThanksPaul RandalLead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-20 : 04:03:16
|
| valdyv: I'd appreciate hearing back what the outcome is pleaseThanks,Kristen |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-11-22 : 14:37:48
|
| I will let you know the outcome. At this stage, we are in the process to have support from Ms as big volume instead of one off support. |
|
|
|
|
SQL2K: Issue on Restoring Master database