Why Can't I Restore a Database to an Older Version of SQL Server?

By:   |   Updated: 2012-04-17   |   Comments (13)   |   Related: 1 | 2 | > Restore


Problem

We recently upgraded one of our SQL Server to a newer version. When I took the backup from one of the databases on that server and attempted to restore it to our test restore server (where we verify backups), I received an error. The test server wasn't upgraded to the newest version, but there's nothing in the database that's incompatible with the older version. I walked through the prompts and it seemed like SQL Server recognized the backup just fine. However, when it went to actually do the restore, it failed. What am I doing wrong?

Solution

You aren't doing anything wrong. You cannot take a backup from a newer version of SQL Server and restore it to an older version. This is true, even though SQL Server Management Studio obviously appears to recognize the backup, like so:

SQL Server recognizes the backup

If you select to restore this backup, but you are on an older version of SQL Server, you likely received an error similar to this one:


Restore Attempt to Older Version error

In this particular case, SQL Server Management Studio is telling me that when SQL Server attempted to restore the backup, it realized that the backup was from SQL Server 2008R2 but I was restoring to SQL Server 2008. As a result, the restore failed.

Why This Happens

It is important to note that though SQL Server can obviously tell a lot about the backup because it ascertain the right version of SQL Server where this backup was taken, it still can't be forced to restore the backup. Even if all of the elements within the database, all of the tables, views, stored procedures, datatypes, etc. are fully compatible with the older version of SQL Server, the restore will always fail. That's because the version of a database is about more than just those things. There are changes to the databases with each version of SQL Server, and this includes service packs and cumulative updates. For instance, consider if I run the following command, restoring a backup taken on SQL Server 2005 to a SQL Server 2008 server:

RESTORE DATABASE Some_2005_DB
FROM DISK='C:\TEMP\Some_2005_DB.bak'
WITH MOVE 'Some_2005_DB_Data' TO 'C:\temp\Some_2005_DB.mdf',
MOVE 'Some_2005_DB_Log' TO 'C:\temp\Some_2005_DB_log.pdf',
RECOVERY;

I end up getting a bunch of notifications of the database being upgraded:

Processed 296 pages for database 'Some_2005_DB', file 'Some_2005_DB_Data' on file 1.
Processed 2 pages for database 'Some_2005_DB', file 'Some_2005_DB_Log' on file 1.
Converting database 'Some_2005_DB' from version 611 to the current version 655.
Database 'Some_2005_DB' running the upgrade step from version 611 to version 621.
Database 'Some_2005_DB' running the upgrade step from version 621 to version 622.
Database 'Some_2005_DB' running the upgrade step from version 622 to version 625.
Database 'Some_2005_DB' running the upgrade step from version 625 to version 626.
Database 'Some_2005_DB' running the upgrade step from version 626 to version 627.
Database 'Some_2005_DB' running the upgrade step from version 627 to version 628.
Database 'Some_2005_DB' running the upgrade step from version 628 to version 629.
Database 'Some_2005_DB' running the upgrade step from version 629 to version 630.
Database 'Some_2005_DB' running the upgrade step from version 630 to version 631.
Database 'Some_2005_DB' running the upgrade step from version 631 to version 632.
Database 'Some_2005_DB' running the upgrade step from version 632 to version 633.
Database 'Some_2005_DB' running the upgrade step from version 633 to version 634.
Database 'Some_2005_DB' running the upgrade step from version 634 to version 635.
Database 'Some_2005_DB' running the upgrade step from version 635 to version 636.
Database 'Some_2005_DB' running the upgrade step from version 636 to version 637.
Database 'Some_2005_DB' running the upgrade step from version 637 to version 638.
Database 'Some_2005_DB' running the upgrade step from version 638 to version 639.
Database 'Some_2005_DB' running the upgrade step from version 639 to version 640.
Database 'Some_2005_DB' running the upgrade step from version 640 to version 641.
Database 'Some_2005_DB' running the upgrade step from version 641 to version 642.
Database 'Some_2005_DB' running the upgrade step from version 642 to version 643.
Database 'Some_2005_DB' running the upgrade step from version 643 to version 644.
Database 'Some_2005_DB' running the upgrade step from version 644 to version 645.
Database 'Some_2005_DB' running the upgrade step from version 645 to version 646.
Database 'Some_2005_DB' running the upgrade step from version 646 to version 647.

(and so on)

The Options

Therefore, you have a handful of options if you need to get a database from a newer version of SQL Server to an older one:

  • Upgrade the older version of SQL Server to be at the same level as the newer SQL Server.
  • Script out the objects from the newer database and then usp a bcp process to extract the data from the newer database and import it into the older database.
  • Use the SQL Server Import and Export Wizard to build an SSIS package to move the data (it will move the data only).
  • Build a custom SSIS package to do the data move.
  • Use replication to move the data from the newer database to the older one.
  • Use some other form of scripting, such as with PowerShell, to keep the databases in sync.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-04-17

Comments For This Article




Wednesday, April 6, 2016 - 2:06:35 PM - Greg Robidoux Back To Top (41152)

Here is a tip that shows how you can move your data to a lower version of SQL Server.  It is not as simple as doing a restore.

https://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-server-database-to-a-lower-version/

Thanks
Greg

Wednesday, April 6, 2016 - 1:49:44 PM - K. Brian Kelley Back To Top (41151)

Dave,

  Keep in mind that we are talking a SQL Server version change here. In other words, the difference going from SQL Server 2012 back to SQL Server 2008 R2 or a case where there's been an update to SQL Server, such as a service pack. Therefore, if you're talking about rolling back an application deployment, that's not an issue. So the first scenario you give doesn't apply.

Keep in mind that in the second case it's not just about features. Metadata structures that are internal to SQL Server could have changed. If you have to support taking a SQL Server DB and being able to roll it back to older versions, especially the case where you're going from 2012 back to say 2008, then you can't move forward. You can't do things like provide for TDE. 

I would challenge you to find a case with a commercial RDBMS product where the data is more than just a straight SQL dump where you can do what you're asking for. 

Wednesday, April 6, 2016 - 10:37:35 AM - Dave Back To Top (41150)

Except no big deal if you can't play your game, but is a big deal if you have to roll back a SQL deployment where data has changed or just want to move a database to an older server that does not need to be upgraded and would suite the database usage feature wise.

You would not have the new code features of course, but the data could likely be loaded into the older database system just fine. Seems unlikely M$FT would support this but I believe they could and should for at least the previous version. I suppose there could be some scenario where something new conceptually in the actual data does not exist in an older database, but can't think of one right off. And they might have to even stage the data if it was organized in a new fashion. But in the end, the table data is in the backup and could be loaded. If they wanted to... Perhaps this is a 3rd party market opportunity.

Wednesday, April 6, 2016 - 9:54:13 AM - K. Brian Kelley Back To Top (41148)

 

Hi Dave,

  I think it has more to do with the problem of trying to potentially introduce new features while still making things work on an older version. It's not worth the squeeze. To give an analogy, if Microsoft did allow backups to be ported back to old versions, it would be the equivalent of making XBox One games able to be played on XBox 360 systems. Or worse, on original XBox consoles. It's just not a realistic expectation when put in that context.

Tuesday, April 5, 2016 - 3:52:04 PM - Dave Back To Top (41142)

Well they "could" have made it work if they wanted to by setting a backup compatibility level, or teaching the older version how to restore a newer backup. How do they expect folks to migrate to the next version? Not have a rollback plan? I'm guessing this more has to do with not allowing moving data to older SQL versions so as to help force purchasing upgrades for those versions...

Thursday, April 19, 2012 - 10:23:36 AM - mortalvisionary Back To Top (17002)

Took Microsoft 27 years to determine that a newer version of Office file might need to be read in an older version (2007 finally happened where can read 2007 with 2003 with compat pack) .  MS SQL Server has not been around for 27 years yet, so we have not suffered enough for this to be possible LOL.  Pain is relative, and any good DBA has experienced this issue, especially when we back up our procs and routines by dumping the bak file, and find that not only can you not restore different versions, like 2008r2 and 2008, but there are issues even with small steps, like sp2 or sp3.   This has me loading new versions and old versions all the time just to restore.  Welcome to the unglorius world of a DBA where nothing is really so easy.  I don't complain as long as I am billng on time and you should not either.   Someone once explained to me that the reason they finish a road and then dig it up 1 month later is to employ people.

If you use LiteGreed or SQLUnsafe (litespeed or sqlsafe), there is wider time windows, but similar issues.  At least you can go one way restoring into the newer version.

The reason to restore a db to an older system after upgrading Prod seems moot, for it it is for DEV, it doesn not match PROD, If it is for testing backups, you would think you want the system version to match.

Most good DBA's do file and database corruption tests frequently for corruption is your worst enemy.  That is why I profess to use more full backups, less incrementals and diffs, and use dynamic compression with dedup more whcih can even be made to work in DW environments.   I have seen the old school backup guys cry when they find corruption has been present for weeks and they have to go back weeks and restore logs which usually means data loss for the chain is usually broken.

Dumping out you DEV work is so easy with a BAK dump, but I also script out objects frequently also for when you work at multiple clients, you always need that quick routine.

  

 

 

Wednesday, April 18, 2012 - 9:58:34 AM - K. Brian Kelley Back To Top (16989)

Dave, Nelson has hit upon it. Part of it is metadata. Part of it is the tweaks they make in every version.

Tuesday, April 17, 2012 - 12:52:41 PM - Nelson Back To Top (16974)

The question asked in the title of this article took me on a different path.

My short answer would be:  You cannot restore a database to an older version of SQL Server because forward compatibility does not exist in the world of computer software.  (Backward compatibility does to some degree.)

Despite the fact that none of the data in the database is incompatiable with the older version, the metadata has changed.  In order to prevent corruption, it is safer to not allow the restore to occur, even though the old version recognizes the file as a database backup and can extract some information.  For restore operations, the backup file must match the expected format completely - and we would not want it any other way.  (Old saying:  Close only counts in horseshoes and hand grenades.)

The old version has no way of knowing what new features are implemented in the new database version. It would be quite a break-through if somehow it could, but prescience has never been a feature of software development.

I don't think I'm saying this very well.  I am sure someone with greater understanding of internals would be able to dive in come up with a more technical explanation.  Seeing and understanding the source code for the software would be an advantage.

Tuesday, April 17, 2012 - 9:26:17 AM - Dave Back To Top (16969)

Didn't mean to come off so harsh, but I was hoping for more specifics about what is preventing a restore other than what was provided.

Tuesday, April 17, 2012 - 9:09:35 AM - K. Brian Kelley Back To Top (16968)

Calin, SQL Server 2000 = version 8.0. SQL Server 2005 = version 9.0. SQL Server 2008 = version 10.0. SQL Server 2008R2 = version 10.5. SQL Server 2012 = version 11.0. So older version is the correct way to say it. We'd say XP is an older version of the Windows Kernel than Vista than Windows 7. Same idea.

Dave, I wrote this article because I encountered several developers, a couple of junior DBAs, and several forum threads over the last month asking why they couldn't restore back. So while it seems like common knowledge to us, it is still something that catches folks by surprise. Not every article is going to be at an advanced level. That was actually a lesson I've taken from the last few SQL Saturdays I've attented. Quite a few attendees are new to SQL Server or don't have a lot of experience in it and are looking for 100 level material by writers and presenters love the more advanced stuff, leaving a big gap for them. SQL Saturday #111 Atlanta had a track dedicated just to beginner topics and it was so heavily attended that next year they are seriously considering two tracks.

 

Tuesday, April 17, 2012 - 8:36:35 AM - Greg Back To Top (16966)

It doesn't work because of upgrading Brian described. And he did supply some workarounds. The first one makes the most sense given the scenarion. So it's far more than just a statement of 'it doesn't work'.

This is actually a fairly common issue some will run into when starting to work with SQL Server. I am one of many that had this added to my responsibilities not because of any schooling or training, but more just because someone thought I should be able to do this too along with my other tasks. All too common these days.

Tuesday, April 17, 2012 - 8:05:57 AM - dave Back To Top (16965)

..Because it doesn't work isn't much of an article.

Tuesday, April 17, 2012 - 7:51:11 AM - Calin OPREA Back To Top (16964)

Brian, the print screen is a bit misleading, combined with the title itself: SQL Server 2008 R2 is NOT a newer versionf of SQL Server 2008, but two completely different versions.

best regards,
calin














get free sql tips
agree to terms