I read that it's possible to restore a database in SQL Server as long as you're restoring from an older version to a newer version, for backwards compatibility reasons.
Does anyone know off hand if you can restore a database from a *.bak file for different editions of SQL Server? We are moving a very large database via FTP that will take a couple days, so we'd rather only do this once. If nobody responds by the time we transfer the database via FTP, we will obviously try this out and see if it works by testing, and answer our own question.
Below is a query to get version details of SQL Server. The productversion
is in the format {major revision}.{minor revision}.{release revision}.{build number}
. In my case, the {release revision}
has a value of 5500
for the source and 5512
for the target. So that looks okay. However, the edition
is different.
Query:
SELECT
SERVERPROPERTY('productversion'),
SERVERPROPERTY('productlevel'),
SERVERPROPERTY('edition')
Source database:
10.0.5500.0
SP3
Developer Edition (64-bit)
Target database:
10.0.5512.0
SP3
Enterprise Edition (64-bit)
Best Answer
From Developer to Enterprise will be fine, just be sure that if you are using processor licensing you have licenses on the target server to cover all of the CPUs. And it's not enough to just hide them from SQL, if they're physically connected to the machine, you're responsible for them.
Also when you go from a lower build to a higher build your database version will increase. There are a few scenarios where this can be problematic - e.g. if you are using 15,000 partition support on a specific build of 2008 it will not work when you upgrade to a specific build of 2008 R2. You may also be relying on optimizations (and have workarounds in place) that are actually bugs in an older build but are fixed in the new build, and this may lead to worse performance. It is also vital to review any trace flags in use at the source and determine if they should also be enabled at the destination. Never mind jobs, logins, etc.
Of course you cannot go backwards. I've never tried a minor downgrade like 10.0.5512 -> 10.0.5500 but it is definitely not possible to go down in service pack or version. So if you have a 2012 database on your Developer Edition instance and you want to put it on your 2008 instance in production, you'll have your work cut out for you (see here and here) - especially if you've used 2012 features.
But to cover other cases that might land people at this question (e.g. someone wants to go from Developer -> Standard or Enterprise -> Express or what have you)...
There are other edition -> edition upgrades that won't go so well, e.g. from Developer -> Express if you've used any features that aren't supported in Express (and same goes for any edition other than Enterprise really). Some examples of features you won't be able to use on down-level editions (in which case the restore will die at the point it tries to bring the database online):
I don't know if there's a way to tell this directly from the .BAK file (I'm sure there's some magic that can be extracted from page headers somewhere, or if you've got a weekend to burn with a hex editor), but while the database is still intact on the source instance, you can always do the following to see if you're using any features that are available because of the SKU you're in:
I'm not sure if SQL Server Audit should be on that list - the edition exclusivity of that feature has changed, so it probably depends on what you're doing with it. There are other things that you might be using but won't show up in the DMV (some because they are in your code, which the DMV doesn't parse, and some because your database is relying on external things such as SQL Server Agent, Service Broker, etc.):
There are also cases where you won't be able to go from Developer to Express because of file size limitations (Express databases are limited to 10GB in total data file size).
Of course there may be other gotchas that you won't be warned about - they won't prevent the migration, but they might lead to very different performance on the target. Examples:
NOEXPAND
. And you may not even be aware that this capability is why your queries suddenly slow down.UPDATE based on this duplicate:
There may be cases where you try to restore a database from a certain edition to a lesser edition (even on the same version), and you get errors that are less than helpful:
This isn't very intuitive. However if you look deeper in SQL Server's event logs, you will see more useful errors (just one example):
Now, that's not quite true - you can also restore to Evaluation Edition or Developer Edition, but that's beside the point. In order to restore this database, you basically have two options:
A variation on (2) would be to just remove the partitioning and other features on the source database, and take another backup. But if it ain't broke...