Downgrading a database: "You can't get there from here!"
January 21st, 20118
Downgrading a database: "You can't get there from here!"
January 21st, 20118
 
 

I see this question a lot:

I have a SQL Server 2008 R2 database. Can I attach it to a SQL Server 2008 SP2 instance?

Part of this stems from the confusion over exactly what 2008 R2 is in the first place. It is not a service pack to SQL Server 2008; it is its own, full-fledged version. A lot of people don't fully realize this because of various reasons, including:

  • they're on software assurance, so the upgrade to 2008 R2 was covered by their 2008 licenses, and they therefore didn't really realize it was a full version;
  • they're not running 2008 R2 in production, but they do have it in development, and didn't notice the @@VERSION change; or,
  • they're confused by the horrible naming choice.

But to answer the question: No, you cannot backup/restore or detach/attach a SQL Server 2008 R2 database to any previous version, nor can you log ship or mirror in that direction. That is also true in going from Denali to 2008 R2/2008/2005, or from 2008 to 2005, or anything from the last 6 years to 2000 – you just can't do it. A lot of people expect to be able to just set the compatibility level lower, however that is not the answer. It is the database version and the internal file/page structures that matter here; compatibility level just tells the parser and engine how to handle certain features.

What About Same Version, Lower Service Pack?

Well, I should have known this question would come up too. Let's say you are building your database in SQL Server 2008 SP2 and you are ready to deploy it to production, but production is still at SP1. You want to get the code to production but the cycle to introduce SP2 there is far too long. (Now, of course, and with the understanding that hardware equivalency is tougher, there should be a QA environment that matches production exactly in terms of software / configuration / version. So it shouldn't be going from dev to production in this manner, but I understand this is the reality for many people.)

That aside, can you do it? Yes. I set out to prove this. In a VM I laid down three instances of SQL Server 2008: one at RTM, one at SP1, and one at SP2. I created two databases in SP2: SP2DB, and SP2DB_15K. I created one database in SP1: SP1DB. I then took backups of SP1DB and SP2DB, and tried restoring them on the RTM instance. This worked fine and I was able to access the databases.

On the SP2 instance, I ran the following command, to enable 15K partitions for the SP2DB_15K database:

EXEC dbo.sp_db_increased_partitions 'SP2DB_15K', 'ON';
GO

Then I backed up the database and tried to restore it on the RTM instance. I received the following error:

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

If I tried to restore it on the SP1 instance, I received an almost identical error (predictably, the only thing that changes is the version string):

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

So, this exception aside, the answer is, yes, at least in SQL Server 2008, you can take a backup or detached database and restore it on an earlier service pack or RTM. Will this always be true? It's tough to say. Denali SP1 may introduce some change to the internal structure or database version, optional or otherwise, that will prevent it from being restored on RTM, just like our exception above.

Going Forward Always Works, Right?

As for the other direction, it is usually fine to backup/restore, detach/attach, log ship or mirror from an older version to a newer version; however, there are exceptions. For example, Denali does not support the 80 compatibility level, so restoring or attaching a SQL Server 2000 database on a Denali instance is out of the question (I blogged about this here in November). Another example is with SQL Server 2008 SP2 – if you have enabled support for 15,000 partitions, the internal database version is upgraded to 662, and you cannot then migrate to SQL Server 2008 R2 because R2 does not understand that version (it is expecting 661) – in this case you will have to wait until 2008 R2 SP1, when support for 15K partitions is added and the database versions will once again have an upgrade path. Here is what happens when I try to restore the SP2DB_15K database on an R2 instance:

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

(Note that this doesn't happen with the SP2DB database, which did not have 15K partitions enabled.)

It does work on Denali, however; here is what happens when I restore the SP2DB_15K database on a Denali CTP1 instance:

Processed 160 pages for database 'SP2DB_15K', file 'SP2DB_15K' on file 1.
Processed 2 pages for database 'SP2DB_15K', file 'SP2DB_15K_log' on file 1.
Converting database 'SP2DB_15K' from version 662 to the current version 684.
Database 'SP2DB_15K' running the upgrade step from version 662 to version 668.
Database 'SP2DB_15K' running the upgrade step from version 668 to version 669.
Database 'SP2DB_15K' running the upgrade step from version 669 to version 670.
Database 'SP2DB_15K' running the upgrade step from version 670 to version 671.
Database 'SP2DB_15K' running the upgrade step from version 671 to version 672.
Database 'SP2DB_15K' running the upgrade step from version 672 to version 673.
Database 'SP2DB_15K' running the upgrade step from version 673 to version 674.
Database 'SP2DB_15K' running the upgrade step from version 674 to version 675.
Database 'SP2DB_15K' running the upgrade step from version 675 to version 676.
Database 'SP2DB_15K' running the upgrade step from version 676 to version 677.
Database 'SP2DB_15K' running the upgrade step from version 677 to version 678.
Database 'SP2DB_15K' running the upgrade step from version 678 to version 679.
Database 'SP2DB_15K' running the upgrade step from version 679 to version 680.
Database 'SP2DB_15K' running the upgrade step from version 680 to version 681.
Database 'SP2DB_15K' running the upgrade step from version 681 to version 682.
Database 'SP2DB_15K' running the upgrade step from version 682 to version 683.
Database 'SP2DB_15K' running the upgrade step from version 683 to version 684.
RESTORE DATABASE successfully processed 162 pages in 0.021 seconds (60.174 MB/sec).

And the messaging is similar for the other databases from the 2008 instance, except their version starts at 661 instead of 662.

But I'm Still Stuck!

So, you have a database that has been upgraded to 2008 R2 and you need to get it back onto 2008 or 2005? There is no straight path for this scenario; it's kind of like driving in Boston. Assuming you aren't using any features that are exclusive to the newer version (and that you can suspend activity against the newer database temporarily), you can use a schema comparison tool like Red Gate's SQL Compare, to build the modules and schema in an empty database on the older version, then use Integration Services to migrate the data.

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

8 Responses

  1. Innocent says:

    Yes Bambi, that is so true.

  2. Bambi says:

    Well I know this thread is kind of old but you can downgrade a database in a way – through SQL Management Studio.
    Under the relevant database to downgrade, select tasks and then Generate Scripts, Change the advanced options to include Schema AND data, target it to SQL 2005 (or 2000?!?!) and walla…
    You got the schema and data – the things which counts, all the indexing, relations, and everything is taking care for you…

  3. AaronBertrand says:

    No, you cannot apply SQL server 2008 sp2 to SQL server 2008 r2. In spite of the absolutely stupid names for these products, they are completely separate products.

  4. rafiq says:

    Hi
    We have SQL 2008 R2 – Can we apply SQL 2008 SP2 to this? is it compatible /advisable or are there any issues?

  5. AaronBertrand says:

    Yes Joey, sound advice.  But this situation also occurs when there never was an older version of the database – e.g. the developer is running 2008 R2 locally and then wants to deploy her database to a 2008 server.  She's going to have to take the long way (migrate the schema / data to an older version) or wait for the server to be upgraded.

  6. Joey D'Antoni says:

    Aaron–
    Nice work, just a comment. This is why you always take a cold backup (offline) of your database files before embarking on any upgrade project. You can then simply restore them and use the older version of the database.

  7. AaronBertrand says:

    Rajasekhar,
    I used backup / restore in my example, but sorry, detach and attach works in a similar way… you can't go backwards.  Have you tried it?
    Aaron

  8. Rajasekhar says:

    Hi As per my experience in sql.Downgrading a database is not possible, but try this this may work "Attach database with mdf and ldf files". i hope this resolution for ur request.