July 3, 2023 at 8:34 am
Hi all, I just want to confirm can I just take a full backup of my user database currently running on SQL 2016 and restore it to a new database on a different server running on SQL 2019? Coming from Oracle DB, I'm not really familiar on how to perform upgrades on SQL databases.
July 3, 2023 at 9:44 am
Yes indeed.
Going up versions is totally supported, so backup 2016, restore to 2019 is all good.
However going down versions is NOT supported, so if you have an issue with the upgrade, it is not an easy rollback, and you will need to look into scripting data changes/double keying the data back into 2016/using bacpac's to move the DB back, but they are not without their own limitations.
July 3, 2023 at 11:19 am
Hi,
after you restored the database on the new sql server 2019, you may change the compatibility level to 2019.
Otherwise you may not use all the new features from 2019.
On the database, right mouseclick -> properties -> options -> compatibility level
Kind regards and good luck,
Andreas
July 3, 2023 at 11:24 am
Yes you can, I've upgraded from SQL 2012 to 2019 by just restoring a backup.
July 3, 2023 at 12:57 pm
Strong suggestion.
Restore the database as everyone has suggested. Then, before you change the compatibility level, turn on Query Store. Run the database for some time with Query Store enabled. A few days, a week, a couple of weeks. Your call. Then, change the compatibility level. Query Store will then be available to help you with queries that are suffering after the upgrade (if any) through plan forcing.
Query Store is an amazing tool for assisting with database upgrades.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply