Upgrade SQL SErver to version 2019

baghul

In our corp environment, we mostly are running sql 2014 and sql 2016. We also use Redgate SQL Backup as our backup and recovery solution.
We want to upgrade to SQL 2019 next month.

I know that when you do native back in SQL (say version 2014) and restore to a newer version SQL (say version 2016), it will upgrade the SQL version to 2016. As part of the restore process. 

Question is that: Can I backup using RG SQL backup on server w SQL 2016 and then restore on a new server running version 2019? Will it work? number one
and
Will it upgrade the db to version 2019?

Thank youn in advance! 

Jon_Kirkwood

Hi @baghul

Thank you for reaching out on the Redgate forums regarding your SQL Backup query.

The behavior with SQL Backup should be much the same as restoring an older SQL Server db through SSMS

 

1 - Yes you can restore an older db backup to a newer version. It's not the same case going from a newer version to older however.

 

2 - The restored db should be a SQL Server 2019 database with a compatibility level set to SQL Server 2016 (130)

This can be viewed in SSMS through database properties > Options > Compatibility level 

 

 

 It would certainly be recommended testing a full backup & restore on SQL Server 2019 to verify the database is being created as you expect.

Depending how you conduct your SQL Server upgrade it may be necessary to re-deploy the SQL Backup agent and conduct a manual restore to create a baseline for your backups in the new environment.

Demers

It is generally possible to use a backup taken with a tool like Redgate SQL Backup to restore a database on a server running a newer version of SQL Server, such as SQL Server 2019. However, the specific details will depend on the version of Redgate SQL Backup that you are using, as well as the version of SQL Server that the backup was taken from.

When you restore a database that was backed up on an older version of SQL Server to a newer version, the database will be upgraded as part of the restore process. This can cause changes to the system tables and other database metadata, which can in turn cause compatibility issues with bitlife certain features or third-party tools.

It's important to check the backup compatibility of the specific version of Redgate you are using,and also check the compatibility of SQL 2016 with SQL 2019, the version of your software and the version of the SQL Server.

baghul

Thank you @Jon_Kirkwood and @Demers
We are continuing to test and will report back.