RESTORE could not start database - Possible schema corruption

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » RESTORE could not start database - Possible...

12 posts, Page 1 of 2

1 2 »»

RESTORE could not start database - Possible schema corruption

Rate Topic

Display Mode

Topic Options

Author

Message

ohack

Posted Friday, May 25, 2012 6:16 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:13 AM
Points: 278, Visits: 393

Hi,

I'm unable to backup/restore a database from a SQL 2005 instance (SP3 db compat:80) to a SQL 2008 or SQL 2008R2 instance. The backup appears to be successful but when I try to restore to either of the possible destination machines I get:

Processed 48600 pages for database 'MY_DB', file 'MY_DB' on file 1.
Processed 2 pages for database 'MY_DB', file 'MY_DB_log' on file 1.
Converting database 'MY_DB' from version 611 to the current version 661.
Database 'MY_DB' running the upgrade step from version 611 to version 621.
Database 'MY_DB' running the upgrade step from version 621 to version 622.
Database 'MY_DB' running the upgrade step from version 622 to version 625.
Msg 3167, Level 16, State 1, Line 3
RESTORE could not start database 'MY_DB'.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
Msg 211, Level 23, State 72, Line 3
Possible schema corruption. Run DBCC CHECKCATALOG.

If I run DBCC CHECKDB (MY_DB) WITH ALL_ERRORMSGS, NO_INFOMSGS
I just get "Command(s) completed successfully."

If I check the backup file with:
RESTORE VERIFYONLY FROM DISK = N'E:\MY_DB_25052012.bak'
I get "The backup set on file 1 is valid"

I've also tried re-running the backup with the same results. The database itself on the 2005 instance seems to be functioning perfectly well.
Any troubleshooting suggestions gratefully received.

Post #1306493

anthony.green

Posted Friday, May 25, 2012 6:27 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

What do you get when you run DBCC CHECKCATALOG in the DB?

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1306501

ohack

Posted Friday, May 25, 2012 7:01 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:13 AM
Points: 278, Visits: 393

DBCC CHECKCATALOG (MY_DB)

just gives me the typical "DBCC execution completed. If DBCC printed error messages, contact your system administrator."

Post #1306528

anthony.green

Posted Friday, May 25, 2012 7:07 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Has it logged anything in the SQL error log?

Post #1306536

ohack

Posted Friday, May 25, 2012 7:31 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:13 AM
Points: 278, Visits: 393

The plot thickens. It turns out I am able to restore the backup to a different 2005 instance. On 2008 and 2008 R2 instances on separate machines this causes the above error and also causes a stack dump in the SQL log.

What am I missing here? Looks like this should be a known issue.

I've also tried setting the db compat mode to 90, then running CHECKDB (no errors) and backing up with the same outcome.

Post #1306559

anthony.green

Posted Friday, May 25, 2012 7:35 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

I am guessing that there will be something in the metadata which it doesn't like on 2008.

Can you script the database and run the script on 2008? If so can you then import the data using the import export data wizard?

Post #1306561

ohack

Posted Friday, May 25, 2012 7:38 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:13 AM
Points: 278, Visits: 393

After the stack dump in the log a line appears:

During upgrade, database raised exception 211, severity 23, state 72, address 00000000004A1A24. Use the exception number to determine the cause.

Post #1306567

anthony.green

Posted Friday, May 25, 2012 7:52 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

can you post the full stack dump? mean while script the db and transfer the data.

Post #1306585

ohack

Posted Monday, May 28, 2012 3:59 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:13 AM
Points: 278, Visits: 393

Sorry, management are happy to leave this one on a legacy 2005 instance and I can only pursue in my own time. I can post the stack dump though.

I did start out following the scripting suggestion but the order of dependencies in the script needed work - those were the only errors I saw.

Post #1307456

anthony.green

Posted Tuesday, May 29, 2012 12:39 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Could you not use the generate scripts function in ssms.

It's somewhere on the database when you right click it, can also get it to script out the data and should take care of the dependencies for you, quick way to get a script of the whole scheme and data to replay on your 2008 server.

Post #1307540

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions