If in SQL Server 2019 I restore (using Restore Databases/in object explorer) a bak file that I created in SQL Server 2016 , what version of SQL Server will that database be running (2016 or 2019)?
--Quote me
February 20, 2020 at 7:33 am
2019.
You can change the compatibility level if you want to, though.
February 20, 2020 at 11:09 am
The compatibility level of the restored database will be 130 (SQL Server 2016). You can indeed change it to 140 or 150 if you wish.
John
February 20, 2020 at 12:39 pm
And, you can never restore it back down to 2016 ever again. Just something to be aware of.
"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
Grant, I don't think that's true:
-- Execute on SQL Server 2019 server
RESTORE DATABASE Test
FROM DISK = '\\Path\to\backup\of\SQL2016\Database.bak'
WITH NORECOVERY,
MOVE 'DataFile' TO 'X:\MSSQL\SQLData\Test_Data.MDF',
MOVE 'LogFile' TO 'X:\MSSQL\SQLLog\Test_log.ldf';
RESTORE DATABASE Test WITH RECOVERY;
SELECT compatibility_level
FROM sys.databases
WHERE name = 'Test'; -- 130
ALTER DATABASE Test SET COMPATIBILITY_LEVEL = 150;
SELECT compatibility_level
FROM sys.databases
WHERE name = 'Test'; -- 150
-- Mmmm - this works...
ALTER DATABASE Test SET COMPATIBILITY_LEVEL = 130;
SELECT compatibility_level
FROM sys.databases
WHERE name = 'Test'; -- 130
-- Maybe if we put it back up to 2019 and then
-- make a change in the database, we'll be
-- stopped from dropping back down to 2016?
ALTER DATABASE Test SET COMPATIBILITY_LEVEL = 150;
SELECT compatibility_level
FROM sys.databases
WHERE name = 'Test'; -- 150
USE Test;
CREATE TABLE TestTable (col1 int);
USE master;
-- No, it still lets us do it!
ALTER DATABASE Test SET COMPATIBILITY_LEVEL = 130;
SELECT compatibility_level
FROM sys.databases
WHERE name = 'Test'; -- 130
John
February 20, 2020 at 1:39 pm
No, sorry for the confusion. My fault.
I meant take a backup from your 2019 instance and restore it to 2016.
Apologies for stating that poorly.
"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
February 20, 2020 at 2:00 pm
Ah, right - yes. I wondered whether we might be talking at cross-purposes. Makes sense now.
John
February 20, 2020 at 2:32 pm
And, of course, you're 100% correct on what you showed with the compatibility levels.
"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
February 20, 2020 at 3:54 pm
Hi John Mitchell & Grant Fritchey for answering the question. Couple more along these lines I wonder if you could help me with:
--Quote me
February 20, 2020 at 3:58 pm
John
February 20, 2020 at 6:38 pm
What he said.
"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
September 20, 2021 at 3:15 pm
Follow question on this as I am not 100% clear on what to do here.
If I restore a 2016 backup to a 2019 server, after the restore, I MUST then alter the compatibility of the restored database in order to use the database on the 2019 version. Is that correct?
What happens if this is not done?
I am getting ready to move a database from a 2016 version to a 2019 version and I want to make sure I do it correctly. Thank you!!
September 20, 2021 at 5:10 pm
No, you don't have to. It will still fundamentally work (unless your specific application implements/relies on SQL 2019 features).
Higher-version features may simply not be available/used (e.g., cardinality estimator and query optimizer changes) -- see https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15
September 20, 2021 at 5:20 pm
Thank you so much for the clarification. It appears that I will need to alter the compatibility as the reason I am moving the database is for some JSON stuff that apparently is not available in the 2016 version.
My colleague is doing some work with a Web Service API and has requested that the database be moved to a 2019 version of SQL from the 2016 version that it is on.
Again, I really appreciate your response!
September 20, 2021 at 5:41 pm
Follow question on this as I am not 100% clear on what to do here.
If I restore a 2016 backup to a 2019 server, after the restore, I MUST then alter the compatibility of the restored database in order to use the database on the 2019 version. Is that correct?
What happens if this is not done?
I am getting ready to move a database from a 2016 version to a 2019 version and I want to make sure I do it correctly. Thank you!!
You do not have to change the compatibility level - it can remain on 2016 if needed. You should test your application against the 2019 compatibility to see if there are any issues with that change, as there might be compatibility issues with your application code using something that is no longer supported/available in that compatibility mode.
There may be things that are available in the 2019 compatibility level that are not available in the 2016 mode. If you need to be able to use that functionality then you would need to change the compatibility level.
So - to do this right, upgrade in a test environment and test your application against the 2019 compatibility before migrating/upgrading in production.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply