You can migrate SQL Server 2014 to 2022 with four methods. Backup and restore moves databases to a new 2022 server. An in place upgrade needs SQL Server 2014 SP3. Detach and attach moves the data files. Migration software exports objects as SQL scripts for the new instance. Raise the compatibility level to 160 after you test the application.
Microsoft ended support for SQL Server 2014 in July 2024. Many teams now migrate SQL Server 2014 to 2022. SQL Server 2022 receives mainstream support until January 2028.
Most guides show one restore and miss the SP3 rule. Few explain what happens to the compatibility level or how to move objects when a restore is not possible.
This guide explains four methods to move SQL Server 2014 to 2022, with the checks before the move and the tasks after it.
Microsoft lists SQL Server 2014 SP3 as the lowest 2014 source for a direct upgrade in its supported version and edition upgrades article.
Why Migrate SQL Server 2014 to 2022?
Support dates decide the timing. Three facts explain it:
- Old Instance. SQL Server 2014 extended support ended in July 2024.
- New Instance. SQL Server 2022 mainstream support ends on 12 January 2028 and extended support ends on 12 January 2033.
- Compatibility. SQL Server 2022 matches compatibility level 160. An upgraded database needs a separate step to use the newest behavior.
Microsoft states that SQL Server 2022 exists only for 64 bit platforms. A 32 bit instance needs a restore on new hardware.
Which Path Suits Your Setup?
Choose a path by situation:
| Situation | Path |
|---|---|
| New server and a long maintenance window | Backup and restore |
| One server on SP3 and no spare hardware | In place upgrade |
| Large data files and a short copy between servers | Detach and attach |
| No restore possible or objects only | SQL script export |
Which Checks Come Before You Migrate SQL 2014 to 2022?
Six checks come first:
- Find the service pack level with this query:
SELECT SERVERPROPERTY('ProductVersion') AS version,
SERVERPROPERTY('ProductLevel') AS service_pack;- Confirm that the new server uses a supported operating system and 64 bit SQL Server 2022.
- Create backups of your user databases and of the system databases master, model and msdb. Backup scripts for every type sit in the Explore Different Types of Backups in SQL Server guide.
- Record the collation of ShopDB and match it on the new server.
- List the logins, linked servers and Agent tasks of the application.
- Confirm that the new server has free disk space for the data and log files.
4 Ways to Migrate SQL Server 2014 to 2022
Three methods use tools from Microsoft and the fourth adds software for script export.
| Method | Server | Main limit |
|---|---|---|
| Backup and Restore | New SQL Server 2022 server | Logins and Agent tasks move separately |
| In Place Upgrade | Same server | Needs SQL Server 2014 SP3 |
| Detach and Attach | New SQL Server 2022 server | Database goes offline during the copy |
| SQL Script Export | New SQL Server 2022 server | Trial record limit |
Method #1: Restore a SQL Server 2014 Backup on 2022 (Free, Easy Rollback)
A backup from SQL Server 2014 restores directly on SQL Server 2022 and the old instance remains online for a return path. Six steps move ShopDB:
- Install SQL Server 2022 on the new server.
- Backup ShopDB on the 2014 instance:
BACKUP DATABASE ShopDB
TO DISK = N'G:\Migrate\ShopDB.bak'
WITH CHECKSUM, STATS = 10;- Move ShopDB.bak to the new server.
- Restore ShopDB with new file paths:
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;- Add the logins of the 2014 instance to the 2022 instance.
- Verify the compatibility level and keep it at 120 until the application passes its tests.
Note: Microsoft states that a backup retains its compatibility level on restore when the level is 100 or higher. SQL Server 2014 databases arrive at level 120. Restore dialogs are explained in Restore a SQL Database From a BAK File.
Method #2: Upgrade in Place With SQL Server 2022 Setup (Free, Needs SP3)
Setup replaces the 2014 engine on the same server and preserves the databases. Microsoft requires SQL Server 2014 SP3 for this path. Eight steps start the upgrade:
- Install SQL Server 2014 SP3 when the build is older.
- Restart Windows if a restart is pending.
- Right click setup.exe on the SQL Server 2022 media and choose Run as administrator.
- Open Installation and choose Upgrade from a previous version of SQL Server.
- Accept the license and resolve any failed rule on Install Rules.
- Choose the 2014 instance on Select Instance.
- Review the summary and click Upgrade.
- Execute
SELECT @@VERSION;in SSMS to confirm version 16.Setup cannot add features during an upgrade. Add them afterward with SQL Server 2022 Setup.
Note: Before Setup starts, place the Agent service account in the sysadmin role and enable Windows Authentication for the Agent. Create the backups first because an in place upgrade cannot be undone.
Method #3: Move the Data Files With Detach and Attach (Free, Source Goes Offline)
Detach frees the MDF and LDF files so you can copy them to the SQL Server 2022 server. ShopDB remains offline until you attach it again. Six steps move the files:
- Right click ShopDB in Object Explorer and select Tasks and then Detach.
- Tick Drop Connections and click OK.
- Copy ShopDB.mdf and ShopDB_log.ldf to the new server.
- Right click Databases on the 2022 instance and select Attach.
- Click Add and select ShopDB.mdf.
- Click OK after you confirm the file paths.
Note: Attach upgrades the database to SQL Server 2022 and the upgrade cannot be reversed. Keep the backup that you took before the detach.
Method #4: Export SQL Server 2014 Objects as SQL Scripts With EmailDoctor (Free Preview, Paid Export)
Teams that cannot restore a backup use EmailDoctor SQL Server Data Migration Tool to preview the 2014 objects and export them as scripts. The migration software connects to the live instance and saves SQL Server compatible scripts that you execute in SSMS on the new instance.
Vendor notes list SQL Server versions from 2000 to 2019 and do not name SQL Server 2022. Test the script export with the free trial on one small database before you move production data.
The trial previews every object. Exports in trial stop at 25 records per table and 5 other categories until you buy a license.
Link the Software to the 2014 Server
Five steps open the database and show its objects:
- Start the software on a Windows PC that reaches the 2014 instance and click Open.
- Select Live SQL Server with Authentication in Online mode and add the login details.
- Select the database from the Drop Down List and click OK.
- Expand the tree to check tables, views and stored procedures.
- Click Export at the top.
Export the Objects as SQL Scripts
Select SQL Server Compatible SQL Scripts on the export type screen and save the script file.
Open the saved file in SSMS, connect to the SQL Server 2022 instance and execute it there.
Note: Offline mode opens an MDF file when the 2014 server is down. Select With Schema & Data when you need rows and not only structure.
Which Tasks Follow a SQL 2014 to 2022 Migration?
Four tasks follow every method.
- Create a fresh backup of every moved database.
- Refresh the statistics:
USE ShopDB;
EXEC sp_updatestats;- Rebuild full text catalogs where the database has them.
- Raise the level once the application tests pass and enable Query Store to catch slow queries:
SELECT name, compatibility_level FROM sys.databases;
ALTER DATABASE ShopDB SET QUERY_STORE = ON;
ALTER DATABASE ShopDB SET COMPATIBILITY_LEVEL = 160;Level 160 matches SQL Server 2022. You need ALTER permission on the database to set it. Test on a restored copy before production.
Which Problems Stop a SQL 2014 to 2022 Move?
Five problems appear most:
| Problem | Reason | Fix |
|---|---|---|
| Setup does not start | Windows has a pending restart | Restart Windows and open Setup again |
| Setup does not start | Windows Installer service is stopped | Start Windows Installer |
| 2014 instance missing on Select Instance | Source is below SP3 or is 32 bit | Install SP3 or restore on a new 64 bit server |
| Agent rule fails | Agent account is not in the sysadmin role | Add the account and enable Windows Authentication |
| New features are missing | Setup cannot add features while upgrading | Add features with Setup afterward |
Frequently Asked Questions
Can I Migrate SQL Server 2014 to 2022 Directly?
Yes. Microsoft lists SQL Server 2014 SP3 or later as a source for a direct upgrade. Apply the service pack first when your build is older.
Can I Migrate SQL Server 2016 to 2022?
Yes. Microsoft lists SQL Server 2016 SP3 or later for a direct upgrade. The other methods in this guide work the same way.
Can I Migrate SQL Server 2008 to 2022?
Yes. Skip the in place upgrade because Microsoft does not list SQL Server 2008 as an upgrade source. A migration works when the source compatibility level is 90 or higher. Level 90 databases move to level 100 automatically and higher levels stay unchanged.
Will a Restore Update the Compatibility Level?
No. A SQL Server 2014 database retains compatibility level 120 until you raise it with ALTER DATABASE.
Can I Return a Database to SQL Server 2014?
No. An older release cannot attach a database from SQL Server 2022. Restore the pre migration backup.
Can I Migrate a 32 Bit SQL Server 2014 Instance?
Yes. Restore the databases on a new 64 bit instance because Setup cannot convert a 32 bit instance.
Can the Migration Software Handle SQL 2014 to 2022?
Vendor notes list versions up to SQL Server 2019 and omit 2022. Export scripts with the free trial and test them on a 2022 instance before you buy.
Is Every Table Exported in the Trial?
No. Trial exports include 25 records per table and 5 other categories. A license removes the limit.
Author’s Verdict
To migrate SQL 2014 to 2022 with the safest rollback, choose backup and restore because the old server remains unchanged. In place upgrades save hardware but need SP3 and cannot be undone.
Detach and attach suits large files on a local network. Script export fits cases where a restore fails.
Once the application passes its tests, raise the compatibility level to 160 for SQL Server 2022. Save the 2014 backups for a week.
- Four methods migrate SQL 2014 to 2022.
- Backup and restore retains the old instance as a rollback.
- In place needs SQL Server 2014 SP3 and a 64 bit instance.
- Detach and attach moves the data files.
- Script export handles objects.
- Treat compatibility level 160 as a separate step and take a new backup.
Next Read: Migrate SQL Server 2012 to 2019
Also Read: Upgrade SQL Server 2014 to 2019 | Transfer Data Between Two SQL Servers
