How to Migrate SQL Server 2012 to 2019?

Why Migrate SQL Server 2012 to 2019?

Support is the main reason. Three facts explain the urgency:

• Security Fixes. SQL Server 2012 receives no security updates, bug fixes or technical support after 12 July 2022.
• Paid Updates. Microsoft sells Extended Security Updates for SQL Server 2012 only to eligible customers with Software Assurance under an Enterprise Agreement.
• Azure Option. Microsoft names Azure virtual machines as a destination with free extra security updates. Confirm the terms for SQL Server 2012 before you plan around them.

Which Migration Path Fits Your Server?

Match your situation to a path in this table:

What Must You Check Before You Migrate?

Six checks come before any move:

1. Check the service pack with this query:

SELECT SERVERPROPERTY('ProductVersion') AS version,
       SERVERPROPERTY('ProductLevel') AS service_pack;

2. Confirm that the new server uses a supported operating system and 64 bit SQL Server 2019.
3. Backup every user database and the master, model and msdb system databases. The Explore Different Types of Backups in SQL Server guide has the scripts for full, differential and log backups.
4. Note the collation of ShopDB and keep the same collation on the new server.
5. List the logins, linked servers and Agent tasks that the application needs.
6. Check the free disk space on the new server for the data and log files.

4 Ways to Migrate SQL Server 2012 to 2019

Three methods use tools from Microsoft and the fourth adds software for MDF files.

Method #1: Restore a SQL Server 2012 Backup on 2019 (Free, Easy Rollback)

A backup from SQL Server 2012 restores directly on SQL Server 2019. The old instance remains online and gives you a quick return path. Six steps move ShopDB:

1. Install SQL Server 2019 on the new server.
2. Backup ShopDB on the old instance:

BACKUP DATABASE ShopDB
TO DISK = N'G:\Migrate\ShopDB.bak'
WITH CHECKSUM, STATS = 10;

3. Copy ShopDB.bak to the new server.
4. Restore ShopDB and relocate the files:

RESTORE DATABASE ShopDB
FROM DISK = N'H:\Migrate\ShopDB.bak'
WITH MOVE N'ShopDB' TO N'H:\Data\ShopDB.mdf',
     MOVE N'ShopDB_log' TO N'I:\Logs\ShopDB_log.ldf',
     STATS = 10;

5. Create the old logins on the new instance.
6. Check the compatibility level and raise it after the application tests.

Note: SQL Server 2012 databases arrive at compatibility level 110 and keep it until you raise it. The Restore a SQL Database From a BAK File guide explains every restore dialog.

Method #2: Upgrade in Place With SQL Server 2019 Setup (Free, Needs SP4)

Setup replaces the 2012 engine on the same server and preserves the databases. Microsoft requires SQL Server 2012 SP4 for this path. Eight steps start the upgrade:

1. Install SQL Server 2012 SP4 when the build is older.
2. Restart Windows when a restart is pending because Setup stops the upgrade in that case.
3. Open the SQL Server 2019 media, right click setup.exe and select Run as administrator.
4. Select Installation and then Upgrade from a previous version of SQL Server.
5. Accept the license terms and fix every failed rule on Install Rules.
6. Select the SQL Server 2012 instance on Select Instance.
7. Click Upgrade on Ready to Upgrade.
8. Execute SELECT @@VERSION; in SSMS to confirm version 15.

   Setup cannot add new features during an upgrade and cannot convert a 32 bit instance to 64 bit. Add features afterward or restore on a new 64 bit instance.

Note: Add the SQL Server Agent service account to the sysadmin role and enable Windows Authentication for the Agent before Setup starts. Take the backups first because an in place upgrade cannot be undone.

Method #3: Move With Log Shipping for a Short Outage (Free, Two Servers)

Log shipping updates a copy of ShopDB on SQL Server 2019 while users work on the old server. Microsoft lists log shipping as a migration path to SQL Server 2019. The database needs the full or bulk logged recovery model. Six steps set it up:

1. Right click ShopDB on the 2012 instance and select Properties.
2. Select Transaction Log Shipping and tick Enable this as a primary database in a log shipping configuration.
3. Click Backup Settings and add a network path for the log backups.
4. Click Add under Secondary databases and connect to the 2019 server.
5. Select No recovery mode on the Restore Transaction Log tab.
6. Click OK to start the backup, copy and restore tasks.

On the cutover night stop the application and let the last log backup restore. Then backup the tail of the log on the old server and restore it on the new one with WITH RECOVERY:

BACKUP LOG ShopDB
TO DISK = N'G:\Migrate\ShopDB_tail.trn'
WITH NORECOVERY;

RESTORE LOG ShopDB
FROM DISK = N'H:\Migrate\ShopDB_tail.trn'
WITH RECOVERY;

Note: The database upgrades when it recovers on SQL Server 2019. You cannot send it back to SQL Server 2012 afterward. Save the old backups.

Method #4: Migrate a SQL Server 2012 MDF File to 2019 With EmailDoctor (Free Preview, Paid Export)

Teams without a working 2012 server open the MDF file with EmailDoctor SQL Server Data Migration Tool. The migration software scans the file and exports its objects with schema and data to the SQL Server 2019 instance. Vendor notes list SQL Server 2019 and earlier releases.

The free trial previews every object but exports only 25 records per table and 5 other categories. Paid versions remove that limit.

Open the MDF File of SQL Server 2012

Six steps scan the file and show its objects:

1. Start the software on a Windows PC and select Offline mode.
2. Click Browse and add the MDF file.
3. Choose Quick Scan for a healthy file or Advance Scan for a damaged file.
4. Select SQL Server 2012 as the file version.
5. Click OK and close the message when the scan ends.
6. Browse the tree to review tables, views and procedures.

Export to the SQL Server 2019 Instance

Six steps send the objects to the new instance:

7. Click Export at the top.
8. Select SQL Server Database as the type.
9. Add the Server Name with Authentication of the 2019 instance.
10. Select Create New Database or Export to Existing Database and set Collation Settings.
11. Tick the objects to move and choose With Schema & Data.
12. Click Save and then OK when Export Completed appears.

Note: Online mode connects to a live 2012 instance when the server still works. Select SQL Server Compatible SQL Scripts to create a script file.

Which Tasks Finish the Migration?

Four closing tasks apply to every method.

1. Backup every moved database again.
2. Update the statistics after the move:

USE ShopDB;
EXEC sp_updatestats;

3. Rebuild the full text catalogs of databases that use them.
4. Raise the compatibility level after application tests and enable Query Store to watch for regressions:

SELECT name, compatibility_level FROM sys.databases;

ALTER DATABASE ShopDB SET QUERY_STORE = ON;
ALTER DATABASE ShopDB SET COMPATIBILITY_LEVEL = 150;

Which Problems Stop the Migration?

Five problems stop a migration:

Frequently Asked Questions

Author’s Verdict

Backup and restore gives the safest rollback because the old instance remains untouched. In place upgrades save hardware but need SP4 and cannot be undone.

Log shipping cuts the outage for large databases. Migration software fits MDF files and servers that no longer start.

Raise the compatibility level once the application passes its tests. Keep the old backups for one week.