Migrate from SQL Standard edition to SQL Express Edition

Copper Contributor

Hello all!

First of all, I am far from an SQL expert, so any help is greatly appreciated.

I have inherited a server which is running a database on SQL Standard edition. The database is small and not critical so I want to move it to SQL Express edition. I have had a hunt around to see if this is possible but so far I have not found a method.

Is this possible? And, if so, how can I achieve this?

Thanks in advance!

2 Replies

@jondukes1915 

 

Hi, Jon.

 

Speaking strictly to the question without getting into the step-by-step detail, then as long as the version of SQL Express is the same or newer than your SQL Server Standard instance, then yes, you can make such a move.

 

The simplest process for moving the database would be to run a backup of the current database on the existing SQL Server Standard would look roughly like:

 

  • Use something like SQL Server Management Studio (version 19, ideally, as it's the most recent) to back up the current database on the existing SQL Server Standard instance;
  • Copy the resulting ".bak" file over to the host hosting the SQL Server Express instance;
  • Use SQL Server Management Studio (or equivalent) to restore the ".bak" file into the SQL Server Express instance - ensuring you use the same database name.

 

Reference:

 

If the database is a "contained" database, then that's pretty much all there is to it. However, it's most likely not a contained database meaning you also need to consider the recreation of any related server-defined logins.

 

Additionally, if the database has any hard dependencies on SQL Server Agent jobs (somewhat likely) or Integration Services packages (fairly unlikely from your description) then SQL Server Express is not suitable for you, as it cannot accommodate such functionality.

 

 

Cheers,

Lain

Thanks @LainRobertson. I will investigate this option.