SQL Servermigration guide

SELECT
    SERVERPROPERTY('MachineName') AS machine_name,
    SERVERPROPERTY('ServerName') AS server_name,
    SERVERPROPERTY('InstanceName') AS instance_name,
    SERVERPROPERTY('Edition') AS edition,
    SERVERPROPERTY('ProductVersion') AS product_version,
    SERVERPROPERTY('ProductLevel') AS product_level,
    SERVERPROPERTY('ProductUpdateLevel') AS product_update_level,
    SERVERPROPERTY('EngineEdition') AS engine_edition,
    @@VERSION AS version_string;

SELECT
    d.name AS database_name,
    d.state_desc,
    d.recovery_model_desc,
    d.compatibility_level,
    d.is_read_only,
    SUSER_SNAME(d.owner_sid) AS database_owner,
    CONVERT(decimal(18,2), SUM(mf.size) * 8.0 / 1024.0) AS size_mb,
    COUNT(mf.file_id) AS file_count
FROM sys.databases AS d
JOIN sys.master_files AS mf
    ON mf.database_id = d.database_id
WHERE d.name NOT IN (N'master', N'model', N'msdb', N'tempdb')
GROUP BY
    d.name,
    d.state_desc,
    d.recovery_model_desc,
    d.compatibility_level,
    d.is_read_only,
    d.owner_sid
ORDER BY size_mb DESC;

SELECT
    DB_NAME(mf.database_id) AS database_name,
    mf.file_id,
    mf.type_desc,
    mf.name AS logical_file_name,
    mf.physical_name,
    CONVERT(decimal(18,2), mf.size * 8.0 / 1024.0) AS size_mb,
    CASE
        WHEN mf.is_percent_growth = 1 THEN CONCAT(mf.growth, N'%')
        ELSE CONCAT(CONVERT(decimal(18,2), mf.growth * 8.0 / 1024.0), N' MB')
    END AS growth_setting,
    CASE
        WHEN mf.max_size = -1 THEN N'Unlimited'
        ELSE CONCAT(CONVERT(decimal(18,2), mf.max_size * 8.0 / 1024.0), N' MB')
    END AS max_size
FROM sys.master_files AS mf
WHERE DB_NAME(mf.database_id) NOT IN (N'master', N'model', N'msdb', N'tempdb')
ORDER BY database_name, mf.type_desc, mf.file_id;

SELECT
    sp.name AS login_name,
    sp.type_desc,
    sp.is_disabled,
    sp.default_database_name,
    sp.create_date,
    sp.modify_date,
    STUFF((
        SELECT N', ' + role_principal.name
        FROM sys.server_role_members AS srm
        JOIN sys.server_principals AS role_principal
            ON role_principal.principal_id = srm.role_principal_id
        WHERE srm.member_principal_id = sp.principal_id
        ORDER BY role_principal.name
        FOR XML PATH(''), TYPE
    ).value(N'.', N'nvarchar(max)'), 1, 2, N'') AS server_roles
FROM sys.server_principals AS sp
WHERE sp.type IN ('S', 'U', 'G')
  AND sp.name NOT LIKE N'##MS_%'
ORDER BY sp.is_disabled, sp.type_desc, sp.name;

SELECT
    DB_NAME() AS database_name,
    dp.name AS database_user,
    dp.type_desc,
    dp.authentication_type_desc,
    dp.default_schema_name,
    sp.name AS matching_server_login
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
    ON sp.sid = dp.sid
WHERE dp.type IN ('S', 'U', 'G')
  AND dp.sid IS NOT NULL
  AND dp.name NOT IN (N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')
  AND sp.sid IS NULL
ORDER BY dp.name;

SELECT TOP (150)
    j.name AS job_name,
    SUSER_SNAME(j.owner_sid) AS job_owner,
    j.enabled,
    s.name AS schedule_name,
    s.enabled AS schedule_enabled,
    h.run_status AS last_run_status,
    DATETIMEFROMPARTS(
        h.run_date / 10000,
        (h.run_date % 10000) / 100,
        h.run_date % 100,
        h.run_time / 10000,
        (h.run_time % 10000) / 100,
        h.run_time % 100,
        0
    ) AS last_run_start_time,
    ((h.run_duration / 10000) * 3600)
      + (((h.run_duration % 10000) / 100) * 60)
      + (h.run_duration % 100) AS last_run_duration_seconds,
    h.message AS last_run_message
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysjobschedules AS js
    ON js.job_id = j.job_id
LEFT JOIN msdb.dbo.sysschedules AS s
    ON s.schedule_id = js.schedule_id
OUTER APPLY (
    SELECT TOP (1)
        h.run_date,
        h.run_time,
        h.run_duration,
        h.run_status,
        h.message
    FROM msdb.dbo.sysjobhistory AS h
    WHERE h.job_id = j.job_id
      AND h.step_id = 0
      AND h.run_date > 0
    ORDER BY h.run_date DESC, h.run_time DESC
) AS h
ORDER BY j.enabled DESC, j.name, s.name;

SELECT
    N'Linked server' AS object_type,
    s.name AS object_name,
    s.product,
    s.provider,
    s.data_source,
    CONVERT(nvarchar(4000), NULL) AS extra_detail
FROM sys.servers AS s
WHERE s.is_linked = 1

UNION ALL

SELECT
    N'Credential' AS object_type,
    c.name AS object_name,
    CONVERT(nvarchar(128), NULL) AS product,
    CONVERT(nvarchar(128), NULL) AS provider,
    CONVERT(nvarchar(4000), NULL) AS data_source,
    c.credential_identity AS extra_detail
FROM sys.credentials AS c

UNION ALL

SELECT
    N'Server trigger' AS object_type,
    tr.name AS object_name,
    CONVERT(nvarchar(128), NULL) AS product,
    CONVERT(nvarchar(128), NULL) AS provider,
    CONVERT(nvarchar(4000), NULL) AS data_source,
    CASE WHEN tr.is_disabled = 1 THEN N'Disabled' ELSE N'Enabled' END AS extra_detail
FROM sys.server_triggers AS tr
ORDER BY object_type, object_name;

WITH LastBackups AS (
    SELECT
        bs.database_name,
        bs.type,
        MAX(bs.backup_finish_date) AS last_backup_finish_date
    FROM msdb.dbo.backupset AS bs
    WHERE bs.type IN ('D', 'I', 'L')
    GROUP BY bs.database_name, bs.type
)
SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    d.state_desc,
    MAX(CASE WHEN lb.type = 'D' THEN lb.last_backup_finish_date END) AS last_full_backup,
    MAX(CASE WHEN lb.type = 'I' THEN lb.last_backup_finish_date END) AS last_differential_backup,
    MAX(CASE WHEN lb.type = 'L' THEN lb.last_backup_finish_date END) AS last_log_backup
FROM sys.databases AS d
LEFT JOIN LastBackups AS lb
    ON lb.database_name = d.name
WHERE d.name <> N'tempdb'
GROUP BY d.name, d.recovery_model_desc, d.state_desc
ORDER BY d.name;

SELECT TOP (100)
    rh.destination_database_name,
    rh.restore_date,
    rh.restore_type,
    rh.replace,
    rh.recovery,
    bs.database_name AS source_database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bmf.physical_device_name
FROM msdb.dbo.restorehistory AS rh
LEFT JOIN msdb.dbo.backupset AS bs
    ON bs.backup_set_id = rh.backup_set_id
LEFT JOIN msdb.dbo.backupmediafamily AS bmf
    ON bmf.media_set_id = bs.media_set_id
ORDER BY rh.restore_date DESC;

-- Template only. Replace names, paths, and MOVE targets.
-- Restore every required backup in order before using WITH RECOVERY.
-- Confirm target storage, permissions, and file paths before the test run.

RESTORE DATABASE YourDatabase
FROM DISK = N'\\fileserver\sqlbackups\YourDatabase_full.bak'
WITH MOVE N'YourDatabase' TO N'D:\SQLData\YourDatabase.mdf',
     MOVE N'YourDatabase_log' TO N'L:\SQLLogs\YourDatabase.ldf',
     NORECOVERY,
     CHECKSUM;

-- Optional: restore the latest useful differential backup after the full backup.
RESTORE DATABASE YourDatabase
FROM DISK = N'\\fileserver\sqlbackups\YourDatabase_diff.bak'
WITH NORECOVERY,
     CHECKSUM;

-- Restore each required log backup in sequence, keeping the database restoring.
RESTORE LOG YourDatabase
FROM DISK = N'\\fileserver\sqlbackups\YourDatabase_log_001.trn'
WITH NORECOVERY,
     CHECKSUM;

RESTORE LOG YourDatabase
FROM DISK = N'\\fileserver\sqlbackups\YourDatabase_log_002.trn'
WITH NORECOVERY,
     CHECKSUM;

-- Restore the final log backup with RECOVERY only when cutover is ready.
RESTORE LOG YourDatabase
FROM DISK = N'\\fileserver\sqlbackups\YourDatabase_final.trn'
WITH RECOVERY,
     CHECKSUM;

-- Template only for a staged log-restore cutover.
-- Use only after test run and after confirming the final synchronization point.

-- 1. Freeze application writes.
-- 2. Take the final source log backup.
BACKUP LOG YourDatabase
TO DISK = N'\\fileserver\sqlbackups\YourDatabase_final.trn'
WITH CHECKSUM;

-- 3. Restore the final log on the target.
RESTORE LOG YourDatabase
FROM DISK = N'\\fileserver\sqlbackups\YourDatabase_final.trn'
WITH RECOVERY,
     CHECKSUM;

-- 4. Switch connections.
-- 5. Run validation.
-- 6. Decide go, rollback, or hold.

Cutover checklist skeleton

1. Confirm source freeze and change window start.
2. Confirm latest backup or final log backup completed.
3. Restore final backup on target and recover the database.
4. Switch application connection strings, DNS, aliases, or listener routing.
5. Validate logins, jobs, linked servers, backups, monitoring, and critical workflows.
6. Decide go or rollback before the agreed decision time.
7. Keep source protected until rollback and retention windows have passed.

SELECT TOP (50)
    qt.query_sql_text,
    q.query_id,
    p.plan_id,
    rs.count_executions,
    CAST(rs.avg_duration / 1000.0 AS decimal(18,2)) AS avg_duration_ms,
    CAST(rs.avg_cpu_time / 1000.0 AS decimal(18,2)) AS avg_cpu_ms,
    CAST(rs.avg_logical_io_reads AS decimal(18,2)) AS avg_logical_reads,
    rs.last_execution_time
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_plan AS p
    ON p.plan_id = rs.plan_id
JOIN sys.query_store_query AS q
    ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt
    ON qt.query_text_id = q.query_text_id
ORDER BY rs.avg_duration DESC;