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.
Best Answer
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 :)