Moving backuped db's from SQL 2017 to SQL 2014

Question

Moving backuped db's from SQL 2017 to SQL 2014

rtrim(ltrim(' '))

SSC Eights!

Points: 878
More actions
April 10, 2019 at 12:15 pm

#3089591

We have a process now that moves Prod Db's .bak from 2008 r2 to 2014 for reporting/DW. We are planning on upgrading our Prod server to 2017.
Will we still be able moved backed up copies of SQL 2017 db's down to SQL 2014?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

no.

you can never restore a database from a higher version of SQL to a lower version. you will need to upgrade your 2014 instances to be 2017 as well.

a database will be upgraded from a lower version to a current version if you restore it from a lower version. the database structure is actually stored in a format unique to the current version of SQL instance.

compatibility level only controls what syntax is allowed to hit the database, it doe snot modify the underlying mdf structure; that is why you can toggle a databases compatibility back and forth at will.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rtrim(ltrim(' ')) SSC Eights! Points: 878 More actions · Answer 2

Thanks Lowell! Any chance there's a Microsoft source to back this up?

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

First page that comes up in a search for "sql server backup" is this one. It includes this quote:

If you restore an older version database to SQL Server 2017, that database will automatically upgrade to SQL Server 2017. This precludes the database from being used with an older version of the Database Engine.

The second result is for the RESTORE command and includes this callout:

Note
No SQL Server backup be restored to an earlier version of SQL Server than the version on which the backup was created.

Finally, Microsoft's documentation on Restore and Recovery includes this callout:

Important
Regardless of the recovery model of a database, a SQL Server backup cannot be restored by a version of SQL Server that is older than the version that created the backup.

Pretty sure Lowell got it right.

You can't go backwards. There are two exceptions. You can replicate from newer versions to older versions as long as you don't use any newer data structures, indexes, behaviors, etc. (and why are you upgrading then?). You can export everything and import it (and again, no new data structures, indexes, etc., why?). Those are your options.

Upgrade the reporting servers.

"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

rtrim(ltrim(' ')) SSC Eights! Points: 878 More actions · Answer 4

Magnificent. That's exactly what I was looking for. Everything I knew told me it was impossible, but 1) a little confirmation is nice and 2) a statement from Microsoft is the bowtie I need for the higher ups.

Thank you!