0

We are migrating a SQL Server 2000 instance to SQL Server 2017. I was able to move all databases successfully through SQL Server backup-restore but having problem with one database. I am even able to move this database but the problem is when users are trying to connect to this database thru Sage ERP application (Version 5.4)- they are getting message:

Damaged Data, DBSETUP: Create Application Table. Table "Table Name" not found.

Though I checked the table exists and there is no error in consistency. We then created a blank database on the SQL Server 2017 instance and tried to create dump of the SQL Server 2000 database through the Sage application into .REC files and moving it to the SQL Server 2017 instance, but we are not able to move it.

There are multiple .REC files, the transfer starts and other .REC files are moved successfully, but it stops at one particular .REC file with this message:

(error=0 native code=0)

The size of the database is 32 GB in SQL Server 2000, but when we move it to SQL Server 2017 through Sage, it becomes 45 GB. This is one thing we noticed. We checked the compatibility and collation are also OK. I also checked the consistency of the database and the particular table, there are no errors. This is what we have tried so far, have been struggling for the last 3 weeks, any help would be greatly appreciated.

Samuel Lelièvre
  • 3,212
  • 1
  • 14
  • 27
Iftekhar Ilm
  • 81
  • 1
  • 9
  • did you run a CHECKDB on original database? And on migrated database? – Mitch Wheat Nov 29 '19 at 08:19
  • Yes I ran DBCC on both databases and its ok. – Iftekhar Ilm Nov 29 '19 at 08:20
  • who is the owner of the original DB? sounds like a permissions problem. – Mitch Wheat Nov 29 '19 at 08:21
  • 2
    Are you sure you have correctly redirected Sage to connect to the new server? Does the new table have permissions that make it visible for the Sage users? – GSerg Nov 29 '19 at 08:22
  • The owner of the original DB is sa. – Iftekhar Ilm Nov 29 '19 at 11:38
  • Yes we are connecting successfully thru sage, we have successfully migrated other databases thru Sage. How do I check if a table has permission to be able to be visible for the Sage users. – Iftekhar Ilm Nov 29 '19 at 11:40
  • And the user who is migrating the data is a sysadmin. – Iftekhar Ilm Nov 29 '19 at 11:41
  • 1
    I just tried restoring sql2000 .bak file to an sql2017 instance but I got `The database was backed up on a server running version 8.00.2305. That version is incompatible with this server, which is running version 14.00.3048`. What are you actually doing? Is there an intermediate sql2008r2 involved in this migration? – wqw Nov 29 '19 at 11:50
  • I understand, while migrating thru SQL backup-restore, I moved the database to an intermediate 2008 r2 server and then moved to 2017, but the problem was database was not accessible thru the Sage application. We then created dump of the database files to .REC files using the Sage app and we were able to move other databases directly from SQL 2000 to SQL 2017 but having problem with only one database. – Iftekhar Ilm Nov 29 '19 at 14:42

1 Answers1

0

It's not possible to migrate a 2000 database to 2017 without a stepping stone server because the formats are too different. So in your case you could restore the 2000 database on a 2005/2008 server, take a backup from there and restore it on an instance running a later version. Paul Randal explains it perfectly here: https://www.sqlskills.com/blogs/paul/you-can-upgrade-from-any-version-2005-to-any-other-version/

Thailo
  • 1,314
  • 7
  • 13
  • I understand, while migrating thru SQL backup-restore, I moved the database to an intermediate 2008 r2 server and then moved to 2017, but the problem was database was not accessible thru the Sage application. We then created dump of the database files to .REC files using the Sage app and we were able to move other databases directly from SQL 2000 to SQL 2017 but having problem with only one database. – Iftekhar Ilm Nov 29 '19 at 14:41