SQL SERVER – FIX Error 3169 – The database was backed up on a server running version

SQL SERVER - FIX Error 3169 - The database was backed up on a server running version error-500x500 At this rate, I think I am going to exhaust the whole error set available in SQL Server. I am glad that I am able to get into unique situations and then resolve them too. And this is more of a diary of the error messages I am getting into. I was playing with my Demo database on SQL Server 2016. Once done, I started restoring from the previous copy.  But is failing with below error 3169:

Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 13.00.0801. That version is incompatible with this server, which is running version 12.00.4213. 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 1
RESTORE DATABASE is terminating abnormally.

Then I realized that I have taken back-up from 13.00.0801 which is SQL Server 2016 and I was trying to restore on 12.00.4213 which is SQL Server 2014. The error message shows both versions, source and destination.

So, it’s is clear that there is no direct way to downgrade from a higher version to a lower version and it would fail with an error message. Why? When an instance of SQL is upgraded, not only do the binaries for the database engine change, the schema level for the databases also changes. So it would not be possible to attach a database with a higher schema level to an instance on a lower version of SQL.

What can be done? One possible approach is to manually export and import the data. You can follow the below steps:

Create an Empty Database

  • In Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Generate Scripts. Follow the steps in the wizard to script the database objects.
  • On the Choose Objects page, select “Script entire database and database objects”.
  • On the Set Scripting Options page, select Save scripts to a specific location.
  • Select the Advanced button; under “Types of data to script” select “Schema Only” and under “Script for server version” select “SQL Server 2008” (or appropriate version)
  • Click on Next and finish to complete the process.
  • Now you can use the generated script file and execute this against the SQL server 2008 instance to create the complete database schema without data.

Move the data

To move the data, you have a couple of options, but all of these are a bit tricky.

  1. Script out the data exactly the way described in the above steps, just by selecting “Types of data to script” as “Data Only”.

Or

  1. Script out the data for every object individually one by one instead of complete database if you have a large database.

Or

  1. BCP to export the data into files and then again use BCP to import the data back into the tables onto the new server.

In short, moving back-up from higher to lower version is not possible. Only option we have is to move the data using the ways described above. Do you know any more ways?

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – SSMA Error : System.Data.OracleClient requires Oracle client software version 8.1.7 or greater
Next Post
Interview Question of the Week #053 – What is the Difference Between Deterministic Functions and Nondeterministic Functions?

Related Posts

11 Comments. Leave new

  • Don’t forget you can also use SSIS to do it as well. I personally have several workflows between varying versions of SQL Server. It also allows me to switch the collation of the columns on the fly since the source server is non-standard.

    Reply
  • SSIS can help to transfer data from one server to another, as mentioned by Houndin.

    Reply
  • Thank you Pinal for the nice article :)

    Reply
  • Error:
    The database was backed up on a server running version 8.00.0760. That version is incompatible with this server, which is running version 11.00.2100. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.

    Solution:
    SQL Server 2000 backups are not supported on SQL Server 2012. Microsoft SQL Server only supports 3 previous versions.
    Try downloading an evaluation copy of SQL Server 2008/2008 R2 and restore using that version of SQL Server. Then from there upgrade to SQL Server 2012.

    Reply
  • Using RedGate Data Compare is another option.

    Reply
  • Khalid Mehmood
    May 12, 2020 11:48 pm

    Hi Pinal, Thanks for the nice and decent article. What about Microsoft® Data Migration Assistant v5.2 ??

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version