T-SQL: Ad-hoc Commands to Backup All Databases

-- turn on Windows cmd shell
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

-- use Windows command to map a drive
EXEC XP_CMDSHELL 'net use B: \\SMBSERVER\ShareName'
-- check the drive
EXEC XP_CMDSHELL 'Dir B:'

-- backup all databases, AD-HOC method, not recommended for daily backups
DECLARE @bk_path VARCHAR(256);
DECLARE @db_name VARCHAR(50);
DECLARE @bk_file_date VARCHAR(20);
DECLARE @bk_file_name VARCHAR(256);
DECLARE @i INT = 1;
 
/* Set backup path */
SET @bk_path = 'B:\Backup\';
 
/* Backup file format DBname_YYYYMMDD_HHMMSS.BAK. Change this as needed. */
SELECT @bk_file_date = FORMAT(GETDATE(), 'yyyyMMdd_hhmmss');
 
DECLARE @db_names TABLE (
   id INT IDENTITY(1,1) PRIMARY KEY, 
   db_name VARCHAR(50) NOT NULL );
 
INSERT INTO @db_names
   SELECT name 
   FROM master.sys.databases 
   WHERE name NOT IN ('master','model','msdb','tempdb')  /* Databases which needs to be excluded */
      AND state = 0 /* Include only the database which are online */
      AND is_in_standby = 0; /* database is not read only for log shipping */
 
WHILE EXISTS (SELECT 1 from @db_names WHERE Id = @i)
BEGIN
    
   SELECT @db_name = db_name from @db_names WHERE Id = @i;
   PRINT 'Backup Started: ' + @db_name
    
   SET @bk_file_name = @bk_path + @db_name + '_' + @bk_file_date + '.BAK';
 
   BACKUP DATABASE @db_name TO DISK = @bk_file_name WITH STATS;
 
   SET @i = @i + 1;
    
END
GO

-- delete a mapped drive after back has completed
EXEC XP_CMDSHELL 'net use B: /delete'