SQL Server Version Migration: Complete Guide — Factors, Data Loss Prevention & Case Study

SQL Server Version and Compatibility Level Reference

Before planning any migration you need to know the internal version numbers and compatibility levels of both your source and target servers. The compatibility level is what you set on each database — it controls optimizer behaviour independently of the engine version.

-- Run on your existing server first — record these values before anything else
SELECT
    @@SERVERNAME                    AS ServerName,
    @@VERSION                       AS FullVersion,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel')   AS ServicePack,
    SERVERPROPERTY('Edition')        AS Edition,
    SERVERPROPERTY('EngineEdition')  AS EngineEdition; -- 3=Enterprise, 2=Standard

-- Per-database compatibility levels
SELECT
    name               AS DatabaseName,
    compatibility_level,
    state_desc         AS State,
    recovery_model_desc AS RecoveryModel,
    log_reuse_wait_desc AS LogReuseWait,
    CAST(ROUND(size * 8.0 / 1024 / 1024, 2) AS DECIMAL(10,2)) AS DataFileSizeGB
FROM sys.databases
WHERE database_id > 4  -- exclude system databases
ORDER BY size DESC;

The 12 Factors to Assess Before You Start

Factor 1: Deprecated and Discontinued Features

Every SQL Server version removes features that were deprecated in the previous one. Run the deprecation usage counter before planning your migration:

-- Check which deprecated features your workload is actively using
-- Non-zero instance_count means your code calls this deprecated feature
SELECT
    object_name,
    counter_name,
    instance_name,
    cntr_value AS UsageCount
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Deprecated%'
  AND cntr_value > 0
ORDER BY cntr_value DESC;

-- Also check for deprecated syntax in cached query plans
SELECT TOP 20
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS avg_cpu_us,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
          END - qs.statement_start_offset)/2)+1) AS StatementText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%FASTFIRSTROW%'     -- removed in 2016
   OR st.text LIKE '%HOLDLOCK%table%'   -- syntax change
   OR st.text LIKE '%sp_db_vardecimal%' -- removed in 2016
ORDER BY qs.execution_count DESC;

Factor 2: Hardware and OS Requirements

Each SQL Server version has minimum OS requirements. You cannot install SQL Server 2019 on Windows Server 2008.

Factor 3: Edition Compatibility

You can restore a database from a higher edition onto a lower one only if you do not use Enterprise-only features. Check for features that require Enterprise Edition:

-- Check for Enterprise-only features in use on your databases
SELECT
    feature_name,
    feature_enabled AS IsEnabled
FROM sys.dm_db_persisted_sku_features;
-- Returns rows ONLY if Enterprise-only features are enabled
-- (partitioning, transparent data encryption, online index ops, etc.)
-- Empty result = safe to migrate to Standard Edition

Factor 4: Linked Servers and External Dependencies

-- Inventory all linked servers — each needs reconfiguring on the new instance
SELECT
    name               AS LinkedServerName,
    product            AS Product,
    provider           AS Provider,
    data_source        AS DataSource,
    is_linked          AS IsLinked,
    is_remote_login_enabled
FROM sys.servers
WHERE is_linked = 1;

-- Jobs that reference the old server name (will break after rename/migration)
SELECT
    j.name   AS JobName,
    js.step_name,
    js.command
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobsteps js ON js.job_id = j.job_id
WHERE js.command LIKE '%' + @@SERVERNAME + '%'
   OR js.command LIKE '%linked_server_name%'
ORDER BY j.name;

Factor 5: SQL Server Agent Jobs

All Agent jobs, schedules, operators, and alerts must be scripted and recreated on the new instance. SQL Server does not migrate these automatically in a backup/restore — they live in msdb.

-- Count what needs to be migrated from msdb
SELECT
    'Jobs'       AS ObjectType, COUNT(*) AS Count FROM msdb.dbo.sysjobs     UNION ALL
SELECT 'Schedules',                COUNT(*)       FROM msdb.dbo.sysschedules UNION ALL
SELECT 'Operators',                COUNT(*)       FROM msdb.dbo.sysoperators UNION ALL
SELECT 'Alerts',                   COUNT(*)       FROM msdb.dbo.sysalerts    UNION ALL
SELECT 'Maintenance Plans',        COUNT(*)       FROM msdb.dbo.sysmaintplan_plans;

