| Author |
Topic |
|
dewacorp.alliances
452 Posts |
 Posted - 2006-11-07 : 17:31:59
|
Hi allWe've created a job for automatic restore into other server. At the end of this step is doing DBCC CHECKDB to verify the database.We restore as follow [SERVERNAME_DBNAME_DRT] as database name. Every user databases are ok except master db. I had the following error: quote:
Page (1:10) in database ID 41 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
quote:
DBCC results for 'sysfilegroups'.There are 1 rows in 1 pages for object 'sysfilegroups'.CHECKDB found 1 allocation errors and 0 consistency errors in table 'ALLOCATION' (object ID 99).
Check the master on SOURCE DB SERVER by doing DBCC CHECKDB and it's ok ?!?!Do I need a special treatment to handle this restoration for master in TARGET SERVER? I thought they are the same thing as the other. I'm appreciated your comment/input.Thanks |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-11-08 : 04:58:23
|
| Hi JenI restored the master database similar like restoring any other User database through TSQL stuff and restored into different name which SERVERNAME_master_DRT.Unless I am not suppose to restore into different server. How do I test the restore for master database then?BTW ... I am using SQL2000. |
 |
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-11-08 : 19:20:13
|
| Looking at the object id = 99, I check on sysobjects of the SOURCESERVERNAME_master_DRT and I could not find anything about this ID=99 or object called "ALLOCATION". I even check on the master database on the SOURCESERVERNAME server and nothing at all about the ALLOCATION or id=99.Any ideas? Does this mean the backup is corrupt?Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-09 : 04:02:13
|
| "Unless I am not suppose to restore into different server."Your plan is fine: restore the MASTER backup to a different server using a XXX_master_YYY style database name for the restored database.Have you tried DBCC CHECKDB on the original server/master database? Hopefully it isn't corrupted, but if it is that might be a good place to start!What version of SQL Server are you using? i.e. the complete output from:SELECT @@VERSIONThis may help:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53072Kristen |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-11-09 : 16:28:03
|
quote:
Have you tried DBCC CHECKDB on the original server/master database?
Yes ... I did ... it's all good. quote:
What version of SQL Server are you using? i.e. the complete output from.
The TARGETSERVER: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1) The SOURCESERVER1: Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4) The SOURCESERVER2: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4) Does the restore needs to be same spec? In fact that the SOURCESERVE2 is the same as TARGETSERVER (SP3a). |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-10 : 04:17:54
|
| "Does the restore needs to be same spec?"Unless there is something special about MASTER (which I doubt in this context) you are fine so long as the target SQL Server version is the same, or later [within reason - you can't restore a SQL 6.5 backup file onto a SQL 2000 server!]As the CHECKDB was OK on the Source machine I suggest copying a freshly made backup and retrying the operation, if you haven't already, in case the file just got corrupted somehow - and if it did Great! your process has detected a failure in a backup file which would have otherwise gone unnoticed.Kristen |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-11-11 : 06:58:50
|
| Hi Kirsten"As the CHECKDB was OK on the Source machine I suggest copying a freshly made backup and retrying the operation, if you haven't already, in case the file just got corrupted somehow - and if it did Great! your process has detected a failure in a backup file which would have otherwise gone unnoticed."The thing is the I made the restoration automatically (run every day) and everytime restoring MASTER for SOURCESERVER1 and SOURCESERVER2, they both have an issue. It can't be everyday the backup is corrupted.Should I ignore this erorr? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-11 : 07:23:42
|
| "It can't be everyday the backup is corrupted."I agree."Should I ignore this erorr?"I wouldn't! You may need to restore that database some day ...I suggest you re-ask the question in the Data Corruption Issues forum - some of the folk over there may have a better idea what the problem is. (Please post a link back to this thread so they see how far we got so far!)Kristen |
|
|
|
Kristen
Test
22859 Posts |
|
|
|
SQL2K: Restoring and DBCC CHeckDB for Master db