select 'BACKUP DATABASE ' + name + ' TO DISK = ''C:\Work\BackupDBs\' + name + '.bak'' WITH FORMAT, MEDIANAME = ''SQLServerBackups'', NAME = ''Full Backup of ' + name +''';'
from master.sys.databases
where database_id > 4
AND name <> 'db_temp'
ORDER BY NAME
Then run the result set.
To make the restore commands use:
select 'RESTORE DATABASE ' + name + ' from DISK = ''C:\Work\BackupDBs\' + name + '.bak'' WITH MOVE ''' + name + ''' TO ''C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\' + name + '.mdf'', MOVE ''' + name +'_log'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\' + name + '_Log.ldf RECOVERY;''' from master.sys.databases where database_id > 4 AND name <> 'db_temp' ORDER BY NAME