T-SQL script to Backup database SQL Server with TimeStamp

The T-SQL script takes a full backup of all databases(System databases and user databases) in the format of DataaseName-YYYY-MM-DD-HH-MM-AM/PM.

The script will create a backup file with the name [DatabaseName]_[YYYYMMDD]_[HH]MMSS.bak in the folder specified (by default).

use Master;
set nocount on
 
declare @timestamp varchar(120)
declare @backuppath varchar(max)
declare @command varchar(max)
set     @backuppath = ('C:\SQL\')
set     @timestamp = (select replace(left(convert(char, getdate(), 120), 10) + '-' + replace(replace(right(getdate(), 8), ' ', ''), ':', '-'), ' ', ''))
set     @command = ('if (''?'') not in (''tempdb'') begin backup database [?] to disk = ''' + @backuppath + '?' + '-' + @timestamp + '.bak'' with INIT,FORMAT,COMPRESSION; end')
exec    master..sp_msforeachdb @command

Note: SQL Server cannot take backup of TempDB system database.

The following screenshot shows the backups taken from the DB script.