2008 to 2017 migration missing db objects RRS feed

  • Question

  • We are working on migration project which we migrate 2008 to 2018.
    After backup and restore we found objects(indexes,tables,views) missing in target 2018. what could be the reason here.
    It seems dba has set the compatibility 110 ( actually 140 is for 2018)before restoring in target .. could that be the reason for missing objects.
    msdb db for 2008 and 2017 will not be same .  for example MSDB. autoadmin_master_switch tables missing in target (2018) and 2018 msdb has more new tables
    Please suggest

    Thursday, November 7, 2019 9:03 AM

All replies

  • Hi,

    Please let us know which mechanism used for db migration ( Import/export , backup/restore , mdf & ldf file attach).

    Regards, Kiron

    • Edited by -Kiron Thursday, November 7, 2019 9:19 AM ,
    Thursday, November 7, 2019 9:09 AM
  • Hi

    backup and restore.

    Thursday, November 7, 2019 9:45 AM
  • After backup and restore we found objects(indexes,tables,views) missing in target 2018
    That's impossible, a restore create an exact 1:1 copy of the source database. Is it possible that you restored an old backup, where the objects didn't exists?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, November 7, 2019 11:16 AM
  • As Olaf mentioned, backup/restore will give you a binary copy of the database (and then the upgrade process will update the system tables). So, if you are lacking tables and such, you most likely restored an old backup.

    Also, you refer to the mdsb database, which confuses me. You don't upgrade msdb by restoring an old version of it. Can you elaborate where msdb come into the picture?

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, November 7, 2019 12:30 PM
  • Hi supersent,

    >> msdb db for 2008 and 2017 will not be same . 

    The versions of SQL Server need to be the same for the source and destination server when restoring the msdb database. If the versions for the source and destination servers do not match, then the restore will fail. For more details, please refer to https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-system-databases-sql-server?view=sql-server-2017#limitations-on-restoring-system-databases.

    Are you sure you are migrating SQL Server 2008 to SQL Server 2017 using restore and backup?

    Best Regards,

    Amelia Gu

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 8, 2019 8:23 AM