1

Recently I have started to see an intermittent error when trying to restore database 'X' via an agent job. The job runs a T-SQL script that restores a database from a 2008 R2 (production) instance to a 2016 (data warehouse) instance. The compatibility level (100) of the production database is not changed on the 2016 instance. Both instances are virtualised; the 2008 R2 instance runs on Server 2008 R2, and the 2016 instance runs on Server 2012.

All the pages appear to have been processed correctly, and all files are restored ok; I know this because I run a number of ETL processes against the database. During the restore the database seems to go through a process of "upgrading itself"; "Converting database 'X' from version 661 to current version 852. [SQLSTATE 01000] (Message 944) Database 'X' running the upgrade step from version 661 to version 668."...

Twice in the last two weeks this job has reported a failure. The final upgrade step that is reported is from version 695 to 696, however I think this is possibly due to the message no longer recording any more information. The job fails with Severity 16 and Message ID 5069. When this error first occurred I ran DBCC CHECKDB against the failed restore and it returned no errors (this is actually the second step of my agent job, after the db restore).

It is possible that this restore takes place during a system backup, and I am looking in to the possibility that this could be causing the problem. However, I hadn't changed anything yesterday (give me a break it was New Years Day!) and the job executed successfully this morning, with no errors.

I have attempted to lookup the message ID however this appears to be a general message ID for numerous events.

Is anybody able to tell me why this job may be failing, and point me in the direction of any material that may help me understand this issue, if it is an issue? It would also be interesting to know what process is going on here during the restore (i.e. what is the 'upgrade process' doing and why?).

The full error message is as follows:

Executed as user: DOMAIN\user. ...e 'X_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 4595488 pages for database 'X', file 'X_1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 8 pages for database 'X', file 'X_XXXX_Data_01' on file 1. [SQLSTATE 01000] (Message 4035) Processed 6 pages for database 'X', file 'X_Log' on file 1. [SQLSTATE 01000] (Message 4035) Converting database 'X' from version 661 to the current version 852. [SQLSTATE 01000] (Message 944) Database 'X' running the upgrade step from version 661 to version 668. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 668 to version 669. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 669 to version 670. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 670 to version 671. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 671 to version 672. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 672 to version 673. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 673 to version 674. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 674 to version 675. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 675 to version 676. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 676 to version 677. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 677 to version 679. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 679 to version 680. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 680 to version 681. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 681 to version 682. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 682 to version 683. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 683 to version 684. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 684 to version 685. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 685 to version 686. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 686 to version 687. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 687 to version 688. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 688 to version 689. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 689 to version 690. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 690 to version 691. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 691 to version 692. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 692 to version 693. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 693 to version 694. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 694 to version 695. [SQLSTATE 01000] (Message 951) Database 'X' running the upgrade step from version 695 to version 696. [SQLSTATE 01000] ... The step failed.

NOTE: I have tagged sql-server-2008-r2 as this is the version of the production instance

  • In all your question you have not posted exact error message which you got from job history. If there is limited message in job history. Select job properties go to advanced and give output file location in output section to capture detailed output when job runs next time. You can also manually try restoring and see the message. Are both editions of SQL Server 2008 R2 and SQL Server 2016 same ? – Shanky Jan 2 at 9:34
  • @Shanky I didn't post the full error message as it is rather long and arduous; I was hoping to be a little more succinct and try to provide snippets. I will update the question. – DimUser Jan 2 at 10:18
  • both editions are not the same. 2008 R2 is Enterprise edition and 2016 is Standard – DimUser Jan 2 at 10:23
  • Since both editions are not same its quite possible that when you are using any enterprise feature in SQL Server 2008 r2 and that is getting recorded into backup and eventually when you are doing restore in SQL Server 2016 standard it is failing since standard will not have enterprise feature, just confirm, the very last part of restore failure message will have this information – Shanky Jan 2 at 10:29
  • I have configure the output file location for the job step, hopefully this will provide the information needed when the error occurs again. Thanks for the advice. – DimUser Jan 2 at 10:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.