Sfrgttk

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)

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):

• Partitioning


• Change Data Capture


• Data Compression


• Transparent Data Encryption

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:

SELECT feature_name FROM sys.dm_db_persisted_sku_features;

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

• mirroring


• certain forms of replication


• log shipping


• database snapshots


• online indexing


• updateable distributed partitioned views


• backup compression


• policy-based management


• plan guides


• database mail


• maintenance plans


• full-text search

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:

1. Different memory / CPU limitations on the target edition (or even the underlying operating system on the target). This bit a lot of people who went from 2008 R2 Enterprise to 2012 Enterprise (CAL), where the service is artificially limited to the first 20 cores). This can lead to straightforward performance differences (not enough memory to satisfy a query, for example, or much slower parallel query performance); more subtle ones include plan choices that are made because of the different underlying hardware.


2. Reliance on features such as indexed view matching on the source will not be automatically respected on the target without changing source code to use NOEXPAND. And you may not even be aware that this capability is why your queries suddenly slow down.


3. Same goes for parallel index operations and probably a slew of other optimizations that aren't coming to mind this moment (thankfully I work almost exclusively in the Enterprise space, so I don't have to worry about the limitations of lower editions in most cases).

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:

RESTORE failed for server 'serverinstance'.

RESTORE could not start database 'databasename'.

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):

Database 'databasename' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

Database 'databasename' cannot be started in this edition of SQL Server because it contains a partition function '_dta_pf__9987'. Only Enterprise edition of SQL Server supports partition functions.

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:

1. Restore to an appropriate edition of SQL Server - which will mean locating or installing a new instance.


2. Restore the backup on the source server as a new database with a different name, remove any and all Enterprise features, then backup the database again, and restore that on the lesser edition. (In this specific case, I left the name of the partition function in the error message, because this seems like a discardable thing anyway - it was created by the Database Engine Tuning Advisor and may have been done so by someone who didn't quite know what they were doing. This isn't always the case.)

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...

Developer and Enterprise are the same software, just with different licensing agreements.

You should be fine restoring this database at your destination.

i am having this problem, i am trying to restore from 2008 standard to 2008 enterprise, and getting this error message

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server ''. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The database was backed up on a server running version 10.50.4042. 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. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&LinkId=20476

BUTTONS:

OK