Follow sqlserver-dba.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

SQLServer-DBA.com Links

Dba_db2_button

dba-ninja.com

SQL Server DBA Jobs

Can we restore a database to an older version in SQL Server?

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.

 

 


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

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.

Working...

Post a comment on Can we restore a database to an older version in SQL Server?


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer