CRM installed on wrong SQL instance and version

Help please! 

I have an SQL server where there is the main install (MSSQLSERVER) using SQL Server 2019 and an instance install (Sage) using SQL Server 2016.

Sage CRM 2021 R1 was installed by a colleague on the Sage instance using SQL Server 2016.  I have been asked by another colleague to move the install from the 2016 instance (Sage) to the 2019 instance (MSSQLSERVER).

Therefore, I plan to install Sage CRM on the 2019 instance (MSSQLSERVER).  I don't have any issues or questions there.

My question relates to the database.  Does anyone know if I can backup the database on the 2016 instance and restore it on the 2019 instance without first upgrading the 2016 instance to the 2019?  My gut tells me that I need to do the upgrade on the 2016 version and database before I move it.  However, there are other databases on that install and I don't know that the client wants to upgrade those databases from 2016 to 2019.  Therefore, I need to move the CRM database from the 2016 install to the 2019 install.  Is that possible without first upgrading the database from 2016 to 2019?  I am unsure here.

Any assistance would be greatly appreciated!!  Thank you!!

Parents
  • I wanted to follow up on this as I was able to answer my own question.

    Yes, you can backup the database on the 2016 instance and restore it on the 2019 instance without upgrading the 2016 instance first.

    A couple of things to note here.

    1.  Since you are not restoring the database to the same version it was backed up in, you will need to modify the "Files" Page on the "Restore" interface.  Something like this....

    Original = C:\Program Files\Microsoft SQL Server\MSSQL13.SAGE300CRE\MSSQL\DATA\CRM.mdf

    Restore As = C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\CRM.mdf 

    2.  After you do the restore, in addition to the normal steps you would take when migrating a database, you will also want to update the compatibility_level of the database.  If you check the compatibility_level in sys.databases for CRM, you will see it is at 130 (for 2016) when you wanted it to be at 150 (for 2019) after the restore.  It is my understanding that this is not a necessary step unless you want to use features available in 2019 with the CRM database.  In my case, that was the reason for the move.  Therefore, I updated the compatibility_level.

    Hope this information helps someone else that might be trying to restore a database from a previous version of SQL server.

Reply
  • I wanted to follow up on this as I was able to answer my own question.

    Yes, you can backup the database on the 2016 instance and restore it on the 2019 instance without upgrading the 2016 instance first.

    A couple of things to note here.

    1.  Since you are not restoring the database to the same version it was backed up in, you will need to modify the "Files" Page on the "Restore" interface.  Something like this....

    Original = C:\Program Files\Microsoft SQL Server\MSSQL13.SAGE300CRE\MSSQL\DATA\CRM.mdf

    Restore As = C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\CRM.mdf 

    2.  After you do the restore, in addition to the normal steps you would take when migrating a database, you will also want to update the compatibility_level of the database.  If you check the compatibility_level in sys.databases for CRM, you will see it is at 130 (for 2016) when you wanted it to be at 150 (for 2019) after the restore.  It is my understanding that this is not a necessary step unless you want to use features available in 2019 with the CRM database.  In my case, that was the reason for the move.  Therefore, I updated the compatibility_level.

    Hope this information helps someone else that might be trying to restore a database from a previous version of SQL server.

Children