Microsoft SQL Server Migration plan and steps

Migration is nothing but moving a databases from one instance to another instance. It could be a single database or all the databases. DBA will get this many times, for testing or development server new build or removing legacy/old servers

In general, this will be a combination of migration and upgrade of newer version ex: existing 2012 from old server to new server 2016. Some will have two instances in same server and migrate for dev and test purpose.

Types: Best backup/restore, for VLDB very large databases, you can plan SAN replication or VM level and mount point/ VMDK movements with detach attach database etc.

Supported Version and Edition Upgrades, if you are going higher version of migration.

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades?view=sql-server-ver15

Important point and Steps, scripts to start with it:

Migrate everything and test it some days and do a final cut over.

-------------------------------------------------------------------All DB size
select sum(size/128) As SizeIn_MB from sys.master_files
-------------------------------------------------------------------
--select * FROM SYS.DATABASES -- name, database_id, state_desc = 'online', 
--select * from SYS.master_files state_desc = 'online'

/*BACKUP_SCRIPT change the backup location*/

SELECT 'BACKUP DATABASE ['+ NAME + '] TO DISK =''F:\MIGRATION_BACKUP\Full\'+ NAME +'_20_SEP_FULL.BAK'''FROM SYS.DATABASES
WHERE database_id NOT IN (1,2,3,4)AND state_desc = 'online'

-------------------------------------------------------------------
/*REPORT LOGINS*/
SELECT 'USE ['+NAME+'];EXEC SP_CHANGE_USERS_LOGIN ''REPORT'' 'FROM MASTER.SYS.DATABASES
WHERE database_id NOT IN (1,2,3,4) AND state_desc = 'online'

sp_change_users_login 'update_one', 'TestUser1','TestUser1'

---------restore ----- Change the backup location and MDF & LDF also this only works with one ldf and mdf
-- You have to restore the more than one MDF or LDF manually, use the following to find more that one files
select database_id,db_name(database_id) as dbname from SYS.master_files group by database_id having count(database_id)>2 




SELECT 'RESTORE DATABASE ['+ DB_NAME(D.database_id)+'] FROM DISK =''F:\MIGRATION_BACKUP\Full\'+ DB_NAME(D.database_id) +'_20_SEP_FULL.BAK'''+
'WITH '+
'MOVE '''+d.name+''' TO ''D:\mssql\data\'+d.name+'.mdf'','+
'MOVE '''+l.name+''' TO ''e:\mssql\log\'+l.name+'.ldf'', norecovery' FROM  SYS.master_files D JOIN
sys.master_files L ON (D.database_id=L.database_id) AND D.file_id=1 AND L.FILE_ID=2
where D.database_id NOT IN (1,2,3,4) -- add the database_id of that more that 2 count DB here
AND l.state_desc = 'online'

--================= -- Cut over time
/*READ_ONLY*/

SELECT 'ALTER DATABASE ['+NAME+'] SET  READ_ONLY WITH NO_WAIT'FROM SYS.DATABASES
WHERE database_id NOT IN (1,2,3,4) AND state_desc = 'online'
-------------------------------------------------------------------

/*Final log backup use differnt folder for log*/ -- change the path F:\MIGRATION_BACKUP\Log\

SELECT 'BACKUP Log ['+ NAME + '] TO DISK =''F:\MIGRATION_BACKUP\Log\'+ NAME +'_20_SEP_log.TRN'''FROM SYS.DATABASES
WHERE database_id NOT IN (1,2,3,4)AND state_desc = 'online'

-- You have to restore the more than one MDF or LDF manually, use this 
select database_id,db_name(database_id) as dbname from SYS.master_files group by database_id having count(database_id)>2 

-- restore log -- change the path F:\MIGRATION_BACKUP\Log\
SELECT 'RESTORE DATABASE ['+ DB_NAME(D.database_id)+'] FROM DISK =''F:\MIGRATION_BACKUP\Log\'+ DB_NAME(D.database_id) +'_20_SEP_log.TRN'''+
'WITH '+
'MOVE '''+d.name+''' TO ''D:\mssql\data\'+d.name+'.mdf'','+
'MOVE '''+l.name+''' TO ''e:\mssql\log\'+l.name+'.ldf'', Recovery' FROM  SYS.master_files D JOIN
sys.master_files L ON (D.database_id=L.database_id) AND D.file_id=1 AND L.FILE_ID=2
where D.database_id NOT IN (1,2,3,4) -- add the database_id of that more that 2 count DB here
AND l.state_desc = 'online'


/*READ_write*/

SELECT 'ALTER DATABASE ['+NAME+'] SET  READ_WRITE  WITH NO_WAIT'FROM SYS.DATABASES
WHERE database_id NOT IN (1,2,3,4) AND state_desc = 'online'

------------------------------------------------------------------- Optional
/*Check Db*/

SELECT 'DBCC CHECKDB( ['+NAME+'])'+' WITH DATA_PURITY'
FROM SYS.DATABASES WHERE database_id NOT IN (1,2,3,4)AND state_desc = 'online'

/*UPDATE USAGE*/
SELECT 'DBCC UPDATEUSAGE( ['+NAME+'])'
FROM SYS.DATABASES WHERE database_id NOT IN (1,2,3,4)AND state_desc = 'online'

/*UPDATE STATISTICS*/
SELECT 'USE ['+NAME+'];EXEC sp_MSforeachtable ''UPDATE STATISTICS ? WITH FULLSCAN'' 'FROM MASTER.SYS.DATABASES
WHERE database_id NOT IN (1,2,3,4) AND state_desc = 'online'

--(OR) --create maintenance job and run