August 22, 2009 at 1:34 am
Dear all,
Currently I run my database (let's call it MYDB) on SQL2000,
I backup MYDB (1.3 Gb of MDF and 800 of LDF) into MYDB.bak
when I tried to restore MYDB.bak to SQL2000, it was successful.
I want to migrate my database into SQL2008, first I tried to restore
MYDB into SQL2008 Express, and it failed with the following message :
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 198872 pages for database 'MYDB', file 'MYDB_Data' on file 1.
Processed 1 pages for database 'MYDB', file 'MYDB_Log' on file 1.
Converting database 'MYDB' from version 539 to the current version 655.
Database 'MYDB' running the upgrade step from version 539 to version 551.
Msg 601, Level 12, State 3, Line 4
Could not continue scan with NOLOCK due to data movement.
Msg 3167, Level 16, State 1, Line 4
RESTORE could not start database 'MYDB'.
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.
I already have tried the "DBCC TRACEON (9134, -1)"
and I tried to full backup log and shrink it
(hopefully the problem is in LDF, and tried to reset the LDF) but
it still give me the same error.
I'd read that my backup might be corrupt, but it worked fine when I
restore into SQL2000 (for that, I assume my backup not corrupt)
I'd run "DBCC checkdb('MYDB')" in my SQL2000, and found no error.
then, I think it's because the Express Edition, I tried to restore
to my other machine which run SQL2008 Standard, but also give me the same error.
then, I tried to restore in SQL2005 Express, which also failed.
Anybody, any ideas? or clues?
Thanks.
August 24, 2009 at 7:07 am
First have you ran a checkdb against the database to make sure there is no corruption? If you're going to do an upgrade, and are confident you have a good back up of the database as-is, you can detach the database files and reattach them to your SQL 2008 instance. The only caveat is that when you do this your mdf/ldf files will not be able to go back to 2000 (i.e. you can't detach and put them back to SQL 2000 instance). In order to roll back you'd have to do your restore hence having a good backup. Also be aware that 2000 Express editions had a 2 GB limit. Yes, I know that you're attaching to a 2008 Express edition and that should have a 4 GB limit but wierd things happen, y'know?
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
August 24, 2009 at 7:16 am
Yup - you've got corruption in your 2000 database. Run DBCC CHECKDB (YourDB) WITH ALL_ERRORMSGS, NO_INFOMSGS and also run DBCC CHECKCATALOG (YourDB).
The upgrade step is failing with 601 - clearly corruption.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
August 29, 2009 at 1:43 am
Mr Jorge,
Thanks for your concern,
I have done some testing on the 2008 Express, by pumped very large of virtual transaction data into it (using my initial database), until it's limit. which still above my requirements.
Also, after I migrate to 2008 based, I will not try to turn back to 2000 again.
Mr Paul, (I've read that you have a very good reputation, I'm amaze...)
I wish I found the 'corruption'.... but I'd run the "DBCC CHECKDB (YourDB) WITH ALL_ERRORMSGS, NO_INFOMSGS" and "DBCC CHECKCATALOG (YourDB)" on MYDB (above case),
returns nothing (succesful)... it keeps me wondering. any further suggestion? (case #1)
on the other hand, I have about 10 databases, which the largest is MYDB (above case),
and the second (let's call it MYDB2) give me the same error message when I tried to restore
to 2008 Standard / 2008 Express / 2005 Express.
and after I run the DBCC from Mr Randal's post, it gives me the following error message : (case #2)
( it does not happen on case #1 )
Server: Msg 8946, Level 16, State 12, Line 2
Table error: Allocation page (1:113232) has invalid PFS_PAGE page header values. Type is 0. Check type, object ID and page ID on the page.
Server: Msg 8921, Level 16, State 1, Line 1
CHECKTABLE terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:113232). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:113232). The PageId in the page header = (0:0).
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify database ID 7 pages from (1:113232) to (1:121319). See other errors for cause.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:121320). The PageId in the page header = (0:0).
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify database ID 7 pages from (1:121320) to (1:129407). See other errors for cause.
CHECKDB found 4 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 4 allocation errors and 1 consistency errors in database 'MYDB2'.
and when I run some script (update statistic table), it returns the following error on a spesific table
I/O error (bad page ID) detected during read at offset 0x000000374a0000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\MYDB2.mdf'.
and the rest of databases (which it size only half of MYDB) succesfully restored to 2008.
any idea for the case #1 and #2?
Thanks.
August 29, 2009 at 7:19 am
Don't know about case 1, but case 2 has irreparable corruption. There's some damage that's preventing CheckDB from running and there is no way that it will be able to repair.
To fix that you're going to have to script out all the objects, bcp out all the data, create a new database, recreate the objects and reload all the data. You may get errors when scripting objects or (more likely) exporting data due to the damaged allocation pages.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2009 at 11:46 am
The databases that are failing have corruption that's preventing the upgrade steps from working. On 2000 there are plenty of metadata corruptions that DBCC CHECKCATALOG won't find - it was essentially unchanged from 7.0, before I took it over. For the databases that are failing to upgrade, you have no choice but to export/import the data into a new database before upgrading.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply