/*
   《Dynamic SQL Applications, Performance, and Security in Microsoft SQL Server》, were tested most heavily with AdventureWorks 2016
*/
BACKUP DATABASE pubs
TO DISK=‘E:\SQLBackups\pubs.bak‘
WITH COMPRESSION;


-- Dynamic SQL Built to Back Up All Databases Starting with “AdventureWorks”

DECLARE @database_list TABLE
      (database_name SYSNAME);
INSERT INTO @database_list
      (database_name)
SELECT
      name
FROM sys.databases
WHERE name LIKE ‘AdventureWorks%‘;
DECLARE @sql_command NVARCHAR(MAX);
DECLARE @database_name SYSNAME;
DECLARE database_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT database_name FROM @database_list
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @database_name;
WHILE @@FETCH_STATUS = 0
BEGIN
      SELECT @sql_command = ‘
      BACKUP DATABASE [‘ + @database_name + ‘]
      TO DISK="E:\SQLBackups\‘ + @database_name + ‘.bak"
      WITH COMPRESSION;‘
      EXEC (@sql_command);
      FETCH NEXT FROM database_cursor INTO @database_name;
END
CLOSE database_cursor;
DEALLOCATE database_cursor;