Generate SQL for a Database Full Backup

generate-sql-for-database-full-backup.sql

-- Generate SQL for running a full database backup 
DECLARE @DatabaseName NVARCHAR(255) = 'Jupiter'; -- Replace with your actual database name
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 = 1; -- 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, FileName NVARCHAR(255));

-- Generate backup file names
INSERT INTO @BackupFiles
SELECT FileIndex, @DatabaseName + '_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) AS Numbers
ORDER BY FileIndex;

-- Generate backup SQL script
DECLARE @SQLScript NVARCHAR(MAX);

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

-- Output the generated SQL script to a grid in SSMS
SELECT [GeneratedSQLScript] = @SQLScript;