One of the quickest and most reliable ways to perform a migration is through backup and restore. If there's a mistake during the upgrade process then rollback is much less involved compared to a detach/attach migration or an upgrade in place.

If the databases are relatively small then I usually take an ad-hoc back up right before the migration. If the DBs are large we can still use backup and restore but might need to change our process a bit, e.g. use log back ups in conjunction with fulls.

Step 1) Back up databases on source instance with this script


DECLARE @DBname VARCHAR(128)
SET @DBName = (SELECT TOP 1 name FROM Sys.databases WHERE database_id > 4 ORDER BY Name ASC)

DECLARE @sql VARCHAR(250)

WHILE @DBname IS NOT NULL
BEGIN
	SET @sql =  'BACKUP DATABASE ['+@DBname+'] TO DISK = ''D:\Backups\'+@DBname+'_adhoc.bak'';
	              '
	EXEC (@sql)
	SET @DBname = (SELECT TOP 1 name FROM Sys.databases WHERE database_id > 4 AND name > @DBname ORDER BY Name ASC)
END

Step 2) Move the backup files to the destination server

Step 3) Generate the restore script on the source server. If you restore the databases to a newer version of SQL Server then don't forget to alter the compatibility level to the new version. For example, set the compatibility to 140 for SQL 2017.


DECLARE @Dest_BackupLocation VARCHAR(200),
        @dataDesitnation VARCHAR(200),
		@logDestination VARCHAR(200)

SET @Dest_BackupLocation= 'E:\Backups\'
SET @dataDesitnation = 'D:\Data\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\UserData\'
SET @logDestination= 'L:\Logs\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Logs\'


SELECT 
'Restore Database [' +bs.database_name+ '] 
 FROM Disk = N'''+@Dest_BackupLocation+bs.database_name+'_adhoc.bak'' With file = 1
, Move N'''+bs.database_name+
'''TO N'''+@dataDesitnation+bs.database_name+'.mdf'', 
   MOVE N'''+bs.database_name+'_log'' 
    TO N'''+@logDestination+bs.database_name+'_log.LDF'', 
  NOUNLOAD, STATS = 10;
  
   ALTER DATABASE ['+bs.database_name+'] SET COMPATIBILITY_LEVEL = 140' AS RestoreScripts
FROM msdb.dbo.backupmediafamily AS mf
JOIN msdb.dbo.backupset AS bs
ON mf.media_set_id = bs.media_set_id 
WHERE  bs.backup_start_date >= DATEADD(hh,-8,GETDATE()) --Get Back ups from an hour ago
AND bs.type = 'D'
ORDER BY 
bs.database_name, 
bs.backup_finish_date

Step 4) Finally, migrate any permissions from the source server.