Factor 6: Logins and Permissions

SQL logins (not Windows logins) have a password hash and a SID that must be transferred exactly — or users will get "login failed" errors after migration. The script below generates CREATE LOGIN statements with the original SID and password hash:

-- Script SQL logins with original SID + hashed password
-- Run on OLD server, execute output on NEW server
SELECT
    'CREATE LOGIN [' + name + '] '
    + 'WITH PASSWORD = ' + CONVERT(NVARCHAR(MAX), password_hash, 1)
    + ' HASHED, '
    + 'SID = ' + CONVERT(NVARCHAR(MAX), sid, 1) + ', '
    + 'DEFAULT_DATABASE = [' + default_database_name + '], '
    + 'CHECK_POLICY = ' + CASE is_policy_checked  WHEN 1 THEN 'ON' ELSE 'OFF' END + ', '
    + 'CHECK_EXPIRATION = ' + CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE 'OFF' END
    + ';' AS CreateLoginStatement
FROM sys.sql_logins
WHERE name NOT IN ('sa', '##MS_PolicyTsqlExecutionLogin##', '##MS_PolicyEventProcessingLogin##')
  AND is_disabled = 0
ORDER BY name;

Factor 7: Orphaned Database Users

After restoring a database to a new server, SQL logins that exist on the database as users may become orphaned — the user's SID in the database does not match any login SID on the new server.

-- Run on NEW server AFTER restore to find orphaned users
USE YourDatabase;
GO

-- Method 1: sp_change_users_login report
EXEC sp_change_users_login 'Report';

-- Method 2: manual check (works on all SQL Server versions)
SELECT
    dp.name      AS DatabaseUser,
    dp.type_desc AS UserType,
    sp.name      AS LinkedLogin,
    CASE WHEN sp.name IS NULL THEN 'ORPHANED' ELSE 'OK' END AS Status
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON sp.sid = dp.sid
WHERE dp.type IN ('S','U')  -- SQL user or Windows user
  AND dp.name NOT IN ('dbo','guest','INFORMATION_SCHEMA','sys')
ORDER BY Status DESC, dp.name;

-- Fix an orphaned user by re-linking to the matching login
ALTER USER [OrphanedUserName] WITH LOGIN = [MatchingLoginName];

Factor 8: Database Mail and SMTP Configuration

Database Mail profiles, accounts, and SMTP settings live in msdb and must be recreated. Script them from the old server before migration.

-- Script Database Mail accounts (run on OLD server)
SELECT
    a.name        AS AccountName,
    a.description,
    a.email_address,
    a.display_name,
    s.servername  AS SMTPServer,
    s.port        AS SMTPPort,
    s.enable_ssl
FROM msdb.dbo.sysmail_account a
JOIN msdb.dbo.sysmail_server  s ON s.account_id = a.account_id;

-- Script profile-to-account associations
SELECT
    p.name    AS ProfileName,
    a.name    AS AccountName,
    pa.sequence_number
FROM msdb.dbo.sysmail_profile        p
JOIN msdb.dbo.sysmail_profileaccount pa ON pa.profile_id  = p.profile_id
JOIN msdb.dbo.sysmail_account         a ON a.account_id   = pa.account_id
ORDER BY p.name, pa.sequence_number;

Factor 9: Full-Text Search Catalogs

Full-Text indexes do not transfer automatically with a backup/restore in older SQL Server versions. Catalog the full-text objects before migration.

SELECT
    OBJECT_NAME(i.object_id) AS TableName,
    i.name                   AS FullTextIndexName,
    c.name                   AS CatalogName,
    c.path                   AS CatalogPath,
    i.change_tracking_state_desc
FROM sys.fulltext_indexes    i
JOIN sys.fulltext_catalogs   c ON c.fulltext_catalog_id = i.fulltext_catalog_id;

Factor 10: SSIS Packages and SSRS Reports

SSIS packages stored in the SSISDB catalog and SSRS reports in the ReportServer database must be migrated separately. SSISDB is a user database and can be backed up and restored. SSRS ReportServer and ReportServerTempDB databases can also be restored, but check for encryption key differences between the old and new SSRS installation.

Factor 11: Replication

If the source server is a replication publisher, distributor, or subscriber, replication must be removed before migration and reconfigured afterwards. Migrating a server mid-replication without removing replication first will corrupt the replication topology.

-- Check if this server participates in replication
SELECT
    name                          AS DatabaseName,
    is_published,
    is_subscribed,
    is_merge_published,
    is_distributor
FROM sys.databases
WHERE is_published = 1
   OR is_subscribed = 1
   OR is_distributor = 1;

Factor 12: Database Size and Backup/Restore Duration

-- Estimate how long the backup + restore will take
-- Rule of thumb: backup writes at ~500 MB/s compressed; restore reads at ~400 MB/s
SELECT
    DB_NAME(database_id)                                  AS DatabaseName,
    SUM(size) * 8.0 / 1024 / 1024                        AS TotalSizeGB,
    CAST(SUM(size) * 8.0 / 1024 / 1024 / 500 * 60 AS INT) AS EstBackupMin,
    CAST(SUM(size) * 8.0 / 1024 / 1024 / 400 * 60 AS INT) AS EstRestoreMin
FROM sys.master_files
WHERE database_id > 4
GROUP BY database_id
ORDER BY TotalSizeGB DESC;

-- Check last successful backup (critical — no recent backup = stop and fix first)
SELECT
    d.name AS DatabaseName,
    MAX(b.backup_finish_date) AS LastFullBackup,
    DATEDIFF(HOUR, MAX(b.backup_finish_date), GETDATE()) AS HoursSinceLastBackup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b
    ON b.database_name = d.name AND b.type = 'D'
WHERE d.database_id > 4
GROUP BY d.name
ORDER BY LastFullBackup ASC;

Four Migration Strategies — When to Use Each

Strategy 1: In-Place Upgrade

Run the new SQL Server installer on the existing server and select "Upgrade". The installer migrates system databases, service accounts, and instance configuration. User databases remain in place with their original compatibility levels.

Strategy 2: Backup / Restore (Recommended for Most Migrations)

Install a fresh SQL Server instance on a new server (or new instance on the same server). Take a full backup of each database on the old instance. Restore to the new instance. Redirect applications to the new server. Keep the old server running for 2–4 weeks as rollback insurance.

-- Step 1: Full backup on OLD server (with compression + checksum)
BACKUP DATABASE [YourDatabase]
TO DISK = N'\\backup-share\YourDatabase_migration.bak'
WITH
    FORMAT,
    COMPRESSION,
    CHECKSUM,
    STATS = 10,
    NAME = N'Pre-migration full backup';

-- Step 2: Verify the backup before trusting it
RESTORE VERIFYONLY
FROM DISK = N'\\backup-share\YourDatabase_migration.bak'
WITH CHECKSUM;
-- Must return: "The backup set on file 1 is valid."

-- Step 3: Restore on NEW server
RESTORE DATABASE [YourDatabase]
FROM DISK = N'\\backup-share\YourDatabase_migration.bak'
WITH
    MOVE N'YourDatabase'      TO N'D:\MSSQL\Data\YourDatabase.mdf',
    MOVE N'YourDatabase_log'  TO N'E:\MSSQL\Log\YourDatabase_log.ldf',
    RECOVERY,
    STATS = 10;

-- Step 4: Set compatibility level (keep at source level initially)
ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 120;  -- match source
-- Raise to 150 only after query performance validation in test

Strategy 3: Log Shipping Migration (Minimal Downtime)

Configure log shipping from the old instance to the new instance. The new server applies transaction log backups continuously — staying seconds or minutes behind the old server. On cutover night you take a final tail-log backup, apply it to the new server, and bring it online. Downtime is the time to take and restore the final tail-log — typically under 5 minutes.

-- On OLD server: configure log shipping (primary)
-- Requires FULL recovery model
ALTER DATABASE [YourDatabase] SET RECOVERY FULL;
GO

-- Take initial full backup for log shipping baseline
BACKUP DATABASE [YourDatabase]
TO DISK = N'\\log-ship-share\YourDatabase_LS_base.bak'
WITH COMPRESSION, CHECKSUM, FORMAT;

