Generate SQL to Backup All Databases SQL Server

generate-sql-backup-all-databases.sql

-- Generate SQL for backing up all databases
DECLARE @BackupDirectory NVARCHAR(255) = 'D:\mssql_backups'; -- Replace with your desired backup directory
DECLARE @Compression BIT = 1; -- 1 for compression, 0 for no compression
DECLARE @NumberOfFiles INT = 2; -- Specify the desired number of backup files
DECLARE @BackupDate SMALLDATETIME = CONVERT(SMALLDATETIME, GETDATE(), 101); -- Get current date with MM/DD/YYYY format
DECLARE @FormattedBackupDate NVARCHAR(20) = REPLACE(REPLACE(CONVERT(NVARCHAR(20), @BackupDate, 106), ' ', '_'), ',', '');
DECLARE @BackupFiles TABLE (FileIndex INT, DatabaseName NVARCHAR(255), FileName NVARCHAR(255));

-- Generate backup file names excluding tempdb
INSERT INTO @BackupFiles
SELECT FileIndex, name AS DatabaseName, name + '_Full_' + @FormattedBackupDate + '_Part' + CAST(FileIndex AS NVARCHAR(5)) + '.bak'
FROM (
    SELECT TOP (@NumberOfFiles) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS FileIndex 
    FROM master.dbo.spt_values
    WHERE name <> 'tempdb'
) AS Numbers
CROSS JOIN sys.databases
WHERE name <> 'tempdb'
ORDER BY FileIndex;

-- Generate backup SQL script for all databases excluding tempdb
DECLARE @SQLScript NVARCHAR(MAX);

SET @SQLScript = 
    (SELECT STRING_AGG(
        'BACKUP DATABASE [' + DatabaseName + '] TO DISK = N''' + @BackupDirectory + '\' + FileName + ''' WITH COMPRESSION, STATS = 10', 
        '; ' + CHAR(13) + CHAR(10))
    FROM @BackupFiles);

-- Output the generated SQL script to a grid in SSMS
PRINT @SQLScript; -- Use PRINT to view the script before executing
-- EXEC sp_executesql @SQLScript; -- Uncomment to execute the script