Migrate SQL Server from 2012 to 2014 (11 to 12)

Question

I have just installed SQL Server Management studio 2014 and I also need to update my (local) server to accept a new database via a bak file from a version 12 server. When I look in Server Properties I see my server has Version 11.0.5858.0 (which is 2012 sp2).

How do I upgrade/migrate my server to version 12?

I've looked at a couple of links: Upgrade Database Engine, Upgrade to SQL Server 2014 Using the Installation Wizard (Setup). I've also seen suggestions that I need to backup and restore databases, which I assume would mean creating a new local (I forgotten how to do this, to tell the truth) and moving them there?

Answer 1

I have just installed SQL Server Management studio 2014

SSMS wont install database engine. Its just a GUI to connect to SQL Server. You can check the SQL Server version using below T-SQL :

SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + ' 
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation

How do I upgrade/migrate my server to version 12?

There are couple of ways to do.

InPlace Upgrade : You just upgrade the current instance by running the setup.exe. The older instance is replaced by the newer version.

Side by side migration: You install and configure a new instance that might exist on the same server or different server and then you move all you databases using backup/restore (preferred) or other methods like - detach/attach, logshipping, copy database wizard, etc.

Note: Depending on your downtime requirements, you have to plan accordingly. Prepare a detailed migration plan and discuss that will stake holders for an acceptable downtime.

If it is your local instance, then you dont have to worry about any downtime.

An excellent answer by Mike Walsh : Are SQL Server in-place upgrades as ill advised as they used to be?

Also, check out UPGRADING TO SQL SERVER 2014: A DOZEN THINGS TO CHECK as well as What to Consider When Creating SQL Server Database Migration Plan?