26 July,2024 by Tom Collins
Question: Is it possible to restore a higher version of SQL Server to a lower version though a .bak file?
Answer: SQL Server doesn't support restores from a higher version to a lower version using .bak backup & restore method. There are other ways to migrate from a higer version to a lower version , but they are outside of the scope of this question
This is a sequence of steps to test out the theory . The end result is an error message
On the source server - source1 : select @@version; Microsoft SQL Server 2019 (RTM-CU25-GDR) (KB5036335) - 15.0.4360.2 (X64) create database restore_test; backup database restore_test to disk = 'F:\Backups\test\restore_test.bak' On target server - target1 select @@version; Microsoft SQL Server 2016 (SP3-GDR) (KB5029186) - 13.0.6435.1 (X64) USE [master] RESTORE DATABASE [restore_test] FROM DISK = N'E:\Temp\restore_test.bak' WITH FILE = 1, MOVE N'restore_test' TO N'E:\MSSQLSERVER\DATA\restore_test.mdf', MOVE N'restore_test_log' TO N'F:\MSSQLSERVER\DATA\restore_test_log.ldf', NOUNLOAD, STATS = 5 GO
Msg 3169, Level 16, State 1, Line 2
The database was backed up on a server running version 15.00.4360. That version is incompatible with this server, which is running version 13.00.6435. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |