2

i have a question about restoring database from newer version of SQL Server to older, when version differs only in SoftwareVersionBuild. I know that's not possible to restore database from SQL Server 2012 to 2008, or from 2008 R2 to 2008 etc. but how about situation when i have to restore DB from SQL Server 2008 R2 SP2 (10.50.4033) to version 10.50.2500? (2008R2 SP1). Well, I know that you'll ask why i just won't try to do it, but unfortunately i don't have access to server with 2008R2 SP1 version, and i'll have to deploy my DB on it soon.

I installed SQL Server 2008 R2 on freshly craeted virtual machine, but exact version of freshly installed SQL Server is 10.50.4000. I managed to successfully restore my test DB from version 10.50.4033 to 10.50.4000, so I guess that it'll work to restore it on version 10.50.2500, too, but I would like to just be sure. I tried to find information about restoring SQL databases when SQL Server version differs only in SoftwareVersionBuild, but i couldn't find answer.

2

Migrating a database specifically from 2008 R2 SP2 to 2008 R2 SP1 will be fine*.

Migrating up or down service pack levels on the same SQL Server version is generally OK, because the internal database version number usually does not change.

There have been a couple of exceptions to this general rule. The one that might interest you concerns databases where support for 15,000 partitions has been enabled (changing the internal database version). If this scenario might apply to you, download and review the SQL Server Technical Article below:

Support for 15,000 Partitions in SQL Server 2008 SP2 and SQL Server 2008 R2 SP1.

Even so, going directly from a 2008 R2 SP2 instance to SP1 should not present any problems. Always have verified (restored and DBCC-checked) backups just in case.


* Caveat: If the SP2 database had support for 15,000 partitions enabled, it would not be accessible when restored to R2 RTM as an intermediate step. Applying SP1 would then resolve this. Good to know to avoid panic at the RTM stage :)

2

how about situation when i have to restore DB from SQL Server 2008 R2 SP2 (10.50.4033) to version 10.50.2500? (2008R2 SP1).

The restore will work just fine when restoring from SP2 to SP1. I verified by restoring SQL server SP1 backup to SQL Server RTM. YOU can check using RESTORE HEADERONLY FROM DISK = 'YOUR BACKUP PATH.BAK'.

You have the SoftwareVersionMajor, SoftwareVersionMinor, and SoftwareVersionBuild so that you can know precisely what version of SQL Server was used and whether any service packs, cumulative updates, or hotfixes have been applied.

I know that's not possible to restore database from SQL Server 2012 to 2008, or from 2008 R2 to 2008 etc.

Practically you can restore, but that involves a lot of work (thought I would recommend as a last resource). Some of the newer stuff in higher version will not work, but atleast you can get the data back in the database.

Note: Restoring a database clears the plan cache for the instance of SQL Server thereby causing recompilations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.