Database Migration Assistant Concepts, Backup/Restore Moves, Log Shipping/AG Cutovers, and Validation

USE master;GOIF DB_ID(N'ServiceHubUpgradeLab') IS NULLBEGIN    CREATE DATABASE ServiceHubUpgradeLab;END;GOALTER DATABASE ServiceHubUpgradeLab SET COMPATIBILITY_LEVEL = 160;ALTER DATABASE ServiceHubUpgradeLab SET QUERY_STORE = ON(    OPERATION_MODE = READ_WRITE,    QUERY_CAPTURE_MODE = AUTO,    WAIT_STATS_CAPTURE_MODE = ON);GOUSE ServiceHubUpgradeLab;GOIF SCHEMA_ID(N'lab24') IS NULL EXEC(N'CREATE SCHEMA lab24 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab24.WorkOrder', N'U') IS NULLBEGIN    CREATE TABLE lab24.WorkOrder    (        work_order_id bigint IDENTITY(1001,1) NOT NULL CONSTRAINT PK_lab24_WorkOrder PRIMARY KEY,        customer_id int NOT NULL,        region_code char(3) NOT NULL,        status varchar(16) NOT NULL,        priority tinyint NOT NULL,        opened_at datetime2(0) NOT NULL,        description nvarchar(300) NULL,        row_version rowversion NOT NULL,        CONSTRAINT CK_lab24_WorkOrder_Status CHECK(status IN ('OPEN','ASSIGNED','CLOSED','ESCALATED')),        CONSTRAINT CK_lab24_WorkOrder_Priority CHECK(priority BETWEEN 1 AND 5)    );    ;WITH n AS    (        SELECT TOP (12000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn        FROM sys.all_objects a CROSS JOIN sys.all_objects b    )    INSERT lab24.WorkOrder(customer_id,region_code,status,priority,opened_at,description)    SELECT        CASE WHEN rn <= 5000 THEN 42 ELSE 100 + (rn % 2000) END,        CASE rn % 3 WHEN 0 THEN 'N01' WHEN 1 THEN 'W02' ELSE 'E03' END,        CASE rn % 4 WHEN 0 THEN 'OPEN' WHEN 1 THEN 'ASSIGNED' WHEN 2 THEN 'CLOSED' ELSE 'ESCALATED' END,        1 + (rn % 5),        DATEADD(minute,-rn,'2026-08-20T12:00:00'),        N'ServiceHub migration lab row ' + CONVERT(nvarchar(20),rn)    FROM n;    CREATE INDEX IX_lab24_WorkOrder_CustomerStatus      ON lab24.WorkOrder(customer_id,status,opened_at)      INCLUDE(region_code,priority);END;GO

USE master;GODECLARE @sep nchar(1) = CASE WHEN CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath')) LIKE N'%' + NCHAR(92) + N'%' THEN NCHAR(92) ELSE N'/' END;DECLARE @backup nvarchar(4000) = CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath')) +    CASE WHEN RIGHT(CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath')),1) IN (NCHAR(92),N'/') THEN N'' ELSE @sep END +    N'ServiceHubUpgradeLab_ch24.bak';BACKUP DATABASE ServiceHubUpgradeLabTO DISK = @backupWITH COPY_ONLY, INIT, CHECKSUM, STATS = 10;RESTORE VERIFYONLY FROM DISK = @backup WITH CHECKSUM;DECLARE @data_logical sysname, @log_logical sysname;SELECT @data_logical = name FROM ServiceHubUpgradeLab.sys.database_files WHERE type = 0;SELECT @log_logical  = name FROM ServiceHubUpgradeLab.sys.database_files WHERE type = 1;DECLARE @data_root nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultDataPath'));DECLARE @log_root  nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultLogPath'));DECLARE @datafile nvarchar(4000)=@data_root + CASE WHEN RIGHT(@data_root,1) IN (NCHAR(92),N'/') THEN N'' ELSE @sep END + N'ServiceHubUpgradeLab_Copy.mdf';DECLARE @logfile  nvarchar(4000)=@log_root  + CASE WHEN RIGHT(@log_root,1)  IN (NCHAR(92),N'/') THEN N'' ELSE @sep END + N'ServiceHubUpgradeLab_Copy_log.ldf';IF DB_ID(N'ServiceHubUpgradeLab_Copy') IS NOT NULLBEGIN    ALTER DATABASE ServiceHubUpgradeLab_Copy SET SINGLE_USER WITH ROLLBACK IMMEDIATE;    DROP DATABASE ServiceHubUpgradeLab_Copy;END;DECLARE @q nchar(1)=NCHAR(39);DECLARE @sql nvarchar(max) =N'RESTORE DATABASE ServiceHubUpgradeLab_Copy FROM DISK = N' + @q + REPLACE(@backup,@q,@q+@q) + @q +N' WITH MOVE N' + @q + REPLACE(@data_logical,@q,@q+@q) + @q + N' TO N' + @q + REPLACE(@datafile,@q,@q+@q) + @q +N', MOVE N' + @q + REPLACE(@log_logical,@q,@q+@q) + @q + N' TO N' + @q + REPLACE(@logfile,@q,@q+@q) + @q +N', RECOVERY, STATS = 10;';EXEC sys.sp_executesql @sql;GOSELECT DB_NAME() AS source_db, COUNT_BIG(*) AS work_orders FROM ServiceHubUpgradeLab.lab24.WorkOrder;SELECT N'ServiceHubUpgradeLab_Copy' AS restored_db, COUNT_BIG(*) AS work_orders FROM ServiceHubUpgradeLab_Copy.lab24.WorkOrder;DBCC CHECKDB(N'ServiceHubUpgradeLab_Copy') WITH NO_INFOMSGS;GO

SELECT    SERVERPROPERTY('ServerName') AS server_name,    SERVERPROPERTY('ProductVersion') AS product_version,    SERVERPROPERTY('Edition') AS edition;SELECT name, compatibility_level, state_desc, recovery_model_descFROM sys.databasesWHERE name IN (N'ServiceHubUpgradeLab',N'ServiceHubUpgradeLab_Copy');SELECT COUNT_BIG(*) AS copied_rows,       CHECKSUM_AGG(BINARY_CHECKSUM(customer_id,region_code,status,priority,opened_at)) AS business_checksumFROM ServiceHubUpgradeLab_Copy.lab24.WorkOrder;SELECT sp.name AS login_name, sp.sidFROM sys.server_principals AS spWHERE sp.type_desc IN ('SQL_LOGIN','WINDOWS_LOGIN','WINDOWS_GROUP')ORDER BY sp.name;

USE master;GOIF DB_ID(N'ServiceHubUpgradeLab_Copy') IS NOT NULLBEGIN    ALTER DATABASE ServiceHubUpgradeLab_Copy SET SINGLE_USER WITH ROLLBACK IMMEDIATE;    DROP DATABASE ServiceHubUpgradeLab_Copy;END;GO-- The .bak file is intentionally not deleted by T-SQL.-- Remove it later using an approved OS/storage retention process after verification.