Best method to move a sql 2012 express install AND upgrade to 2016 standard

SQL Server Administration
1

Hi all,

So I have a running production 2012 express server which i need to move to a new machine. I also need to upgrade this from 2012 express to 2016 standard. Right now I'm thinking of installing 2012 express on the new machine, configuring it to match as exactly as i can the old server, copy over all the databases etc and then do an upgrade install of 2016.

Although i'd prefer not doing an upgrade install since it seems less messy to me, i think doing it this way is more likely to ensure everything will work.

Any thoughts?

2

I would not put express anywhere near the new machine.

  1. Install SQL2016 on new machine
  2. Script Logins, with SIDs, etc from old machine

https://support.microsoft.com/en-gb/kb/918992

http://www.sqlservercentral.com/Forums/Topic1240480-1351-1.aspx

  1. Apply needed logins etc on new machine.
  2. Backup DBs on old machine .
  3. Restore DBs to new machine.
  4. Run DBCC CHECKDB, UPDATE STATISTICS etc on new machine
  5. Check Backup/Test Restore routines are okay
  6. Test!!! (Especially if you change the compatibility level as SQL2014 introduced a new cardinality estimator which is generally better but can cause problems with some queries.)
3

There might be no problem from shifting from one machine to another only you to take care that you have the latest backup of the data files. Simply after copying database file to another machine. Attach your .mdf file there.

4

I would definitely Backup and Restore in the way that @Ifor described

Here are my notes on upgrading (its written for a specific version, but it covers the general case of "how" as well as things to consider. I expect its way more than you need, but you may find some useful items in there.