-- Configure log shipping primary (simplified — use SSMS wizard or these sprocs)
EXEC msdb.dbo.sp_add_log_shipping_primary_database
    @database             = N'YourDatabase',
    @backup_directory     = N'\\log-ship-share\',
    @backup_share         = N'\\log-ship-share\',
    @backup_job_name      = N'LSBackup_YourDatabase',
    @backup_retention_period = 4320,     -- keep 3 days of logs
    @backup_threshold        = 60,       -- alert if no backup in 60 min
    @threshold_alert         = 14420,
    @threshold_alert_enabled = 1,
    @history_retention_period = 43200,
    @backup_job_id        = @LS_BackupJobId OUTPUT,
    @primary_id           = @LS_PrimaryId OUTPUT,
    @overwrite            = 1;

-- CUTOVER: take final tail-log backup (breaks log chain on old server)
BACKUP LOG [YourDatabase]
TO DISK = N'\\log-ship-share\YourDatabase_taillog.trn'
WITH NORECOVERY, NO_TRUNCATE;

-- On NEW server: restore tail-log and bring database online
RESTORE LOG [YourDatabase]
FROM DISK = N'\\log-ship-share\YourDatabase_taillog.trn'
WITH RECOVERY;
-- Database is now online on new server with zero data loss

Strategy 4: Availability Group Migration (Near-Zero Downtime)

If the new server has SQL Server 2019 Enterprise, add it as a secondary replica in a temporary Availability Group with the old server as primary. Once the secondary is synchronised, fail over to the new server. The failover itself takes under 60 seconds for synchronous-commit AGs.

-- On NEW server: add as secondary replica to existing AG
ALTER AVAILABILITY GROUP [MigrationAG]
    ADD REPLICA ON N'NewServerName'
    WITH (
        ENDPOINT_URL     = N'TCP://newserver.internal:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE    = MANUAL,
        SEEDING_MODE     = AUTOMATIC
    );

-- Wait for secondary to fully synchronise
-- Monitor until synchronization_state_desc = 'SYNCHRONIZED'
SELECT
    ag.name        AS AGName,
    ar.replica_server_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.log_send_queue_size    AS LogSendQueueKB,
    drs.redo_queue_size        AS RedoQueueKB
FROM sys.availability_replicas     ar
JOIN sys.availability_groups       ag  ON ag.group_id  = ar.group_id
JOIN sys.dm_hadr_database_replica_states drs ON drs.replica_id = ar.replica_id
WHERE ag.name = 'MigrationAG';

-- Failover (run on OLD primary, or on any replica with SSMS connected to the AG listener)
ALTER AVAILABILITY GROUP [MigrationAG] FAILOVER;

-- Verify new primary
SELECT
    ag.name,
    ar.replica_server_name,
    ar.availability_mode_desc,
    ars.role_desc
FROM sys.availability_replicas   ar
JOIN sys.availability_groups     ag  ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = ar.replica_id;

Handling Data Loss — The Three-Layer Defence

Data loss in a migration happens in three ways: backup taken too early (transactions after the backup are lost), restore failure not detected (backup was corrupt, restore silently incomplete), and post-restore data discrepancy (some tables did not make it across, or row counts diverge). Each has a specific defence.

Layer 1: The Pre-Migration Baseline Snapshot

Before taking the migration backup, capture a baseline of row counts and financial totals for every table. This becomes your reconciliation target after restore.

-- Run on OLD server: capture baseline (save results to a spreadsheet or text file)
-- This is your post-restore reconciliation target
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql += '
SELECT ''' + t.name + ''' AS TableName, COUNT(*) AS RowCount
FROM ' + QUOTENAME(t.name) + ' UNION ALL'
FROM sys.tables t
WHERE t.type = 'U'
ORDER BY t.name;

-- Remove trailing UNION ALL and execute
SET @sql = LEFT(@sql, LEN(@sql) - 10);
EXEC sp_executesql @sql;

-- For financial tables: also capture SUM totals
SELECT
    'CONT_HDR'  AS TableName,
    COUNT(*)    AS RowCount,
    SUM(CH_OUTSTANDING) AS TotalOutstanding,
    SUM(CH_TOTAL_PAID)  AS TotalPaid
FROM dbo.CONT_HDR

