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.
- Script out the data exactly the way described in the above steps, just by selecting “Types of data to script” as “Data Only”.
- Script out the data for every object individually one by one instead of complete database if you have a large database.
- 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)
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.
SSIS surely helps
SSIS can help to transfer data from one server to another, as mentioned by Houndin.
oh yeah – of course SSIS can be used.
Thank you Pinal for the nice article :)
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.
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.
Perfect. Thanks for sharing.
Using RedGate Data Compare is another option.
I didn’t know that. Thanks for sharing.
Hi Pinal, Thanks for the nice and decent article. What about Microsoft® Data Migration Assistant v5.2 ??