SQL Backup Plan with Dynamic file Name

DECLARE @day VARCHAR(5)
DECLARE @month VARCHAR(15)
DECLARE @year VARCHAR(5)
DECLARE @hour VARCHAR(5)
DECLARE @filename VARCHAR(500)
SET @day = DATENAME(DAY, GETDATE())
SET @month = DATENAME(MONTH, GETDATE())
SET @year = DATENAME(YEAR, GETDATE())
SET @hour = DATENAME(HOUR, GETDATE())
SET @filename = 'D:DATABASE Backupdb_name_' + @day + @month + @year + @hour + '.bak'
Backup DATABASE db_name TO Disk = @filename WITH format
BACKUP log db_name TO disk = @filename WITH format

2 comments