UNION ALL

SELECT 'CONT_PAYMNT', COUNT(*), SUM(CP_AMOUNT), 0
FROM dbo.CONT_PAYMNT;

Layer 2: Backup Verification Before Every Restore

Never restore a backup you have not verified. A corrupt backup discovered during a 3 AM migration is the worst possible moment to find out your backup file is unusable.

-- Always run RESTORE VERIFYONLY before any migration restore
RESTORE VERIFYONLY
FROM DISK = N'\\backup-share\YourDatabase_migration.bak'
WITH CHECKSUM;

-- Also check the backup header — confirms backup type, size, and SQL Server version
RESTORE HEADERONLY
FROM DISK = N'\\backup-share\YourDatabase_migration.bak';
-- Key columns: BackupType (1=Full), DatabaseName, BackupSize, ServerVersion,
--              DatabaseVersion (compatibility level), SoftwareVersionMajor

Layer 3: Post-Restore Reconciliation

After every restore, run the reconciliation procedure before declaring success. Compare against the baseline captured in Layer 1.

-- Post-restore row count check (run on NEW server)
-- Compare output against the baseline from the old server
CREATE OR ALTER PROCEDURE dbo.usp_PostRestoreReconciliation
    @DatabaseName SYSNAME
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql   NVARCHAR(MAX);
    DECLARE @Failures INT = 0;

    -- Dynamic row count across all user tables
    CREATE TABLE #RowCounts (TableName SYSNAME, RowCount BIGINT);

    SET @sql = '';
    SELECT @sql += '
    INSERT INTO #RowCounts
    SELECT ''' + t.name + ''', COUNT(*)
    FROM ' + QUOTENAME(@DatabaseName) + '.dbo.' + QUOTENAME(t.name) + ';'
    FROM sys.tables t
    WHERE t.type = 'U'
    ORDER BY t.name;

    EXEC sp_executesql @sql;

    SELECT TableName, RowCount FROM #RowCounts ORDER BY TableName;

    -- Check for tables with zero rows (never normal for a live database)
    SELECT @Failures = COUNT(*) FROM #RowCounts WHERE RowCount = 0;

    IF @Failures > 0
        PRINT 'WARNING: ' + CAST(@Failures AS VARCHAR) + ' table(s) have zero rows — investigate before go-live.';
    ELSE
        PRINT 'Row count check passed — no empty tables found.';

    DROP TABLE #RowCounts;
END;
GO

EXEC dbo.usp_PostRestoreReconciliation @DatabaseName = 'YourDatabase';

The Tail-Log Backup: Capturing the Last Transactions

For a backup/restore migration under FULL recovery model, the transactions that occurred between your migration backup and the moment you close the old database are in the live transaction log — not in the backup file. A tail-log backup captures these final transactions so they can be applied to the new server. Without it, those transactions are lost.

-- Step 1: On OLD server — set application read-only or stop writes to the database
-- (take the application offline, or set the DB to restricted user)
ALTER DATABASE [YourDatabase] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

-- Step 2: Take the tail-log backup (WITH NORECOVERY leaves old DB in restoring state)
BACKUP LOG [YourDatabase]
TO DISK = N'\\backup-share\YourDatabase_taillog.trn'
WITH
    NORECOVERY,    -- puts old DB offline — no rollback from here
    NO_TRUNCATE,   -- captures log even if DB is not accessible
    COMPRESSION,
    CHECKSUM;

-- Step 3: On NEW server — restore the differential/log chain (if any) + tail log
-- (Example: full backup + tail log only — no differentials)
RESTORE DATABASE [YourDatabase]
FROM DISK = N'\\backup-share\YourDatabase_migration.bak'
WITH NORECOVERY;    -- leave in restoring state to accept log restore

RESTORE LOG [YourDatabase]
FROM DISK = N'\\backup-share\YourDatabase_taillog.trn'
WITH RECOVERY;      -- bring database online

-- Step 4: Verify database is accessible
SELECT state_desc FROM sys.databases WHERE name = 'YourDatabase';
-- Expected: ONLINE

Post-Migration Validation: 20 Checks Before Go-Live

-- ============================================================
-- Post-Migration Validation Suite
-- Run on NEW server after restore, before redirecting applications
-- ============================================================

-- Check 1: Database state
SELECT name, state_desc, user_access_desc, is_read_only, recovery_model_desc
FROM sys.databases WHERE name = 'YourDatabase';
-- Expected: ONLINE, MULTI_USER, False, FULL

-- Check 2: Compatibility level (should match source initially)
SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabase';

-- Check 3: Database integrity (DBCC CHECKDB — run off-peak, takes time on large DBs)
DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;
-- Expected: zero errors

-- Check 4: Orphaned users (must be zero before go-live)
USE YourDatabase;
EXEC sp_change_users_login 'Report';

-- Check 5: Index fragmentation baseline (heavy fragmentation = rebuild before go-live)
SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name                     AS IndexName,
    ips.index_type_desc,
    CAST(ips.avg_fragmentation_in_percent AS DECIMAL(5,1)) AS FragPct,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID('YourDatabase'), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 30
  AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Check 6: Missing index recommendations (high-impact ones only)
SELECT TOP 10
    CAST(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS INT)
                                                          AS ImpactScore,
    migs.avg_total_user_cost                              AS AvgQueryCost,
    migs.avg_user_impact                                  AS AvgImpactPct,
    mid.statement                                         AS TableName,
    'CREATE INDEX IX_' + REPLACE(mid.statement,'[dbo].','')
    + ' ON ' + mid.statement
    + ' (' + ISNULL(mid.equality_columns,'') + ')'
    + ISNULL(' INCLUDE (' + mid.included_columns + ')','') AS SuggestedIndex
FROM sys.dm_db_missing_index_groups         mig
JOIN sys.dm_db_missing_index_group_stats    migs ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details        mid  ON mid.index_handle  = mig.index_handle
ORDER BY ImpactScore DESC;

-- Check 7: Stale statistics (update before opening to users)
SELECT
    OBJECT_NAME(s.object_id) AS TableName,
    s.name                   AS StatName,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    CAST(sp.rows_sampled * 100.0 / NULLIF(sp.rows, 0) AS DECIMAL(5,1)) AS SamplePct
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE sp.last_updated < DATEADD(DAY, -7, GETDATE())
  AND sp.rows > 10000
ORDER BY sp.last_updated;

-- Update all statistics after restore (always do this)
EXEC sp_updatestats;

-- Check 8: SQL Agent jobs present and correct
SELECT j.name, j.enabled, j.date_modified, c.name AS CategoryName
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.syscategories c ON c.category_id = j.category_id
ORDER BY j.name;

-- Check 9: Database Mail is configured and working
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DefaultProfile',
    @recipients   = 'dba@yourcompany.com',
    @subject      = 'Post-migration DB Mail test',
    @body         = 'Database Mail working on new SQL Server instance.';

-- Check 10: Backup jobs configured and first backup taken
SELECT d.name, MAX(b.backup_finish_date) AS LastBackup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name = d.name AND b.type = 'D'
WHERE d.database_id > 4
GROUP BY d.name
ORDER BY LastBackup;

-- Check 11: TempDB files correctly configured (1 per CPU core, up to 8)
SELECT name, physical_name, size * 8 / 1024 AS SizeMB, growth
FROM tempdb.sys.database_files;

-- Check 12: Max server memory configured (never leave at default 2,147,483,647 MB)
SELECT name, value_in_use
FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'min server memory (MB)',
               'max degree of parallelism', 'cost threshold for parallelism');

-- Check 13: RCSI enabled (if it was enabled on old server)
SELECT name, is_read_committed_snapshot_on FROM sys.databases WHERE name = 'YourDatabase';

-- Check 14: Linked servers accessible
EXEC sp_testlinkedserver N'YourLinkedServerName';

-- Check 15: Full-text catalogs populated
SELECT
    c.name,
    c.is_accent_sensitivity_on,
    FULLTEXTCATALOGPROPERTY(c.name,'ItemCount') AS IndexedItems,
    FULLTEXTCATALOGPROPERTY(c.name,'PopulateStatus') AS PopStatus
    -- PopStatus 0=idle, 1=full population in progress, 4=error
FROM sys.fulltext_catalogs c;

-- Check 16: No blocking at baseline (before opening to users)
SELECT blocking_session_id, session_id, wait_type, wait_time, status, command
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

-- Check 17: Replication (if applicable) — agents running
SELECT name, status FROM msdb.dbo.MSreplication_monitordata;

-- Check 18: Page verify setting (should be CHECKSUM)
SELECT name, page_verify_option_desc FROM sys.databases WHERE name = 'YourDatabase';
-- If not CHECKSUM:
ALTER DATABASE YourDatabase SET PAGE_VERIFY CHECKSUM;

-- Check 19: Auto-close and auto-shrink must be OFF
SELECT name, is_auto_close_on, is_auto_shrink_on FROM sys.databases WHERE name = 'YourDatabase';
ALTER DATABASE YourDatabase SET AUTO_CLOSE OFF;
ALTER DATABASE YourDatabase SET AUTO_SHRINK OFF;

-- Check 20: Error log clean (no startup errors)
EXEC xp_readerrorlog 0, 1, N'Error', NULL, NULL, NULL, N'DESC';

Raising the Compatibility Level Safely

After the migration is stable and the application has been running on the new server for at least one week, raise the compatibility level to unlock new optimizer features. Do not do this on cutover night — do it in a planned change with a rollback path.

-- Step 1: Capture current plan cache (the baseline to compare against)
SELECT TOP 100
    qs.execution_count,
    qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,
    qs.total_logical_reads / qs.execution_count      AS avg_reads,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1
    ) AS StatementText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_cpu_ms DESC;

-- Step 2: Use Query Store to compare plans before and after (enable it first)
ALTER DATABASE YourDatabase SET QUERY_STORE = ON;
ALTER DATABASE YourDatabase SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO
);

-- Step 3: Raise the compatibility level
ALTER DATABASE YourDatabase SET COMPATIBILITY_LEVEL = 150;  -- SQL Server 2019

-- Step 4: Update statistics immediately after raising level
-- (the new optimizer will use different cardinality estimates)
EXEC sp_updatestats;

-- Step 5: Monitor for plan regressions using Query Store
-- Find queries that got slower after the compatibility level change
SELECT TOP 20
    qsq.query_id,
    qsq.query_hash,
    CAST(qsrs_after.avg_duration / 1000 AS DECIMAL(10,2))  AS After_avg_ms,
    CAST(qsrs_before.avg_duration / 1000 AS DECIMAL(10,2)) AS Before_avg_ms,
    CAST((qsrs_after.avg_duration - qsrs_before.avg_duration)
         * 100.0 / NULLIF(qsrs_before.avg_duration, 0) AS DECIMAL(10,1)) AS PctChange,
    qsqt.query_sql_text
FROM sys.query_store_query        qsq
JOIN sys.query_store_query_text   qsqt  ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan         qsp_b ON qsp_b.query_id = qsq.query_id
JOIN sys.query_store_plan         qsp_a ON qsp_a.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats qsrs_before ON qsrs_before.plan_id = qsp_b.plan_id
JOIN sys.query_store_runtime_stats qsrs_after  ON qsrs_after.plan_id  = qsp_a.plan_id
WHERE qsp_b.compatibility_level = 120  -- plans under old compat level
  AND qsp_a.compatibility_level = 150  -- plans under new compat level
  AND qsrs_after.avg_duration > qsrs_before.avg_duration * 1.20  -- 20%+ regression
ORDER BY PctChange DESC;

-- If a query regressed: force the old plan for that specific query
-- Get the old plan_id from the query above, then:
EXEC sys.sp_query_store_force_plan @query_id = 42, @plan_id = 17;
-- This forces the old plan for that query while you investigate the regression

Case Study: DemoBank Finance — SQL Server 2014 to SQL Server 2019

DemoBank Finance ran SQL Server 2014 Standard Edition (version 12.0.6444) on Windows Server 2012 R2. The driver was end-of-extended-support in July 2024 — after that date, security patches would no longer be available, creating an FCA compliance exposure. The migration was planned for March 2024, three months before EOL.

Assessment Findings

Strategy Chosen: Backup / Restore with Tail-Log

The 210 GB database fit comfortably in the planned 3-hour maintenance window. Log shipping was evaluated but rejected — the additional setup complexity was not justified for a database where 3 hours of downtime was acceptable (migration was scheduled for a Saturday night when no payment runs were due).

The Migration Timeline (Saturday, 22:00 to Sunday 01:30)

Post-Migration Results at 30 Days

-- The comprehensive deprecated syntax scan — search ALL programmable objects
-- Run on OLD server before migration to catch infrequently-executed code
SELECT
    o.type_desc    AS ObjectType,
    OBJECT_NAME(m.object_id) AS ObjectName,
    m.definition
FROM sys.sql_modules m
JOIN sys.objects      o ON o.object_id = m.object_id
WHERE m.definition LIKE '%FASTFIRSTROW%'
   OR m.definition LIKE '%DBCC SHOWCONTIG%'
   OR m.definition LIKE '%sp_db_vardecimal%'
   OR m.definition LIKE '%BACKUP%WITH PASSWORD%'
   OR m.definition LIKE '%\*=%' ESCAPE '\'   -- non-ANSI *= outer join
   OR m.definition LIKE '%=%\*' ESCAPE '\'   -- non-ANSI =* outer join
ORDER BY o.type_desc, ObjectName;

The Rollback Plan

Every migration must have a defined rollback trigger and a tested rollback procedure. "We can always go back" is not a plan. "If check X fails, we execute steps A–D within 30 minutes" is.

-- ROLLBACK PROCEDURE (if migration fails during the cutover window)

-- Step 1: Bring the old server's database back online
-- (If tail-log WITH NORECOVERY was taken, old DB is in Restoring state — recover it)
RESTORE DATABASE [YourDatabase] WITH RECOVERY;
-- Old database is now back online on the OLD server

-- Step 2: Verify old database is healthy
DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS;
SELECT state_desc FROM sys.databases WHERE name = 'YourDatabase';

-- Step 3: Update application connection strings back to old server
-- (or flip DNS/listener back — whichever method was used to redirect apps)

-- Step 4: Restart application and verify
-- Run smoke test against old server to confirm everything is back to normal

-- Step 5: Investigate what failed on the new server before rescheduling

10 Common Post-Migration Problems and Their Fixes

-- Fix #10: set max server memory correctly
-- Rule of thumb: leave 4 GB for OS on servers up to 64 GB RAM;
--               leave 10% of total RAM for OS on servers over 64 GB

DECLARE @TotalRAMGB INT = 64;   -- replace with your server's actual RAM
DECLARE @MaxMemMB   INT = (@TotalRAMGB - 4) * 1024;  -- leave 4 GB for OS

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', @MaxMemMB; RECONFIGURE;

-- Verify
SELECT name, value_in_use FROM sys.configurations WHERE name = 'max server memory (MB)';

Frequently Asked Questions

Can I skip versions — e.g. migrate directly from SQL Server 2012 to SQL Server 2022?

Yes. SQL Server supports restoring a backup from any older version to a newer one — you cannot restore a newer backup onto an older server. A SQL Server 2012 (compatibility level 110) database restored onto SQL Server 2022 will run at level 110 until you raise it. The caveats are: deprecated features removed between 2012 and 2022 must all be found and fixed, and the bigger the version gap the more likely you are to encounter breaking changes. Run the deprecated feature scan and the full object text search for all removed syntax before any skip-version migration.

Do I need to raise the compatibility level after migration?

Not immediately — and not on cutover night. The compatibility level controls optimizer behaviour, not engine features. You get most of SQL Server 2019's engine improvements (ADR, improved In-Memory OLTP, better parallelism) regardless of the level. The new optimizer features (IQP, Batch Mode on Rowstore, scalar UDF inlining) only activate when you raise to level 150. Best practice: raise the level in a separate change, one week after migration, with Query Store enabled so you can catch and roll back any plan regressions.

How do I migrate the SQL Server Agent jobs?

In SSMS: right-click each job → Script Job As → CREATE TO → New Query Window. Run the generated script on the new server. For bulk migration, use the Transfer SQL Server Objects Task in SSIS, or use the Database Engine Tuning Advisor's migration wizard. Alternatively, back up and restore the msdb database to the new server — but this also moves all backup history and mail history, which may not be desired. Most DBAs prefer scripting individual jobs for cleaner control.