May 25, 2012 at 6:17 am
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.
May 25, 2012 at 6:27 am
What do you get when you run DBCC CHECKCATALOG in the DB?
May 25, 2012 at 7:01 am
DBCC CHECKCATALOG (MY_DB)
just gives me the typical "DBCC execution completed. If DBCC printed error messages, contact your system administrator."
May 25, 2012 at 7:07 am
Has it logged anything in the SQL error log?
May 25, 2012 at 7:31 am
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.
May 25, 2012 at 7:35 am
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?
May 25, 2012 at 7:38 am
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.
May 25, 2012 at 7:52 am
can you post the full stack dump? mean while script the db and transfer the data.
May 28, 2012 at 3:59 pm
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.
May 29, 2012 at 12:39 am
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.
May 29, 2012 at 12:51 am
I got the same problem before and finally restore to the same version then export data to the higer version.
May 29, 2012 at 12:58 am
It seems metedata issue and i got the same problem before and finally restore to the same version the export data to higer version.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply