Native backup of all databases in a Microsoft SQL database server

Sometimes it is really convenient to backup all the databases in MS SQL in one go. The script below iterates through all non-system databases that are online and not in standby mode, creating individual timestamped backup files for each.

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = '/tmp/data/backups/'  

-- specify filename format
-- SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '-' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
   BACKUP DATABASE @name TO DISK = @fileName  

   FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

The script generates filenames using a YYYYMMDD-HHMMSS format, for example: MyDatabase_20191113-120000.BAK. System databases (master, model, msdb, tempdb) are excluded, and only online databases that aren’t in log shipping standby mode are backed up.

Original concept based on an MSSQLTips article on database backup automation.